find number of records [resolved]

[Acidic]

How do you find the number of records in a DB using MySQL?

[visualAd]

I'm assuming that as you posted this in the PHP forum you are using mysql and PHP. If that is the case then the:

mysql_num_rows() function will do the trick on a previously run select query:

$result = mysql_query ("SELECT * FROM mytable;", $link);

echo ("Number of rows in table: " . mysql_num_rows($result)); 

[kows]

You can also do a query to count it itself without calling a PHP function to get it.

<?
  $qry = mysql_query("SELECT COUNT(*) FROM table WHERE conditions='value'");
  $arr = mysql_fetch_array($qry);
  echo "number of records: $arr[0]\n";
?>

[CornedBee]

Which is much much faster.

[The Hobo]

Originally posted by kows
You can also do a query to count it itself without calling a PHP function to get it.

<?
  $qry = mysql_query("SELECT COUNT(*) FROM table WHERE conditions='value'");
  $arr = mysql_fetch_array($qry);
  echo "number of records: $arr[0]\n";
?>

I know it's not what you meant, but mysql_query() and mysql_fetch_array() are both PHP functions.

[The Hobo]

Originally posted by CornedBee
Which is much much faster.

I'm not doubting you are calling you wrong, but where/how did you learn this?

[kows]

I'm guessing that for PHP to count your query it's going to have to requery your query and add the MySQL COUNT() function to it, or it could loop through the query you give it and increment the count every loop through..

Using MySQL's internal COUNT() function just runs through the table once and counts every record.. and since it's an internal function it will run faster than PHP having to count the query.

Anyways, that's just my theory.

[visualAd]

Originally posted by kows
I'm guessing that for PHP to count your query it's going to have to requery your query and add the MySQL COUNT() function to it, or it could loop through the query you give it and increment the count every loop through..

Using MySQL's internal COUNT() function just runs through the table once and counts every record.. and since it's an internal function it will run faster than PHP having to count the query.

Anyways, that's just my theory.

mysql always returns the number of records in a query. PHP need just query that.

As for which method is quicker - I'm not sure.

With my method mysql has to create a table containing all the records and fields and store it in memory.

In your method only the only record returned is one which contains the number of records in the table. So I guess that would mean that it is quicker.

[CornedBee]

Spot on visualAd. The lack of actual records returned makes for much space and speed savings. ESPECIALLY if the database is on a different server than the script (uncommon).

[Electroman]

Well it would be best to use the PHP count function if your going to be needing all the records anyway. However if your only interested in how many it'd be better to use MySQL count(primary key). That probably went with out saying but I thought I'd stop the lot of you just deciding to stick with one of these for all situations .

[Acidic]

OK, thanks guys.

I think I'm still too new to PHP/MySQL to comprehend what is being said. For me it works, I know I shouldn't think like that, but I do.