Count query results php mysql

How to Count Result in a MySQL Query in PHP7: Step-by-Step Guide

Learn how to count the result of a MySQL query in PHP7 with step-by-step guidance using MySQL COUNT(), SELECT COUNT(*), mysqli_result::$num_rows, PDOStatement::rowCount(), count(), sqlsrv_num_rows(), mysqli_num_rows(), and fetchColumn(). Follow best practices for database design to optimize queries and avoid common issues.

• Using MySQL COUNT() to Count Rows in PHP
• Using SELECT COUNT(*) to Count Rows in PHP
• Using mysqli_result::$num_rows to Retrieve Rows in Result Set
• Advantages of Using PDOStatement::rowCount() to Count Rows in PHP
• Using count() to Count Elements in an Array
• Using sqlsrv_num_rows() to Retrieve Rows in Result Set
• Using mysqli_num_rows() to Return Rows in Result Set
• Using fetchColumn() to Count Rows in a Table
• Other helpful code examples for counting result in a MySQL query using PHP7
• Conclusion
• How to count data from database in PHP?
• How to display count (*) in PHP?
• How do you count data in a database?
• How to echo count (*) from MySQL in PHP?

PHP7 has removed the mysql_* functions, so users need to use mysqli or PDO functions to count the result of a MySQL query in PHP7. This article will provide step-by-step guidance on how to count the number of rows in a table using MySQL COUNT(), SELECT COUNT(*), mysqli_result::$num_rows, PDOStatement::rowCount(), count (), sqlsrv_num_rows(), mysqli_num_rows(), and fetchColumn() in PHP7. The article will also cover important and helpful points to optimize queries, avoid common issues, and follow best practices for database design .

Using MySQL COUNT() to Count Rows in PHP

The MySQL COUNT() function can be used to count the number of rows in a table. The COUNT() function can be used in a SELECT statement in PHP to retrieve the number of rows. Here’s an example code:

SELECT COUNT(*) FROM table_name; 

Using SELECT COUNT(*) to Count Rows in PHP

The SELECT COUNT( ) statement can be used to count the number of rows in a table. The SELECT COUNT( ) statement can be used in a PHP script with mysqli or PDO functions. Here’s an example code:

SELECT COUNT(*) as row_count FROM table_name; $row_count = $result->fetch_assoc()["row_count"]; 

Using mysqli_result::$num_rows to Retrieve Rows in Result Set

The mysqli_result::$num_rows function can be used to retrieve the number of rows in the result set. Here’s an example code:

$result = $mysqli->query("SELECT * FROM table_name"); $count = $result->num_rows; 

Advantages of Using PDOStatement::rowCount() to Count Rows in PHP

The PDOStatement::rowCount() function has many advantages over mysqli_result::$num_rows. The PDOStatement::rowCount() function can retrieve only part of the result set but still get the total row count. Here’s an example code:

$stmt = $pdo->prepare("SELECT * FROM table_name"); $stmt->execute(); $count = $stmt->rowCount(); 

Using count() to Count Elements in an Array

The count() function returns the number of elements in an array. Here’s an example code:

$array = array("one", "two", "three"); $count = count($array); 

Using sqlsrv_num_rows() to Retrieve Rows in Result Set

The sqlsrv_num_rows() function retrieves the number of rows in a result set. Here’s an example code:

$stmt = sqlsrv_query($conn, "SELECT * FROM table_name"); $count = sqlsrv_num_rows($stmt); 

Using mysqli_num_rows() to Return Rows in Result Set

The mysqli_num_rows() function is used to return the number of rows present in the result set. Here’s an example code:

$result = mysqli_query($conn, "SELECT * FROM table_name"); $count = mysqli_num_rows($result); 

Using fetchColumn() to Count Rows in a Table

The fetchColumn() method available in PDO can be used to count the number of rows in a table. Here’s an example code:

$stmt = $pdo->query("SELECT COUNT(*) FROM table_name"); $count = $stmt->fetchColumn(); 

Other helpful code examples for counting result in a MySQL query using PHP7

In Php as proof, php 7 count result in database code sample

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); $link = mysqli_connect("host", "username", "password","db_name"); mysqli_set_charset($link, "utf8mb4");$result = mysqli_query($link, "SELECT count(*) FROM blackandwhite"); $num_rows = mysqli_fetch_row($result)[0];echo "$num_rows Rows\n"; 

In Php case in point, php count result query code sample

Conclusion

Counting the result of a MySQL query in PHP7 can be done using mysqli or PDO functions with MySQL COUNT(), SELECT COUNT(*), mysqli_result::$num_rows, PDOStatement::rowCount(), count(), sqlsrv_num_rows(), mysqli_num_rows(), and fetchColumn(). Optimizing queries with indexes and minimizing joins, using appropriate data types, and avoiding redundancy are best practices for database design. Following these steps and helpful points will ensure successful query result counting in PHP7.

Источник

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)

Источник

Counting MySQL Query Results in PHP

To choose between MySQL and MySQLi when using PHP, there are two solutions. The first solution is to use PDO for MySQL query. The second solution involves retrieving the returned value by using «$result[count()]» since the column name is selected as count().

Get the count of the mysql query results in php

$query = 'SELECT COUNT(1) FROM test_table WHERE test_no=12345'; $queryResult = $db->query($query); $count = $queryResult->fetchColumn(); 

If you’re utilizing PDO, it’s recommended to avoid specifying a column name in COUNT() . Instead, opt for either * or 1 and retrieve the first column’s data with fetchColumn() .

When dealing with large amounts of data, it’s advisable to let MySQL handle row counting. This avoids the unnecessary transfer of data from MySQL to PHP just for counting and discarding, which would be the case if PHP were in charge. Hence, the query labeled as SELECT COUNT(test) is the more preferable option among the two presented.

When working with PDO, it’s recommended to utilize the rowCount() function for determining the number of rows returned from a query. To illustrate, consider the following example.

try < $s = $conn->execute("YOUR QUERY HERE"); $count = $s->rowCount(); > catch(PDOException $e) < echo $e->getMessage(); > 

PHP MySQL count records SELECT COUNT with a certain value, dept_id = dept.id WHERE ticket.status !=1 GROUP BY dept.id ORDER BY ticket_count DESC»; if ($result = $mysqli->query($query)) < while ($row = $result->

How to retrieve and display a COUNT query result in PHP?

$db = new PDO('mysql:host=#YOUR HOST#;dbname=#YOUR DB#;charset=utf8', '#YOUR LOGIN#', '#YOUR PASSWORD#'); $query = $db->query('SELECT COUNT(*) AS count FROM articles'); $countq = $query->fetch(); $query->closeCursor(); echo $countq['count']; 

I hope this will help you

In the query, it will be necessary to establish a limit.

$count = "SELECT COUNT(*) FROM articles LIMIT 5,10"; 

You can begin with 5 and specify the maximum number of results you would like to obtain as 10.

The mention of $db leaves the question of its nature unanswered. Is it a database object class? This code will function seamlessly if you are using a database class. Additionally, the class will offer you functions that enable data querying without resorting to mysql_fetch_array (or even mysqli_fetch_array).

MySQL rows count with PHP and show result, $con) < die('Could not connect: ' . mysql_error()); >mysql_select_db(«predator_db», $con); $result = mysql_query

Is it possible to get a sql count result assign to a php variable? [duplicate]

You can use mysql_fetch_row :

//code for connecting to database $query = "SELECT COUNT(*) FROM `user_table` WHERE `username` = 'John12' OR `username` = 'Paula25'"; $result = mysql_query($query); if(!$result) die($db->error); $row = mysql_fetch_row($result); $count = $row[0]; if($count < 2) < // do this if true >else < // do this >

According to @mith’s comment, it is advisable to use mysqli instead of mysql for new projects. For more information, you can refer to this article: PHP’s MySQL vs MySQLi.

To retrieve the returned value, use $result and pass in the count() function as the column name since it was selected.

or you can use alias like

The code snippet retrieves the count of records from a database table. The query uses the ‘SELECT COUNT(*)’ statement and aliases the result as ‘count’.

After executing the MySQL query, the result is returned and stored in the variable «$result». Its «count» value is then divided by 2.

if you don’t want to use alias

The value you provided will be divided by two and assigned to the variable $result[‘COUNT(*)’].

In your situation, something similar to this must be done.

Count number of rows in Mysql using PHP7, $link = mysqli_connect(«xxx», «xxx», «xxx»); mysqli_select_db(«xxx», $link); $result = mysqli_query(«SELECT * FROM blackandwhite», $link); $

MySQL rows count with PHP and show result

You’re attempting to retrieve data from database instead of your designated predator_db database.

I’ll demonstrate with the fundamentals, but I recommend exploring MySQLi prepared statements and/or PDO for further understanding.

$link = mysqli_connect("localhost", "predator_db", "PASS", "predator_db"); $result = mysqli_query($link, "select COUNT(id) AS count FROM `database`"); // make sure it worked if(!$result) < die('Error: ' . mysqli_error($link)); >else < $num_rows = mysqli_fetch_assoc($result); // echo it echo "Rows: " . $num_rows['count']; >

Naming a table with a reserved keyword like database is not recommended. But if you choose to do so, you should always enclose the name in backticks «. This way, your query will be valid.

$result = mysql_query("select count(1) FROM `database`"); 

Consider exploring MYSQLi since the previous MySQL driver is no longer supported.

How to count rows in MySQL table in PHP ?, Approach: By using PHP and MySQL, one can perform database operations. We can get the total number of rows in a table by using the MySQL

Источник