php mysql count

[yoyobebob]

alright,

i have a table in mysql with a bunch of rows. I want to count how many rows are in the table.

so i use the count function, right:

<?php
	require("db_connect.php");
	mysql_select_db("DATABASE", $con);
	$sql = "COUNT(hitstring) FROM hits";
	mysql_query($sql);
	?>

How do I echo the result?
Thanks

[Thomas]

<?
$my_query = mysql_query($query);
$num_rows = mysql_num_rows($my_query);
echo $num_rows;
?>

The PHP function for counting MySql rows is 'mysql_num_rows();'.

[yoyobebob]

At 10/6/09 06:12 PM, Thomas wrote: <?
$my_query = mysql_query($query);
$num_rows = mysql_num_rows($my_query);
echo $num_rows;
?>

The PHP function for counting MySql rows is 'mysql_num_rows();'.

Ok,

i did this:

<?php
	require("db_connect.php");
	mysql_select_db("DATABASE", $con);
	$my_query = mysql_query("SELECT * hits");
	$num_rows = mysql_num_rows($my_query);
	echo $num_rows;
	?>

And it gives me this:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in ....

What's wrong?

[urbn]

At 10/6/09 06:23 PM, yoyobebob wrote: What's wrong?

Easiest way is to die the mysql_error:

<?php
	require("db_connect.php");
	mysql_select_db("DATABASE", $con);
	$my_query = mysql_query("SELECT * hits")or die(mysql_error();
	$num_rows = mysql_num_rows($my_query);
	echo $num_rows;
	?>

[henke37]

You should not use the php function mysql_num_rows, because it is inefficient. You had the right thing going before this was brought up.

However, you forgot the query type keyword, or in stupid: "Select".

[blah569]

Using COUNT is much faster than counting every row each time if you're not using the data. Something like this works:

$getcount = mysql_query("SELECT COUNT(*) as `num` FROM `hits` WHERE `username` = '$user'")or die("FSAFSDF:  ".mysql_error());
$count = mysql_fetch_array($getcount);

echo $count['num'];

[blah569]

Ahh sorry, a little tired. I'm not sure why I added the WHERE, you will want to remove the WHERE `user` = '$usern'

[citricsquid]

How fast is mysql_num_rows compared to count, is it a noticeable difference?

[liljim]

At 10/7/09 08:45 AM, citricsquid wrote: How fast is mysql_num_rows compared to count, is it a noticeable difference?

Yes. "SELECT COUNT(*) FROM table"; will always return ONE row with one cell (unless you're doing somethign like "SELECT COUNT(*), AVG(somefield) FROM table[ GROUP BY whatever]", which would give you two cells etc), even if the output is 0 (i.e. the dataset is empty.) "SELECT [whatever] FROM table [WHERE... GROUP BY.... ORDER BY....etc]"; and then analysing that dataset with mysql_num_rows() will force the database to retrieve every row in the database that matches the query. Doing this just to get a tally of the number of entries is assinine.

Imagine you have 1 million rows in your table and all you want is the total number of entries in that table. You really don't want to sit waiting around for a query to fetch every single row back from the table just to get that total, which is what you'd be doing by selecting a dataset and then running that through mysql_num_rows().

Here's an example of output from a COUNT():

+----------+
| COUNT(*) |
+----------+
|  1951870 |
+----------+
1 row in set (0.00 sec)

[henke37]

A count query is an O(1) operation on all table engines that mysql supports. It just reads the stored count from memory. It doesn't even have to open any file.

On the other hand, reading the full table content, just to get the row count is an O(n) operation. And that can take quite some time if there is a lot of data.