Mysql num rows php pdo

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)

Источник

Count Rows in MySQL PHP

1. Use fetchColumn() Method of PDO to Count the Total Number of Rows in a MySQL Table
2. Use a Procedural Method to Count the Number of Rows in the MySQL Table Using the mysqli_num_rows() Function
3. Use an Object-Oriented Way to Count the Number of Rows in a Table Using the num_rows Property

We will introduce a method to count all the rows from a table in MySQL in PHP using the fetchColumn() and COUNT() methods. The fetchColumn() function is given by PDO(PHP Data Objects) and the COUNT() method is a SQL function. PDO is an object-oriented way of connecting the database and the backend. This method is flexible because PDO is compatible with 12 different database systems.

We will demonstrate another method to count all rows from a table using the mysqli_num_rows() function. This method uses an object-oriented way to establish the database connection with the server using the mysqli() function. However, the rest of the process is done procedurally.

We will demonstrate a way to count all the rows from a table using PHP’s num_rows property. This method follows the complete object-oriented practice to connect the database and count the total number of rows. The use of the prepared statement in this method makes it safe from the vulnerability of SQL injection.

Use fetchColumn() Method of PDO to Count the Total Number of Rows in a MySQL Table

PDO is one of the object-oriented ways to connect the database with the PHP server. We can use the fetchColumn() method available in PDO to count the table’s rows. Firstly, we create a database and populate the table with data. Then, we set up the database connection using the correct host, database username, database password, and database name. We use an instance of a PDO object to store the connection. Once we ensure the database connection is working, we query the SQL statement using the COUNT() function and execute it. The COUNT() function takes the * as the parameter that counts all rows in the specified table. Then we use the fetchColumn() method to display the number of rows in the table.

The instructions below assume that a database connection with the server is established. There are two rows in the table in the database.

MariaDB [oop]> select * from users;  +----+-----------+------------+------------+  | id | firstname | lastname | dob |  +----+-----------+------------+------------+  | 1 | Dan | James | 1998-08-23 |  | 2 | Dean | Henderson | 2000-03-30 |  +----+-----------+----------+--------------+  2 rows in set (0.003 sec) 

For example, in Test.php , write a class named Test that inherits the DB class. Write a public function getRowsNumber() inside the class. Assign a variable $sql and write a query using the SELECT statement to select everything from the users table. Use COUNT(*) in the SELECT statement to count the number of rows. Use the connect function to get the connection from DB.php and query the above-written SQL command using the query() function. Assign these tasks to a $stmt variable. Use $stmt variable to call the fetchColumn() method and display the result.

In the example below, the superclass DB contains a connect() method which contians the database connection. The getRowsNumber() function is invoked from anothe php file as:

$testObj = new Test(); $testObj->getRowsNumber() 

# php 7.*  php class Test extends DB   public function getRowsNumber()   $sql = "SELECT COUNT(*) FROM users";  $stmt = $this->connect()->query($sql);  $count = $stmt->fetchColumn();  print $count;  > > 

The total number of rows is: 2 

Use a Procedural Method to Count the Number of Rows in the MySQL Table Using the mysqli_num_rows() Function

We can use the mysqli_num_rows() function in PHP to count the rows in a MySQL table. We can create an object of the mysqli() function to connect the database with the PHP server. The function takes hostname, username, password, and database name as parameters. We write the SQL statement to select every row from the table. We use the mysqli_query() function to use the database connection and execute the query. Then we use the mysql_num_rows() function to count the number of rows and display it.

The example below uses the same database, the same table, and the same data in the table as in the first method.

For example, assign the hostname, username, password, and database name to the variables $host , $username , $password , and $database , respectively. Create an object of the mysqli() function using the new keyword and pass the variables as the function’s parameters. Assign the value of the object in the $conn variable. Write a SQL query to select everything from the users table in the $sql variable. Use a variable $result to store the mysqli_query() function which takes the $conn and the $sql variables. Check the boolean value of the $result variable using the if condition. Inside the if condition use mysqli_num_rows() with $request variable as the parameter and assign it to $rowcount variable. Print the $rowcount variable.

#php 7.x  php $conn = new mysqli($host, $username, $password, $database); $sql = "SELECT * FROM users"; if ($result=mysqli_query($conn,$sql))   $rowcount=mysqli_num_rows($result);  echo "The total number of rows are: ".$rowcount; > ?> 

The total number of rows are: 2 

Use an Object-Oriented Way to Count the Number of Rows in a Table Using the num_rows Property

We can use the num_rows property in PHP to count the number of rows in a MySQL table. This approach uses the object-oriented method. The method is quite similar to the second method in creating the database connection and writing the SQL query. The use of prepared statements differs this method from the second one. We use prepare() function to create a prepared statement, execute() function to execute the prepared statement and store_result() function to store the result.

For example, assign the hostname, username, password, and database name to the variables $host , $username , $password , and $database , respectively. Create an object of the mysqli() function using the new keyword and pass the variables as the function’s parameters. Assign the value of the object in the $conn variable. Write a SQL query to select everything from the users table in the $sql variable.

Use a variable $stmt to store the prepared statement. Use $conn vairable to call the prepare() function which takes the sql variable as the parameter. Check the boolean value of the $stmt variable using if condition. Inside the if condtion, use the $stmt variable to call the execute() function and then the store_result() function . And then, call the num_rows property and print it.

#php 7.x  php $conn = new mysqli($host, $username, $password, $database); $sql = "SELECT * FROM users"; if ($stmt = $conn->prepare($sql))   $stmt->execute();  $stmt->store_result();  printf("Number of rows: %d.\n", $stmt->num_rows); > ?> 

Subodh is a proactive software engineer, specialized in fintech industry and a writer who loves to express his software development learnings and set of skills through blogs and articles.

Related Article — PHP MySQL

Источник