[RESOLVED] How little do I need to query just to get a count?

schwim

I have a portion of my script that simply needs to return a total, but the only way I know to do that is by a normal count.

Currently, the script does it with this:

  $query = mysql_query("SELECT * FROM ".$prefix."testimonials");

			$total = mysql_num_rows($query);

			if($total==0)

			{

				echo ("0");

			}else{

				echo $total;

			}

Now, I know that's very wasteful, getting everything for a count, so I can change that to just pull the id, but I'm wondering if there's an even less taxing way to get a count.

Any suggestions would be greatly appreciated.
thanks,
json

proxydude

Select COUNT(*) From tablename;

can be every column, dosen't mkae much of a differnce, if you were to use a column name, not sure how mysql would react, but i presume select more than one column would hog mysql

but either way you'll notice improvement

table

id name age etc

Select COUNT(name) From tablename;

schwim

Hi there proxydude,

Thanks very much for your help. I think I may have implemented it incorrectly though.

It used to show total, waiting and approved totals to be 8,5,3

Now it shows total waiting and approved to be 1,1,1

here's how I implemented what you suggested:

		        $query = mysql_query("SELECT COUNT(*) FROM ".$prefix."testimonials");

			$total = mysql_num_rows($query);

			if($total==0)

			{

				echo ("0");

			}else{

				echo $total;

			}



	        echo ("</li>

	        <li>Approved: ");



	        $query = mysql_query("SELECT COUNT(*) FROM ".$prefix."testimonials WHERE active=1");

			$total_app = mysql_num_rows($query);

			if($total_app==0)

			{

				echo ("0");

			}else{

				echo $total_app;

				echo (" (<a href=index.php?action=approved><i>view</i></a>)");

			}



	        echo ("</li>

	        <li>Waiting: ");



	        $query = mysql_query("SELECT COUNT(*) FROM ".$prefix."testimonials WHERE active=0");

			$total_wait = mysql_num_rows($query);

			if($total_wait==0)

			{

				echo ("0");

			}else{

				echo $total_wait;

				echo (" (<a href=index.php?action=waiting><i>view</i></a>)");

			}



	        echo ("</li>

	        </ul>

	        ");

}

Do you happen to see what I did wrong?

thanks,
json

proxydude

ok first off the reason why its return 1,1,1

if i was to do this

$my_array = count(array("one","two","three));
print $my_array;// this would return 3

now imagine this was the return of a mysql query

basically what your doing is counting the result of the count

ie count(3); // returns 1

its kinda hard to exlpain needless to say

$query = mysql_query("SELECT COUNT(*) FROM ".$prefix."testimonials");

is returning the already counted fields

so, i use my own class for database functions ive actually forgotten the php defined functions but

assuming the return is

3,4,6
print "$query[0] $query[1] $query[2]";

[edit]

as i said i dont rember the actuall php mysql functions cause i use a class to handle that

but you may have to use mysql_result() or somthing becase just printing $query[1] would give you a result id

i think it may be something like

print mysql_result($query[1]);

www.php.net/mysql

Examples

Example 1. mysql_result() example
<?php
$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');
if (!$link) {
   die('Could not connect: ' . mysql_error());
}
$result = mysql_query('SELECT name FROM work.employee');
if (!$result) {
   die('Could not query:' . mysql_error());
}
echo mysql_result($result, 2); // outputs third employee's name

mysql_close($link);
?>

$query = mysql_query("SELECT COUNT(*) FROM ".$prefix."testimonials"); // 6,6,6

            $total = mysql_num_rows($query);  //1,1,1

cahva

Huh! Now that was hard explained! (and wrong Im afraid) 🙂

The reason why its giving 1,1,1 is because the rows will always return 1 with mysql_num_rows because mysql returns you 1 row, the result! 🙂

Just change this:

$total = mysql_num_rows($query);

..into this

$total = mysql_result($query,0);

schwim

cahva wrote:
Huh! Now that was hard explained! (and wrong Im afraid) 🙂

The reason why its giving 1,1,1 is because the rows will always return 1 with mysql_num_rows because mysql returns you 1 row, the result! 🙂

Just change this:
$total = mysql_num_rows($query);
..into this
$total = mysql_result($query,0);

That did it, thanks very much!

Can I ask why the ",0" was added to the end of that statement?

thanks,
json

Weedpacket

proxydude wrote:
* can be every column, dosen't mkae much of a differnce, if you were to use a column name, not sure how mysql would react, but i presume select more than one column would hog mysql

This is wrong as well (and not just the writing). It does make a difference if a column name is used, because if that column contains NULLs, then the rows containing them won't be counted. It also puts more load on the database because it has to check that column's values to see if they are null or not.

schwim wrote:
Can I ask why the ",0" was added to the end of that statement?

You could, but you'd probably get an answer more quickly by looking up the function in the manual.

schwim

You could, but you'd probably get an answer more quickly by looking up the function in the manual.

You were working off of the mistaken assumption that I would think to do that, so in this instance, it took me longer because someone had to tell me to look 🙂

I'm sure that next time, providing I actually think to do so, looking it up myself will be much quicker.

Thanks for pointing me in the right direction,
json

Weedpacket

What I did in Firefox was create a bookmark to http://www.php.net/%s and gave it a keyword of "php". So now I just type "php mysql_result" into the address bar and go.

schwim

holy cow, that's cool. I never knew that the keywords were useful! I thought it was in case I was afflicted with alzheimers, I might be able to find a link I need without calling for assistance....

thanks! I've added it to my toolbar...

hehe, too cool for words, really.

thanks,
json