Delicious Bookmark this on Delicious Share on Facebook SlashdotSlashdot It! Digg! Digg



PHP : Function Reference : MySQL Functions : mysql_num_rows

mysql_num_rows

Get number of rows in result (PHP 4, PHP 5, PECL mysql:1.0)
int mysql_num_rows ( resource result )

Example 1453. mysql_num_rows() example

copy to clipboard
<?php

$link = mysql_connect("localhost", "mysql_user", "mysql_password");
mysql_select_db("database", $link);

$result = mysql_query("SELECT * FROM table1", $link);
$num_rows = mysql_num_rows($result);

echo "$num_rows Rows\n";

?>

Related Examples ( Source code ) » mysql_num_rows

Examples ( Source code ) » Authenticate user
Examples ( Source code ) » Open browser password dialog and authenticate user based on database
Examples ( Source code ) » Adding a Row to a Table
Examples ( Source code ) » Listing All Rows and Fields in a Table
Examples ( Source code ) » Finding the Number of Rows Returned by a SELECT Statement with mysql_num_rows()
Examples ( Source code ) » Listing the Databases Available in a Connection
Examples ( Source code ) » Connect to MySQL
Examples ( Source code ) » Get returned row count
Examples ( Source code ) » Create table and install data
Examples ( Source code ) » Listing Tables in a Database

Code Examples / Notes » mysql_num_rows

wil1488
To use SQL COUNT function, without select the source...
see an example:
<?
//MAKE THE CONNECTION WITH DATABASE
$my_table = mysql_query("SELECT COUNT(*) as TOTALFOUND from table", $link); //EXECUTE SQL CODE
Note: will return the total on TOTALFOUND
print (mysql_result($my_table,0,"TOTALFOUND")); //use the field camp to get the total from your SQL query!
?>
Thanks, good luck.

jsirovic
The reason it's just as slow is that to count that way as it is to fetch, minus the data transfer.
Even when executing a limit query, when you ask it to fetch the number of total rows, it must scan the whole table every time to calculate the count.

webmaster
The fastest way to get the number of rows in a table is doing this:
$total = mysql_result(mysql_query("SELECT COUNT(id) FROM yourtable"),0);
As long as there are no NULL ids (shouldnt be), it will return the correct rows extremely fast.  If you already used yourtable though, it is faster to use mysql_num_rows() on the result of it.

sam
Some user comments on this page, and some resources including the FAQ at :
http://www.faqts.com/knowledge_base/view.phtml/aid/114/fid/12 suggest using count(*) to count the number of rows
This is not a particularly universal solution, and those who read these comments on this page should also be aware that
select count(*) may not give correct results if you are using "group by" or "having" in your query, as count(*) is an agregate function and resets eachtime a group-by column changes.
select sum(..) ... left join .. group by ... having ...
can be an alternative to sub-selects in mysql 3, and such queries cannot have the select fields replaced by count(*) to give good results, it just doesn't work.
Sam

philip
Regarding SQL count(), see this faq :
* http://www.faqts.com/knowledge_base/view.phtml/aid/114/fid/12
Note: If you already have a $result, use mysql_num_rows() on it otherwise use SQL count().  Don't SELECT data just for a count.

pjoe444
Re my last entry:
This seems the best workaround to get an 'ordinary' loop going, with possibility of altering output according to row number
(eg laying out a schedule)
$rowno=mysql_num_rows($result);
for ($i=0; $i<mysql_num_rows($result); $i++) {
$row = mysql_fetch_assoc($result);
print "<div class=\"showing\">";
print "<b>".$row['timeon']."-".$row['timeoff']."</b> ".$row['event']."<br />;
if ($i!=$rowno-1) {
   print "other-html-within-sched-here</div>";
   }  
else print "end-last-entry-html-here</div>";
}  //close loop

alex dot feinberg 4t gm41l
Re dzver at abv dot bg's note...
I just ran some tests using MySQL Super Smack. Surprisingly, a SELECT * followed by a SELECT COUNT(*) actually was close in speed to a SELECT SQL_CALC_FOUND_ROWS * followed by a SELECT FOUND_ROWS(), but the SQL_CALC_FOUND_ROWS solution was still a bit faster.
Perhaps it varies by table structure? Either way, it might be worth checking which is faster for your application.

simon_nuttall
Object oriented version of wil1488 at gmail dot com's comment for counting table rows:
<?php
$result = $mysqli->query("SELECT COUNT(*) as TOTALFOUND from table");
$row_array=$result->fetch_array(MYSQLI_ASSOC);
print($row_array['TOTALFOUND']);
?>

tac
MySQL 4.0 supports a fabulous new feature that allows you to get the number of rows that would have been returned if the query did not have a LIMIT clause.  To use it, you need to add SQL_CALC_FOUND_ROWS to the query, e.g.
$sql = "Select SQL_CALC_FOUND_ROWS * from table where state='CA' limit 50";
$result = mysql_query($sql);
$sql = "Select FOUND_ROWS()";
$count_result = mysql_query($sql);
You now have the total number of rows in table that match the criteria.  This is great for knowing the total number of records when browsing through a list.

they call me .. "blaqy"
Just wanted to add my 2 cents in regards to the mysql functions:
SQL_CALC_FOUND_ROWS
SELECT FOUND_ROWS()
It was difficult finding any information on PHP usage.
What wasn't (or currently isn't) mentioned is that:
$query = "SELECT FOUND_ROWS()";
Will return a 'recordset' .. that holds the 'number of rows', not the actual value.  So the correct usage is:
$result = mysql_query($query);
$total_records = mysql_result($result, 0);
Not:
$total_records = mysql_query($query);
As some of the literature .. may suggest to you.

dzver
It is faster to run second query "select count(...) from ... ", than adding SQL_CALC_FOUND_ROWS to your first query, and then using select FOUND_ROWS() + mysql_num_rows().

30-jan-2005 01:18
In response to oran at trifeed dot com:
You are only experiencing this behaviour because you have not given your FOUND_ROWS() result an alias:
$qry = mysql_query ( 'SELECT FOUND_ROWS() AS total' );
$rst = mysql_fetch_array ( $qry, MYSQL_ASSOC );
echo $rst['total'];
Sean :)

deaggi
In Reply to the last post: This may not always work correctly, as $object->doesExist would contain a result, not a boolean value. A better way (using the same method) would be using a cast:
<?php
class Object {
 var $doesExist = false;
 [...]
 function load() {
   $result = mysql_query('...');
   $this->doesExist = (bool) ($res = mysql_fetch_array($result))
   [...]
 }
}
?>
johannes

rancid m+m
In PHP 5, mysql_affected_rows looks for the link as the first parameter, not a MySQL result.

webmaster dasourcerer net
In one of my applications, I had to let an object know wether it exists in the database or not. I found a cheap solution w/o the usage of mysql_num_rows():
<?php
class Object {
 var $doesExist = false;
 [...]
 function load() {
   $result = mysql_query('...');
   $this->doesExist = ($res = mysql_fetch_array($result))
   [...]
 }
}
?>

eriline dot mees
If you have a problem using:
$res1=mysql_query("select SQL_CALC_FOUND_ROWS * from minutabel LIMIT 20");
$res2=mysql_query("select FOUND_ROWS()");
($res2 got always "0")
then be sure the php.ini config option "mysql.trace_mode" is "Off".
You can use
$vana=ini_set('mysql.trace_mode','Off');
// do your $res1 and $res2 queries.
ini_set('mysql.trace_mode',$vana);
for temporary disabling.

thismelancholy
I´ve noticed that on some servers one need to put "or die(mysql_error())" when you use mysql_num_rows(), or else it will throw an error. :S

aaronp123 att yahoo dott comm
I may indeed be the only one ever to encounter this - however if you have a myisam table with one row, and you search with valid table and column name for a result where you might expect 0 rows, you will not get 0, you will get 1, which is the myisam optimised response when a table has 0 or one rows.  Under "5.2.4 How MySQL Optimises WHERE Clauses" it reads:
*Early detection of invalid constant expressions. MySQL quickly detects that some SELECT statements are impossible and returns no rows.
and
*All constant tables are read first, before any other tables in the query. A constant table is:
1) An empty table or a table with 1 row.
2) A table that is used with a WHERE clause on a UNIQUE index, or a PRIMARY KEY, where all index parts are used with constant expressions and the index parts are defined as NOT NULL.
Hopefully this will keep someone from staying up all night with 1146 errors, unless I am completely mistaken in thinking I have this figured out.

jonbendi @t stud o ntnu o no
I find that mysql_num_rows() overlook LIMIT clauses.
For instance:
//table has 700 rows
$command = "SELECT * FROM table LIMIT 500";
$q = mysql_query($command);
$rows = mysql_num_rows($q);
//$rows is 700

mancini
here is a really fast mysql_num_rows alternative that makes use of the SELECT FOUND_ROWS() MySQL function , it only reads a single row and it is really helpfull if you are counting multiple tables with thousands of rows
<?php
function get_rows ($table) {
       $temp = mysql_query("SELECT SQL_CALC_FOUND_ROWS * FROM $table LIMIT 1");
       $result = mysql_query("SELECT FOUND_ROWS()");
       $total = mysql_fetch_row($result);
       return $total[0];
}
?>

oran
For me
SELECT SQL_CALC_FOUND_ROWS together with
SELECT FOUND_ROWS()
Only worked with the following syntax:
$result = @mysql_query($query);
$resultTotal = @mysql_query("SELECT FOUND_ROWS()");
$res= mysql_fetch_array($resultTotal);
echo $res['FOUND_ROWS()'];
hope it helped
oran
http://www.trifeed.com

jonas
A small tip concerning SQL_CALC_FOUND_ROWS and FOUND_ROWS()
Remember that you can us "AS" when working with mysql_fetch_assoc.
$sql="
SELECT
FOUND_ROWS() AS `found_rows`;
";
$result = mysql_query($sql);
$myrow = mysql_fetch_assoc($result);
$row_count = $myrow['found_rows'];
echo $row_count;

pjoe444
A pity there seems no way of getting the CURRENT  row number that's under iteration in a typical loop,
such as:
while ($row = mysql_fetch_assoc($result)) { }
After all there is an array of row arrays, as signified by
mysql_num_rows($result):
Say this gives "40 rows" : it would be useful to know when the iteration is on row 39.
The nearest seems to be "data seek":but it connects directly to a
row number eg (from mysql_data_seek page)
for ($i = mysql_num_rows($result) - 1; $i >= 0; $i--) {
  if (!mysql_data_seek($result, $i)) {
      echo "Cannot seek to row $i: " . mysql_error() . "\n";
      continue;
  }
= it still wouldn't solve knowing what row number you're on in an ordinary loop.
One reason for this situation is the php fetch (fetch-a-single-row) construction, without any reasonable FOR loop possibility with row numbers.
Suggestion:
$Rows[$i] possibility where
$i would be the row number
$Rows[$row[], $row[], $row[].....]
            0            1            2     etc
-- the excellent retrieval WITHIN a row ( $row[$i] ),
while certainly more important,  is not matched by
similar possibilities for rows themselves.
and Count($result) doesnt work of course, $result being a
mere ticket-identifier...
Peter T

typer85
A note on the following usage; that suggest to use several MySQL Functions to get the number of Table Records.
You may be familiar with following:
<?php
$sqlQuery = 'Select SQL_CALC_FOUND_ROWS `MyField` From `MyTable` Limit 1;';
$sqlQuery_1 = 'Select FOUND_ROWS( );';
?>
I omitted the actual connection to MySQL and the execution of the query, but you get the idea.
I did some tests and on a fairly high traffic web site, one that executes several queries quite often and found that using this combination of MySQL Functions can actually result in wrong results.
For example, assume I have two queries to get the number of Table Records in two different Tables. So in essence, we are executing 4 queries ( 2 queries for each Table ).
If two different requests come in through PHP, your going to run into problems. Note than when I mean request, I mean two different clients requesting your PHP page.
---------------
Request 1:
---------------
Execute: SQL_CALC_FOUND_ROWS On Table 1
---------------
Request 2:
---------------
Execute: SQL_CALC_FOUND_ROWS On Table 2
---------------
Request 1:
---------------
Execute: Select FOUND_ROWS( )
At this point, you see the race condition that occurred. While Request 1 was being executed, Request 2 came in.
At this point Request 1 will return the number of Table Records in Table 2 and not Table 1 as expected!
Why? Because MySQL does not differ between requests. Each query is in a queue waiting its turn. As soon as its turn comes in it will be executed my MySQL.
The MySQL Function Select FOUND_ROWS( ) will return the result of the last SQL_CALC_FOUND_ROWS!
Keep in mind.

liamvictor
// this works properly
$query = "SELECT first_name FROM users_tbl WHERE user_id='$user_id' AND password = '$p0' ";
$result = mysql_query($query, $connection) or die ("<p class=err>Error - Query failed: ".mysql_error()."");
$num_rows = mysql_num_rows($result);
if ($num_rows){
while ($myrow = mysql_fetch_row($result)){
$first_name = $myrow[0];
print ("

Line:".__LINE__." num_rows:$num_rows first_name:$first_name
$query");
}
}else{
print ("

Password error.");
}
// Here 1 row is returned with a value of 0 when the password is wrong rather than reporting the password error.
$query = "SELECT COUNT(first_name) FROM users_tbl WHERE user_id='$user_id' AND password = '$p0' ";
$result = mysql_query($query, $connection) or die ("<p class=err>Error - Query failed: ".mysql_error()."");
$num_rows = mysql_num_rows($result);
if ($num_rows){
while ($myrow = mysql_fetch_row($result)){
$count_first_name = $myrow[0];
print ("

Line:".__LINE__." num_rows:$num_rows count:$count_first_name
$query");
}
}else{
print ("

Password error.");
}

Change Language


Follow Navioo On Twitter
mysql_affected_rows
mysql_change_user
mysql_client_encoding
mysql_close
mysql_connect
mysql_create_db
mysql_data_seek
mysql_db_name
mysql_db_query
mysql_drop_db
mysql_errno
mysql_error
mysql_escape_string
mysql_fetch_array
mysql_fetch_assoc
mysql_fetch_field
mysql_fetch_lengths
mysql_fetch_object
mysql_fetch_row
mysql_field_flags
mysql_field_len
mysql_field_name
mysql_field_seek
mysql_field_table
mysql_field_type
mysql_free_result
mysql_get_client_info
mysql_get_host_info
mysql_get_proto_info
mysql_get_server_info
mysql_info
mysql_insert_id
mysql_list_dbs
mysql_list_fields
mysql_list_processes
mysql_list_tables
mysql_num_fields
mysql_num_rows
mysql_pconnect
mysql_ping
mysql_query
mysql_real_escape_string
mysql_result
mysql_select_db
mysql_set_charset
mysql_stat
mysql_tablename
mysql_thread_id
mysql_unbuffered_query
Terms of Service Privacy Policy Sitemap

©2024 -EPSILONSYS SOFTWARE inc

Other EPSILONSYS SOFTWARE Web Sites : Programming Resources | News Headline | Free local classifieds

eXTReMe Tracker