count total records after groupBy select
Hello, I have a mysql select query that has a groupBy. I want to count all the records after the group by statement. Is there a way for this directly from mysql ? thanks. ...
mysql
Upgrading mysql from 4.1.22 to 5.0
Hi, I'm trying to upgrade our company's 4.1.22 version of MySQL to 5. I'm using sudo yum --enablerepo=centosplus upgrade mysql* but keep getting an error of conflicted files with the 4.1 version. Does that mean there really isn't any other way than uninstalling 4.1 and installing 5.0? I have read that using the yum upgrade command...
mysql condition to select empty fulltext cell?
i need write a select query to find the number of rows which have an empty fulltext field but for some reason both: select count(id) from table where field is null; and select count(id) from table where field = ""; don't seem to work! what else is there?! ...
Faster to get one row from DB or count number of rows
Hi, I was wondering if its faster to retrieve the "count" of the number of rows or to retrieve just 1 row using limit. The purpose being to see whether theres any row when given certain Where conditions. ...
How to store MySQL query results in another Table?
How to store results from following query into another table. Considering there is an appropriate table already created. SELECT labels.label,shortabstracts.ShortAbstract,images.LinkToImage,types.Type FROM ner.images,ner.labels,ner.shortabstracts,ner.types WHERE labels.Resource=images.Resource AND labels.Resource=shortabstracts.Reso...
Counting and joining two tables
Eventhosts – containing the three regular hosts and an "other" field (if someone is replacing them) or edit: being a guest host (in addition to the regulars) eventid | host (SET[Steve,Tim,Brian,other]) ------------------------------------------- 1 | Steve 2 | Tim 3 | Brian 4 | Brian 5 | other 6 | othe...
WordPress - Synchronizing local folder and SQL table with server ftp folder / table
I'm about to design / develop a WordPress website for client that wants me to build the site on his server. I prefer to develop locally, and without lag time, but I'd like to find a middle ground that works for both of us. I have transmit for FTP and know there is a way to synchronize a local folder with the server. Although, My concern...
Database INSERT does not take place
My code is as follows: <?php include("config.php"); $ip=$_SERVER['REMOTE_ADDR']; if($_POST['id']) { $id=$_POST['id']; $id = mysql_escape_String($id); $ip_sql=mysql_query("select ip_add from Voting_IP where mes_id_fk='$id' and ip_add='$ip'"); $count=mysql_num_rows($ip_sql); if($count==0) { $sql = "upd...
my.cnf: Improve speed of adding new columns and indexes in large MySQL tables
I have a large table (> 10 million rows) which I am adding new columns and indexes to. This is taking progressively longer and longer to complete. I have a powerful server with 16GB of memory available. What are the best settings in my.cnf to increase in order to speed this process up? ...
Which grants are required to see privileges for *other* users in information_schema.schema_privileges?
For a specific database in a MySQL Server 5.5 I would like to view all grants given to any user. To do this I have been reading from the information_schema.schema_privileges table using a select statement as follows: select * from information_schema.schema_privileges where table_schema='myproject'; The problem is that I only see my ow...
How to map a database view using Ruby ActiveRecord?
How to map a mysql database view using Ruby ActiveRecord in Rails? ...
Left Join not returning all rows
I have this query in MySQL: SELECT pr.*, pr7.value AS `room_price_high` FROM `jos_hp_properties` pr LEFT OUTER JOIN `jos_hp_properties2` pr7 ON pr7.property=pr.id WHERE pr7.field=23 The jos_hp_properties table has 27 rows but the query only returns one. Based on this question I think it may be because of the WHERE clause. The jos_hp_p...
Optimize mysql table ?
Here is my actual table schema (I'm using Mysql) : Table experiment : code(int) sample_1_id sample_2_id ... until ... sample_12_id rna_1_id rna_2_id ... until ... rna_12_id experiment_start How can I optimize both part : sample_n_id and rna_n_id (all are bigint(20) and allow null=true) ? About values : we can have : ex : sample_1_id ...
Display a ranking grid for game : optimization of left outer join and find a player
Hello, I want to do a ranking grid. I have a table with different values indexed by a key: Table SimpleValue : key varchar, value int, playerId int I have a player which have several SimpleValue. Table Player: id int, nickname varchar Now imagine these records: SimpleValue: Key value playerId for 1 1 int ...
PHP PDO - Num Rows
PDO apparently has no means to count the number of rows returned from a select query (mysqli has the num_rows variable). Is there a way to do this, short of using count($results->fetchAll()) ? ...
How to join 1 table twice in the same query and keep results separate
Hi, we're building a scheduler system and have a couple of situations where we're trying to get some notes from a table to display. We've looked at other answers and none seem to quite match this problem. The bookings table holds a numeric reference to both a client note (if present) and a stylist note (if present). The notes table ho...
Search MySQL Database
Hey, I have put together an iPhone web app and i'm currently reading and displaying data from a MySQL database. I have added a search bar to this page and was wondering what the best way would be to search the content on the page. http://j.mp/c6lV8c ...
MySQL Sub-query.. Doesn't provide proper information after 3 entries into table.
After I get 3 rows in my forum_threads table this no longer does it's job. Which is to organize a list of active forum threads and put the most recently responded-to thread at the top of the list, followed by second most recent posted-to thread, followed by third, fourth, etc. Like I said, the query works wonders up until there is a fou...
Why Banks or Financial Companies prefer Oracle than other RDBMS for their "Core" systems?
I'd like to know why most Banks or Financial companies prefer Oracle than other RDBMS for their core systems (the absolutely minimum features that a Bank must support). I found a few answers that didn't satisfy me. For example: Oracle has more features. But features for what? Can't you implement that in application level if you were not ...
How to Connect to a Mysql database using PHP?
<?php mysql_connect("localhost", "username", "password") or die(mysql_error()); echo "Connected to MySQL<br />"; ?> This is the code I am using to check if I am able to connect to Mysql. I am having problem connecting using this code. Should I change localhost to the name of the website? I tried ("www.abc.com","login username", "pass...