|
|
 |
mysql_num_rows (PHP 3, PHP 4, PHP 5) mysql_num_rows -- Get number of rows in result Descriptionint mysql_num_rows ( resource result )
Retrieves the number of rows from a result set. This command is only valid
for SELECT statements. To retrieve the number of rows affected by a INSERT,
UPDATE, or DELETE query, use mysql_affected_rows().
Return Values
The number of rows in a result set on success, or FALSE on failure.
Examples
Example 1. mysql_num_rows() example |
<?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";
?>
|
|
NotesNote:
If you use mysql_unbuffered_query(),
mysql_num_rows() will not return the
correct value until all the rows in the result set have been
retrieved.
Note:
For downward compatibility, the following
deprecated alias may be used:
mysql_numrows()
User Contributed Notes
mysql_num_rows
jsirovic AT g male dot com
19-May-2005 10:34
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.
alex dot feinberg 4t gm41l
28-Apr-2005 05:56
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.
liamvictor at gmail dot com
13-Apr-2005 07:22
// 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()."</p>");
$num_rows = mysql_num_rows($result);
if ($num_rows){
while ($myrow = mysql_fetch_row($result)){
$first_name = $myrow[0];
print ("<p>Line:".__LINE__." num_rows:$num_rows first_name:$first_name <br> $query</p>");
}
}else{
print ("<p>Password error.</p>");
}
// 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()."</p>");
$num_rows = mysql_num_rows($result);
if ($num_rows){
while ($myrow = mysql_fetch_row($result)){
$count_first_name = $myrow[0];
print ("<p>Line:".__LINE__." num_rows:$num_rows count:$count_first_name <br> $query</p>");
}
}else{
print ("<p>Password error.</p>");
}
dzver at abv dot bg
20-Feb-2005 07:00
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().
29-Jan-2005 07: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 :)
oran at trifeed dot com
15-Dec-2004 11:06
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
pjoe444 at yahoo dot com
18-Nov-2004 05:38
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
pjoe444 at yahoo dot com
18-Nov-2004 04:24
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
They call me .. "Blaqy"
08-Nov-2004 09:30
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.
sam at liddicott dot com
04-Nov-2004 07:40
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
aaronp123 att yahoo dott comm
21-Feb-2003 07:40
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.
webmaster at _NOSPAM_elite-gaming dot com
10-Oct-2002 09:48
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.
tac at smokescreen dot org
14-Jan-2002 01:58
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.
philip at cornado dot c()m
06-May-2001 01:37
| |
mysql_num_fields