Php mysqli result row count

Содержание

how to get the total row count with mysqli
3 Answers 3
mysqli_num_rows
Parameters
Return Values
Examples
Notes
See Also
User Contributed Notes 3 notes
MySQL — count total number of rows in php
11 Answers 11
PHP Code
Php mysqli result row count

how to get the total row count with mysqli

i am trying to understand mysqli extension and did google but got very few info on this except php.net which was helpful. now after all this i am trying to achieve what i could with mysql extension which is as follows:

// MYSQL STYLE OF fetching array, query limit and perform total row count all at once $sql = "SELECT SQL_CALC_FOUND_ROWS *, post.id as pid, bla bla FROM account ORDER BY pid ASC". $eb["array"]['querylimit']; $result = mysql_query($sql, $eb["con"]); $TotalRcount = mysql_fetch_row(mysql_query("SELECT FOUND_ROWS()")); // Performing record count [current] // $RecordCount = mysql_num_rows($result); while($row = mysql_fetch_array($result)) < // read columns >

with mysqli how can i achieve this? am sure i am missing many things. please help me with example on how to achieve my goal.

3 Answers 3

//Establish connection using mysqli api $conn = mysqli_connect('hostname', 'username', 'password', 'database_name'); $sql = "SELECT SQL_CALC_FOUND_ROWS *, post.id as pid, bla bla FROM account ORDER BY pid ASC". $eb["array"]['querylimit']; $sql2 = "SELECT FOUND_ROWS()"; $result1 = $conn->query($sql); $result2 = $conn->query($sql2); $TotalRcount = $result2->fetch_row(); // Performing record count [current] // $RecordCount = $result->num_rows(); while($row = $result->fetch_array(MYSQLI_BOTH)) < // read columns >

In a while loop i have used MYSQLI_BOTH constant but you may change it to MYSQLI_NUM or MYSQLI_ASSOC whichever you need.

Thanks for this answer. I tried this and $TotalRcount = $result2->fetch_row(); returns an array with the actual total number of rows in index 0

Источник

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.

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)

Источник

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.

Источник