Php num rows
mysqli_result::$num_rows
mysqli_result::$num_rows — mysqli_num_rows — Получает число рядов в результирующей выборке
Описание
Возвращает число рядов в результирующей выборке.
Поведение функции mysqli_num_rows() зависит от того, используется ли буферизованная или не буферизованная результирующая выборка. Для небуферизованной выборки mysqli_num_rows() не вернет правильное число рядов до тех пор, пока все ряды не будут получены и обработаны.
Список параметров
Только для процедурного стиля: Идентификатор результата запроса, полученный с помощью mysqli_query() , mysqli_store_result() или mysqli_use_result() .
Возвращаемые значения
Возвращает число рядов в результирующей выборке.
Замечание:
Если число рядов больше чем PHP_INT_MAX , то число будет возвращено в виде строки.
Примеры
Пример #1 Объектно-ориентированный стиль
query ( «SELECT Code, Name FROM Country ORDER BY Name» )) <
/* определение числа рядов в выборке */
$row_cnt = $result -> num_rows ;
printf ( «В выборке %d рядов.n» , $row_cnt );
/* закрытие выборки */
$result -> close ();
>
/* закрытие соединения */
$mysqli -> close ();
?>
Пример #2 Процедурный стиль
Результат выполнения данных примеров:
Смотрите также
- mysqli_affected_rows() — Получает число строк, затронутых предыдущей операцией MySQL
- mysqli_store_result() — Передает на клиента результирующий набор последнего запроса
- mysqli_use_result() — Готовит результирующий набор на сервере к использованию
- mysqli_query() — Выполняет запрос к базе данных
User Contributed Notes 5 notes
If you have problems making work this num_rows, you have to declare ->store_result() first.
prepare ( «SELECT * FROM table1» );
$query -> execute ();
$query -> store_result ();
$rows = $query -> num_rows ;
// Return 4 for example
?>
The method signature should be:
mixed mysqli_num_rows (mysqli_result $result)
«If the number of rows is greater than PHP_INT_MAX, the number will be returned as a string.»
This function doesn’t work with LIMIT used jointly with SQL_CALC_FOUND_ROWS. If you want to obtain the total rows found you must do it manually, example:
con -> query ( «SELECT SQL_CALC_FOUND_ROWS id, erreur FROM Erreurs ORDER BY id DESC LIMIT $limit OFFSET $offset » );
while( $row = $result -> fetch_assoc ()) <
$errorList [] = new Erreur ( $row );
>
$result -> free ();
// $foundRows = $result->num_rows; // 25
$foundRows = $this -> con -> query ( «SELECT FOUND_ROWS() as foundRows» );
$this -> foundRows = $foundRows -> fetch_assoc (); // 178
return $errorList ;
>
?>
mysql_num_rows
mysql_num_rows — Возвращает количество рядов результата запроса
Данное расширение устарело, начиная с версии PHP 5.5.0, и будет удалено в будущем. Используйте вместо него MySQLi или PDO_MySQL. Смотрите также инструкцию MySQL: выбор API и соответствующий FAQ для получения более подробной информации. Альтернативы для данной функции:
Описание
Возвращает количество рядов результата запроса. Эта команда работает только с запросами SELECT или SHOW, возвращающих актуальный результат запроса. Чтобы получить количество рядов, обработанных функциями INSERT, UPDATE, REPLACE и DELETE, используйте функцию mysql_affected_rows() .
Список параметров
Обрабатываемый результат запроса. Этот результат может быть получен с помощью функции mysql_query() .
Возвращаемые значения
Количество рядов в результате запроса в случае успеха или FALSE в случае возникновения ошибки.
Примеры
Пример #1 Пример использования mysql_num_rows()
Примечания
Замечание:
При использовании mysql_unbuffered_query() функция mysql_num_rows() не вернёт корректного значения до тех пор, пока все ряды не будут получены.
Замечание:
Для обратной совместимости может быть использован следующий устаревший псевдоним: mysql_numrows()
Смотрите также
- mysql_affected_rows() — Возвращает число затронутых прошлой операцией рядов
- mysql_connect() — Открывает соединение с сервером MySQL
- mysql_data_seek() — Перемещает внутренний указатель в результате запроса
- mysql_select_db() — Выбирает базу данных MySQL
- mysql_query() — Посылает запрос MySQL
Коментарии
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.
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.
*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.
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.
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.
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)
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()’];
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’];
To use SQL COUNT function, without select the source.
Thanks, good luck.
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’];
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:
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.
Execute: SQL_CALC_FOUND_ROWS On Table 1
Execute: SQL_CALC_FOUND_ROWS On Table 2
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!
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():
doesExist = ( $res = mysql_fetch_array ( $result ))
[. ]
>
>
?>
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:
doesExist = (bool) ( $res = mysql_fetch_array ( $result ))
[. ]
>
>
?>
The following code can wrap it all up in a single query so you don’t have to worry about multiple client requests:
$stmtMain = $mysqli->prepare(«SELECT SQL_CALC_FOUND_ROWS jobid,title FROM tbljobs
LIMIT ?, ? UNION SELECT FOUND_ROWS(),’!!IgnoreCount!!’;»)
Then iterate through the results with something like:
Improvement to chrisdberry82 at gmail dot com’s code:
You can see, that you can even ORDER the final result anyway you like 😉
Then you can fetch the result like this:
And you got rid of the lousy IFs 😉
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.
query ( «LOCK TABLE t WRITE» );
$results = $mysqli -> query ( «SELECT id FROM t LIMIT 0,10» );
$totalNumResults = array_pop ( $mysqli -> query ( «SELECT FOUND_ROWS()» )-> fetch_row ());
$mysqli -> query ( «UNLOCK TABLES» );
?>
Now you may iterate through the results just like any other result set.
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.
To simulate this, create the following script:
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:
If a race condition existed, the results of the first instance of the script would equal the results of the second instance of the script.
For example, the second instance of the script will execute the following SQL query:
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:
So it turns out NO race condition exists, and every solution presented to combat this «issue» are pretty much not needed.
When mysql.trace_mode = On, SELECT FOUND_ROWS() allway returns 0. It looks like a bug.
If you have a table with 5 rows and issue:
SELECT SQL_CALC_FOUND_ROWS * FROM table LIMIT 1;
mysql_num_rows() returns 1 as expected.
If you issue «SELECT FOUND_ROWS()» just in sequence, in the same connection resource, it allways returns 0 rather than expected 5.
Just switch mysql.trace_mode to Off and things will work.
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:
doesExist = (bool) ( $res = mysql_fetch_array ( $result ))
[. ]
>
>
?>
mysql_num_rows
mysql_num_rows — Возвращает количество рядов результата запроса
Данное расширение устарело, начиная с версии PHP 5.5.0, и удалено в PHP 7.0.0. Используйте вместо него MySQLi или PDO_MySQL. Смотрите также инструкцию MySQL: выбор API и соответствующий FAQ для получения более подробной информации. Альтернативы для данной функции:
Описание
Возвращает количество рядов результата запроса. Эта команда работает только с запросами SELECT или SHOW, возвращающих актуальный результат запроса. Чтобы получить количество рядов, обработанных функциями INSERT, UPDATE, REPLACE и DELETE, используйте функцию mysql_affected_rows() .
Список параметров
Обрабатываемый результат запроса. Этот результат может быть получен с помощью функции mysql_query() .
Возвращаемые значения
Количество рядов в результате запроса в случае успеха или FALSE в случае возникновения ошибки.
Примеры
Пример #1 Пример использования mysql_num_rows()
Примечания
Замечание:
При использовании mysql_unbuffered_query() функция mysql_num_rows() не вернёт корректного значения до тех пор, пока все ряды не будут получены.
Замечание:
Для обратной совместимости может быть использован следующий устаревший псевдоним: mysql_numrows()
Смотрите также
- mysql_affected_rows() — Возвращает число затронутых прошлой операцией рядов
- mysql_connect() — Открывает соединение с сервером MySQL
- mysql_data_seek() — Перемещает внутренний указатель в результате запроса
- mysql_select_db() — Выбирает базу данных MySQL
- mysql_query() — Посылает запрос MySQL
User Contributed Notes 22 notes
Some user comments on this page, and some resources including the FAQ at :
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.
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.
*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.
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)
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()’];
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:
doesExist = (bool) ( $res = mysql_fetch_array ( $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.
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’];
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.
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():
doesExist = ( $res = mysql_fetch_array ( $result ))
[. ]
>
>
?>
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:
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.
Execute: SQL_CALC_FOUND_ROWS On Table 1
Execute: SQL_CALC_FOUND_ROWS On Table 2
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!
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.
query ( «LOCK TABLE t WRITE» );
$results = $mysqli -> query ( «SELECT id FROM t LIMIT 0,10» );
$totalNumResults = array_pop ( $mysqli -> query ( «SELECT FOUND_ROWS()» )-> fetch_row ());
$mysqli -> query ( «UNLOCK TABLES» );
?>
Now you may iterate through the results just like any other result set.
When mysql.trace_mode = On, SELECT FOUND_ROWS() allway returns 0. It looks like a bug.
If you have a table with 5 rows and issue:
SELECT SQL_CALC_FOUND_ROWS * FROM table LIMIT 1;
mysql_num_rows() returns 1 as expected.
If you issue «SELECT FOUND_ROWS()» just in sequence, in the same connection resource, it allways returns 0 rather than expected 5.
Just switch mysql.trace_mode to Off and things will work.
To use SQL COUNT function, without select the source.
Thanks, good luck.
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.
To simulate this, create the following script:
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:
If a race condition existed, the results of the first instance of the script would equal the results of the second instance of the script.
For example, the second instance of the script will execute the following SQL query:
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:
So it turns out NO race condition exists, and every solution presented to combat this «issue» are pretty much not needed.
mysql_num_rows
mysql_num_rows — Возвращает количество рядов результата запроса
Данное расширение устарело, начиная с версии PHP 5.5.0, и будет удалено в будущем. Используйте вместо него MySQLi или PDO_MySQL. Смотрите также инструкцию MySQL: выбор API и соответствующий FAQ для получения более подробной информации. Альтернативы для данной функции:
Описание
Возвращает количество рядов результата запроса. Эта команда работает только с запросами SELECT или SHOW, возвращающих актуальный результат запроса. Чтобы получить количество рядов, обработанных функциями INSERT, UPDATE, REPLACE и DELETE, используйте функцию mysql_affected_rows() .
Список параметров
Обрабатываемый результат запроса. Этот результат может быть получен с помощью функции mysql_query() .
Возвращаемые значения
Количество рядов в результате запроса в случае успеха или FALSE в случае возникновения ошибки.
Примеры
Пример #1 Пример использования mysql_num_rows()
Примечания
Замечание:
При использовании mysql_unbuffered_query() функция mysql_num_rows() не вернёт корректного значения до тех пор, пока все ряды не будут получены.
Замечание:
Для обратной совместимости может быть использован следующий устаревший псевдоним: mysql_numrows()
Смотрите также
- mysql_affected_rows() — Возвращает число затронутых прошлой операцией рядов
- mysql_connect() — Открывает соединение с сервером MySQL
- mysql_data_seek() — Перемещает внутренний указатель в результате запроса
- mysql_select_db() — Выбирает базу данных MySQL
- mysql_query() — Посылает запрос MySQL
Коментарии
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.
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.
*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.
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.
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.
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)
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()’];
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’];
To use SQL COUNT function, without select the source.
Thanks, good luck.
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’];
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:
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.
Execute: SQL_CALC_FOUND_ROWS On Table 1
Execute: SQL_CALC_FOUND_ROWS On Table 2
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!
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():
doesExist = ( $res = mysql_fetch_array ( $result ))
[. ]
>
>
?>
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:
doesExist = (bool) ( $res = mysql_fetch_array ( $result ))
[. ]
>
>
?>
The following code can wrap it all up in a single query so you don’t have to worry about multiple client requests:
$stmtMain = $mysqli->prepare(«SELECT SQL_CALC_FOUND_ROWS jobid,title FROM tbljobs
LIMIT ?, ? UNION SELECT FOUND_ROWS(),’!!IgnoreCount!!’;»)
Then iterate through the results with something like:
Improvement to chrisdberry82 at gmail dot com’s code:
You can see, that you can even ORDER the final result anyway you like 😉
Then you can fetch the result like this:
And you got rid of the lousy IFs 😉
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.
query ( «LOCK TABLE t WRITE» );
$results = $mysqli -> query ( «SELECT id FROM t LIMIT 0,10» );
$totalNumResults = array_pop ( $mysqli -> query ( «SELECT FOUND_ROWS()» )-> fetch_row ());
$mysqli -> query ( «UNLOCK TABLES» );
?>
Now you may iterate through the results just like any other result set.
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.
To simulate this, create the following script:
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:
If a race condition existed, the results of the first instance of the script would equal the results of the second instance of the script.
For example, the second instance of the script will execute the following SQL query:
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:
So it turns out NO race condition exists, and every solution presented to combat this «issue» are pretty much not needed.
When mysql.trace_mode = On, SELECT FOUND_ROWS() allway returns 0. It looks like a bug.
If you have a table with 5 rows and issue:
SELECT SQL_CALC_FOUND_ROWS * FROM table LIMIT 1;
mysql_num_rows() returns 1 as expected.
If you issue «SELECT FOUND_ROWS()» just in sequence, in the same connection resource, it allways returns 0 rather than expected 5.
Just switch mysql.trace_mode to Off and things will work.
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:
doesExist = (bool) ( $res = mysql_fetch_array ( $result ))
[. ]
>
>
?>
PHP Выбор данных из MySQL
Выбор данных из базы данных MySQL
Инструкция SELECT используется для выбора данных из одной или нескольких таблиц:
или мы можем использовать символ * для выбора всех столбцов из таблицы:
Чтобы узнать больше о SQL, пожалуйста, посетите наш учебник SQL.
Выбор данных с mysqli
В следующем примере выбираются столбцы ID, имя и фамилия из таблицы мигуестс и отображаются на странице:
Пример (mysqli объектно-ориентированный)
connect_error) <
die(«Connection failed: » . $conn->connect_error);
>
$sql = «SELECT id, firstname, lastname FROM MyGuests»;
$result = $conn->query($sql);
if ($result->num_rows > 0) <
// output data of each row
while($row = $result->fetch_assoc()) <
echo «id: » . $row[«id»]. » — Name: » . $row[«firstname»]. » » . $row[«lastname»]. «
«;
>
> else <
echo «0 results»;
>
$conn->close();
?>
Строки кода, поясняющие пример выше:
Сначала мы наводим SQL-запрос, который выбирает столбцы ID, имя и фамилия из таблицы мигуестс. Следующая строка кода запускает запрос и помещает результирующие данные в переменную с именем $result.
Затем проверяет, function num_rows() если есть более нуля строк, возвращаемых.
Если возвращено более нуля строк, функция fetch_assoc() помещает все результаты в ассоциативный массив, через который можно пройти цикл. while() цикл проходит через результирующий набор и выводит данные из столбцов ID, имя и фамилия.
В следующем примере показано, как в примере выше, в mysqli процедурный способ:
Пример (mysqli процедурный)
0) <
// output data of each row
while($row = mysqli_fetch_assoc($result)) <
echo «id: » . $row[«id»]. » — Name: » . $row[«firstname»]. » » . $row[«lastname»]. «
«;
>
> else <
echo «0 results»;
>
Вы также можете поместить результат в таблицу HTML:
Пример (mysqli объектно-ориентированный)
connect_error) <
die(«Connection failed: » . $conn->connect_error);
>
$sql = «SELECT id, firstname, lastname FROM MyGuests»;
$result = $conn->query($sql);
if ($result->num_rows > 0) <
echo «
Выбор данных с помощью PDO (+ подготовленные операторы)
В следующем примере используются подготовленные операторы.
Он выбирает столбцы ID, имя и фамилия из таблицы мигуестс и отображает их в таблице HTML:
Пример (PDO)
setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $conn->prepare(«SELECT id, firstname, lastname FROM MyGuests»);
$stmt->execute();
// set the resulting array to associative
$result = $stmt->setFetchMode(PDO::FETCH_ASSOC);
foreach(new TableRows(new RecursiveArrayIterator($stmt->fetchAll())) as $k=>$v) <
echo $v;
>
>
catch(PDOException $e) <
echo «Error: » . $e->getMessage();
>
$conn = null;
echo «
