mysql_num_rows

(PHP 4, PHP 5)

mysql_num_rows — Get number of rows in result

Warning

This extension was deprecated in PHP 5.5.0, and it was removed in PHP 7.0.0.
Instead, the MySQLi or PDO_MySQL extension should be used.
See also MySQL: choosing an API guide and
related FAQ for more information.
Alternatives to this function include:

Description

intmysql_num_rows
( resource$result
)

Retrieves the number of rows from a result set. This command is only valid
for statements like SELECT or SHOW that return an actual result set.
To retrieve the number of rows affected by a INSERT, UPDATE, REPLACE or
DELETE query, use mysql_affected_rows().

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.

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.

In preventing the race condition for the SQL_CALC_FOUND_ROWS and FOUND_ROWS() operations, it can become complicated and somewhat kludgy to include the FOUND_ROWS() result in the actual result set, especially for complex queries and/or result ordering. The query gets more complex, you may have trouble isolating/excluding the FOUND_ROWS() result, and mysql_num_rows() will return the number of actual results + 1, all of which makes your code messier and harder to read. However, the race condition is real and must be dealt with.

A alternative and cleaner method is to explicitly lock the table using a WRITE lock (preventing other processes from reading/writing to the table). The downsides I can see are a performance hit, and your mysql user must have lock permissions.

Actually I am a little ashamed to be saying this, but I stand corrected about a rather old note I posted on 17-Jul-2007 06:44.

Using SQL_CALC_FOUND_ROWS and FOUND_ROWS( ) will NOT trigger a race condition on MySQL, as that would pretty much defy their entire purpose.

The results for their usage is actually unique per connection session as it is impossible for processes to share anything in PHP. As far as PHP is concerned, each request represents a new connection to MySQL as each request is isolated to its own process.

Set the connection and query information for something that matches your environment.

Run the script once with the Sleep query string and once again without it. Its important to run them both at the same time. Use Apache ab or something similar, or even easier, just open two browser tabs. For example:

This happens while the first instance of the script is sleeping. If a race condition existed, when the first instance of the script wakes up, the result of the FOUND_ROWS( ) it executes should be the number of rows in the SQL query the second instance of the script executed.

But when you run them, this is not the case. The first instance of the script returns the number of rows of its OWN query, which is:

You can see, that you can even ORDER the final result anyway you like ;)Then you can fetch the result like this:<?php$res = mysql_query($sql);$count = mysql_fetch_assoc($res); while($row = mysql_fetch_assoc($res)){ unset($row["0"]); //get rid of the "sorting col"print_r($row); //or whatever ;)}echo $count["id"]; // the total number of rows?>