[ozwald]

Is there a way I can use SELECT COUNT(*) as Num to count the number of rows in a table and return 0? I've got a table that sometimes has a lot of entries, and at other times has no entries. When it tries to count an empty table, it returns an error message. I don't won't to use SELECT * and mysql_num_rows() because when the table does have a lot of entries, that would slow things down. How can I do this? Thanks.

[NeverMind]

COUNT() shouldn't return an error! what error does it make?

I have used COUNT(*) many times on empty tables and it does return a zero without any problems!

[ozwald]

Here is the error I get:

Warning: mysql_result() [function.mysql-result]: Unable to jump to row 0 on MySQL result index 13 in /address/to/script.php on line 93

Here's line 93:

PHP Code:

$members = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM rbb_ppl_online WHERE UNIX_TIMESTAMP(activity) >= $limit_time AND member='y' GROUP BY ip_address"),0); 

[Sabu]

Your alternative would be to make the query to select all the valid rows, then use the function

Code:

mysql_num_rows($query);

which will return the number of rows.

[ozwald]

Yes, I've been using mysql_num_rows(), but the tables I'm querying have the potential to be very big, and I'm worrying about database load. It'd be much more efficient to get the COUNT(*) query to work.

[NeverMind]

how about:

PHP Code:

$fetch = mysql_query("SELECT COUNT(*) as Num FROM rbb_ppl_online WHERE UNIX_TIMESTAMP(activity) >= $limit_time AND member='y' GROUP BY ip_address");



$members = mysql_result($fetch, 0, 'Num'); 

you are aware that GROUP BY will not make the COUNT() count all records but instead it will group them by ip_address and returns them in sets?
if you want the whole number of records, remove the GROUP By clause.

[ozwald]

Originally Posted by NeverMind

how about:

PHP Code:

$fetch = mysql_query("SELECT COUNT(*) as Num FROM rbb_ppl_online WHERE UNIX_TIMESTAMP(activity) >= $limit_time AND member='y' GROUP BY ip_address");



$members = mysql_result($fetch , 'Num'); 

you are aware that GROUP BY will not make the COUNT() count all records but instead it will group them by ip_address and returns them in sets?
if you want the whole number of records, remove the GROUP By clause.

I tried that new code, I get the samer error message, except it points to the line with $members = mysql_result($fetch , 'Num');

BTW, I do want it to group the results so the script doesn't count the same IP as different people. This gives more accurate user online figures.

[ozwald]

BTW, it works just fine on my local machine - no error messages or anything. But online on a server, it gives me an error...would there maybe a way to ingore MySQL errors and not print them to the browser?

[Sabu]

This will let you know if there's an error in the query itself.

Code:

$fetch = mysql_query("SELECT COUNT(*) as Num FROM rbb_ppl_online WHERE UNIX_TIMESTAMP(activity) >= $limit_time AND member='y' GROUP BY ip_address") or die(mysql_error());

[ozwald]

Originally Posted by Sabu

This will let you know if there's an error in the query itself.

Code:

$fetch = mysql_query("SELECT COUNT(*) as Num FROM rbb_ppl_online WHERE UNIX_TIMESTAMP(activity) >= $limit_time AND member='y' GROUP BY ip_address") or die(mysql_error());

I tried that, and I get the exact same error message.