Page 1 of 1

Counting the number of rows.

Posted: Sat Mar 07, 2009 9:18 pm
by JellyFish
Hey, I'm looking to count the number of rows in a result set. I'm not particularly interested in the fetching any kind of data from a table. Rather I'm just looking to count the number of rows of a particular query.

Code: Select all

$result = mysql_query("SELECT * FROM table1 WHERE active = 1")
I then go on getting the number of rows with php:

Code: Select all

mysql_num_rows($result);
My question is, is there a better way to do this? I don't really need the result set, so isn't this kind of a waste? I could replace the * (wildcard) with just a name of one field so that it's not as much of a waste of resources. Or is there a better way that involves SQL do to the trick? Maybe there's a SQL statement that returns a result set with one row with one field containing the number of rows returned based on the WHERE clause's criteria.

Is there a better way?

Thanks for reading.

Re: Counting the number of rows.

Posted: Sun Mar 08, 2009 12:10 am
by mattareddy
noumber of rows(mysql_rows_num("select * from emp");)

Re: Counting the number of rows.

Posted: Sun Mar 08, 2009 12:11 am
by Benjamin

Code: Select all

 
SELECT COUNT(*) AS total FROM TABLE
 
The total will be in a field called total.

Re: Counting the number of rows.

Posted: Sun Mar 08, 2009 11:39 am
by JellyFish
Thanks astions, this is just what I was looking for.

Another thing I was wondering, sense after I count the rows in one table I'm going to be doing more queries, maybe there's a way to have all my SQL in one statement. Is there a way to update a row in one table if the number of rows in another table equals 1, and if not return a result set? This is a lot to jam into one statement, but maybe it'd be faster then having three separate statements.

Code: Select all

 
//First query, count the number of rows in one table
$result = mysql_query("SELECT COUNT(*) FROM `posts` WHERE `email` = '$email'") or die(mysql_error());
if (mysql_result($result, 0, 0) == 1)
{
$key = makeKey();
//Third query, update another table
mysql_query("UPDATE `users` SET `Key` = '$key' WHERE `Email` = '$email'") or die(mysql_error());
}
else
{
//The third query, return a result set instead of updating the table row
$result = mysql_query("SELECT Key FROM users WHERE Email = '$email'") or die(mysql_error());
}
 
I wonder if there's a way to combine all this into one SQL query:

Code: Select all

 
/*Something Like*/
IF (COUNT(*) FROM posts WHERE email = '$email') = 1 (UPDATE users SET Key = '$key' WHERE Email = '$email') ELSE (SELECT Key FROM users WHERE Email = '$email');
 
Idk, it's a crazy idea.

Re: Counting the number of rows.

Posted: Sun Mar 08, 2009 12:46 pm
by John Cartwright
I think when you use the word "cram" on non-nested SQL, its best to use multiple SQL statements :)

Re: Counting the number of rows.

Posted: Sun Mar 08, 2009 1:58 pm
by Bill H
I think when you use the word "cram" on non-nested SQL, its best to use multiple SQL statements
:P