Select num row php

How to get number of rows returned by an SQL select query?

I would like to get the number of lines returned by a select query in PHP. I have the following code:

$connection = new mysqli($server_name, $server_login, $server_password, $dbName); if (!$connection) < echo "error"; die("Connection failed. ".mysqli_connect_error()) >//. $command = "SELECT player_id FROM Player WHERE player_login = '" . $login."' AND player_password= '".$password."' "; $result = mysqli_query($connection, $command); echo num_rows($result); 

I also tried with mysqli_stmt_num_rows() and mysqli_num_rows() but my result is always null (no result actually). Do you know why?

Incidentally: if this ever returns >1 row, I think you might have some security issues on this site. And login/password forms are big targets for SQL Injection. Look at prepared queries for safety!

3 Answers 3

There are a few ways to get the number of rows returned, the most common ones are to run COUNT(*) in MySQL, but there’s also mysqli_num_rows($result) (not num_rows() like you used, unless you created that function yourself). mysqli_stmt_num_rows() will only work when you’re using prepare() instead of query() .

In ordre to use COUNT(*) you have to run and fetch the query first, while mysqli_num_rows() is a constant returned by the MySQLiResult object, which you can use if the query didn’t fail.

I modified the piece of code you’ve got to check if the query actually succeeded, mysqli_num_rows() won’t work if the query failed.

$command = "SELECT player_id FROM Player WHERE player_login = '" . $login."' AND player_password= '".$password."' "; if ($result = mysqli_query($connection, $command)) < echo mysqli_num_rows($result); >else < /* Query failed */ echo "There was an error with the query: $command"; echo "
".mysqli_error($connect); > 

Or you can use COUNT(*) , but then you’ll have to fetch the results first.

$command = "SELECT player_id, COUNT(*) as cnt FROM Player WHERE player_login = '" . $login."' AND player_password= '".$password."' "; if ($result = mysqli_query($connection, $command)) < $row = mysqli_fetch_assoc($result); echo $row['cnt']; >else < /* Query failed */ echo "There was an error with the query: $command"; echo "
".mysqli_error($connect); > 

You should also note that this query is vulnerable to SQL injection, you should learn how to use prepared statements with placeholders to protect yourself against that. The manual on prepare() is a good place to start with that.

You also seem to be storing passwords either in plain-text, or with poor methods (such as md5 or sha1 ). PHP offer’s a built-in function, password_hash() / password_verify() which you should use. If you’re below PHP version 5.5, these functions aren’t native, but there’s a compability pack which can be used instead.

As a final note, mixing object oriented and procedural code will technically work (as the procedural ones in reality call the object oriented ones), but it’s considered bad practice. If you connect with an object, continue to use object-oriented code.

References

Источник

mysqli_num_rows

The behaviour of mysqli_num_rows() depends on whether buffered or unbuffered result sets are being used. This function returns 0 for unbuffered result sets unless all rows have been fetched from the server.

Parameters

Return Values

An int representing the number of fetched rows. Returns 0 in unbuffered mode unless all rows have been fetched from the server.

Note:

If the number of rows is greater than PHP_INT_MAX , the number will be returned as a string .

Examples

Example #1 Object-oriented style

mysqli_report ( MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT );
$mysqli = new mysqli ( «localhost» , «my_user» , «my_password» , «world» );

$result = $mysqli -> query ( «SELECT Code, Name FROM Country ORDER BY Name» );

/* Get the number of rows in the result set */
$row_cnt = $result -> num_rows ;

printf ( «Result set has %d rows.\n» , $row_cnt );

Example #2 Procedural style

mysqli_report ( MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT );
$link = mysqli_connect ( «localhost» , «my_user» , «my_password» , «world» );

$result = mysqli_query ( $link , «SELECT Code, Name FROM Country ORDER BY Name» );

/* Get the number of rows in the result set */
$row_cnt = mysqli_num_rows ( $result );

printf ( «Result set has %d rows.\n» , $row_cnt );

The above examples will output:

Notes

Note:

In contrast to the mysqli_stmt_num_rows() function, this function doesn’t have object-oriented method variant. In the object-oriented style, use the getter property.

See Also

• mysqli_affected_rows() — Gets the number of affected rows in a previous MySQL operation
• mysqli_store_result() — Transfers a result set from the last query
• mysqli_use_result() — Initiate a result set retrieval
• mysqli_query() — Performs a query on the database
• mysqli_stmt_num_rows() — Returns the number of rows fetched from the server

User Contributed Notes 3 notes

If you have problems making work this num_rows, you have to declare ->store_result() first.

$mysqli = new mysqli ( «localhost» , «root» , «» , «tables» );

$query = $mysqli -> prepare ( «SELECT * FROM table1» );
$query -> execute ();
$query -> store_result ();

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:

public function errorList ( int $limit = 25 , int $offset = 0 ) $errorList = array();
$result = $this -> 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 ;
>
?>

in php 5.3.8 had unexpected troubles when checking for mysqli_result::$num_rows
If the result of the query is empty then var_dump of the result will be like this:
class mysqli_result#5 (5) public $current_field => NULL
public $field_count => NULL
public $lengths => NULL
public $num_rows => NULL
public $type => NULL
>
but var_dump($result->num_rows) will give integer-typed zero instead of NULL:
int(0)

Источник

mysqli_num_rows

Поведение функции mysqli_num_rows() зависит от того, используется ли буферизованная или не буферизованная результирующая выборка. Для не буферизованной выборки mysqli_num_rows() не вернет правильное число рядов пока все ряды не будут получены и обработаны.

Список параметров

Только для процедурного стиля: Идентификатор результата запроса, полученный с помощью mysqli_query() , mysqli_store_result() или mysqli_use_result() .

Возвращаемые значения

Возвращает число рядов в результирующей выборке.

Замечание:

Если число рядов больше чем PHP_INT_MAX , то число будет возвращено в виде строки.

Примеры

Пример #1 Объектно-ориентированный стиль

$mysqli = new mysqli ( «localhost» , «my_user» , «my_password» , «world» );

/* проверка соединения */
if ( mysqli_connect_errno ()) printf ( «Соединение не удалось: %s\n» , mysqli_connect_error ());
exit();
>

if ( $result = $mysqli -> query ( «SELECT Code, Name FROM Country ORDER BY Name» ))

/* определение числа рядов в выборке */
$row_cnt = $result -> num_rows ;

printf ( «В выборке %d рядов.\n» , $row_cnt );

/* закрытие выборки */
$result -> close ();
>

/* закрытие соединения */
$mysqli -> close ();
?>

Пример #2 Процедурный стиль

$link = mysqli_connect ( «localhost» , «my_user» , «my_password» , «world» );

/* проверка соединения */
if ( mysqli_connect_errno ()) printf ( «Соединение не удалось: %s\n» , mysqli_connect_error ());
exit();
>

if ( $result = mysqli_query ( $link , «SELECT Code, Name FROM Country ORDER BY Name» ))

/* определение числа рядов в выборке */
$row_cnt = mysqli_num_rows ( $result );

printf ( «В выборке %d рядов.\n» , $row_cnt );

/* закрытие выборки */
mysqli_free_result ( $result );
>

/* закрытие соединения */
mysqli_close ( $link );
?>

Результат выполнения данных примеров:

Смотрите также

• mysqli_affected_rows() — Получает число строк, затронутых предыдущей операцией MySQL
• mysqli_store_result() — Передает результирующий набор последнего запроса
• mysqli_use_result() — Готовит результирующий набор на сервере к использованию
• mysqli_query() — Выполняет запрос к базе данных

Источник

MySQL — count total number of rows in php

What is the best MySQL command to count the total number of rows in a table without any conditions applied to it? I’m doing this through php, so maybe there is a php function which does this for me? I don’t know. Here is an example of my php:

 mysql_select_db("db", $con); $result = mysql_query("some command"); $row = mysql_fetch_array($result); mysql_close($con); ?> 

11 Answers 11

 mysql_select_db("db", $con); $result = mysql_query("select count(1) FROM table"); $row = mysql_fetch_array($result); $total = $row[0]; echo "Total rows: " . $total; mysql_close($con); ?> 

**2022: STOP USING MYSQL METHODS, THESE ARE NOT SECURE AND REMOVED FROM PHP! Use MySQLi or PDO instead.

Either use COUNT in your MySQL query or do a SELECT * FROM table and do:

$result = mysql_query("SELECT * FROM table"); $rows = mysql_num_rows($result); echo "There are " . $rows . " rows in my table."; 

You’re first part is correct. I would emit the last part pulling back the entire dataset. In this questions scope, that’s a waste of resources for a simple count.

@George I would not suggest using it either. But as he asked if there was any PHP function that could do it, I just shared the option. Just answering the question.

And that’s fine, however, it’s always good to point out potential issues like this when you answer a question. Not only does it help the user understand the differences, it increases the legitimacy of your answer, and the attention it gets. Which means, more points for you! 🙂

mysqli_num_rows is used in php 5 and above.

 $sql="SELECT Lastname,Age FROM Persons ORDER BY Lastname"; if ($result=mysqli_query($con,$sql)) < // Return the number of rows in result set $rowcount=mysqli_num_rows($result); printf("Result set has %d rows.\n",$rowcount); // Free result set mysqli_free_result($result); >mysqli_close($con); ?> 

$result = mysql_query('SELECT COUNT(1) FROM table'); $num_rows = mysql_result($result, 0, 0); 

Use mysql_result (the best for this instance, IMHO) or one of the similar functions for fetching data from result sets.

you can do it only in one line as below:

$cnt = mysqli_num_rows(mysql_query("SELECT COUNT(1) FROM TABLE")); echo $cnt; 

Why are you writing this answer, when there’s accepted answer? And this question has been asked in 2011.

This answer is wrong. It return always 1 because you count the amount of records returned from the query. Since that you are using an aggregate function like COUNT you will get always an row in the result, and later you count this row thereby you’ll get always 1. Furthermore you are using a deprecated library

use num_rows to get correct count for queries with conditions

$result = $connect->query("select * from table where "); $count=$result->num_rows; echo "$count"; 

prepare("SELECT count(*) FROM staff_login"); $staff->execute(); $staffrow = $staff->fetch(PDO::FETCH_NUM); $staffcount = $staffrow[0]; echo $staffcount; ?> 

Good addition. A small enhancement: If you use $staffcount = $staff->fetchColumn(); you can get rid of the $staffrow temporary variable.

 $sql="SELECT Lastname,Age FROM Persons ORDER BY Lastname"; if ($result=mysqli_query($con,$sql)) < // Return the number of rows in result set $rowcount=mysqli_num_rows($result); echo "number of rows: ",$rowcount; // Free result set mysqli_free_result($result); >mysqli_close($con); ?> 

it is best way (I think) to get the number of special row in mysql with php.

 $sql="select count('user_id') from login_user"; $result=mysqli_query($conn,$sql); $row=mysqli_fetch_array($result); echo "$row[0]"; mysqli_close($conn); ?> 

$sql = "select count(column_name) as count from table"; 

Well, I used the following approach to do the same: I have to get a count of many tables for listing the number of services, projects, etc on the dashboard. I hope it helps.

PHP Code

// create a function 'cnt' which accepts '$tableName' as the parameter. function cnt($tableName)< global $conection; $itemCount = mysqli_num_rows(mysqli_query($conection, "SELECT * FROM `$tableName`")); echo'
'.$itemCount.'
'; > 

Then when I need to get the count of items in the table, I call the function like following

In my HTML front end, so it renders the count number

• It’s to be noted that I create the cnt() function as a global function in a separate file which I include in my head, so I can call it from anywhere in my code.

Источник