Counting the number of rows.

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
JellyFish
DevNet Resident
Posts: 1361
Joined: Tue Feb 14, 2006 7:18 pm
Location: San Diego, CA

Counting the number of rows.

Post 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.
mattareddy
Forum Newbie
Posts: 7
Joined: Sun Mar 08, 2009 12:01 am

Re: Counting the number of rows.

Post by mattareddy »

noumber of rows(mysql_rows_num("select * from emp");)
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: Counting the number of rows.

Post by Benjamin »

Code: Select all

 
SELECT COUNT(*) AS total FROM TABLE
 
The total will be in a field called total.
User avatar
JellyFish
DevNet Resident
Posts: 1361
Joined: Tue Feb 14, 2006 7:18 pm
Location: San Diego, CA

Re: Counting the number of rows.

Post 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.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: Counting the number of rows.

Post by John Cartwright »

I think when you use the word "cram" on non-nested SQL, its best to use multiple SQL statements :)
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

Re: Counting the number of rows.

Post by Bill H »

I think when you use the word "cram" on non-nested SQL, its best to use multiple SQL statements
:P
Post Reply