How to Accurately Count MySQL Rows Using PHP?

How to Retrieve Exact Row Count from a MySQL Table using PHP

Getting a count of rows in a MySQL table is a common task in database handling. PHP provides several options for this, especially for those who prefer procedural code.

The Error:

One common error is obtaining the word "Array" instead of a number. This occurs when using mysqli_fetch_assoc() which returns an associative array.

Solution 1: Using Array Indexes

To resolve this, access the array index corresponding to the column name. For example:

$count = mysqli_fetch_assoc($result)['COUNT(*)'];

Solution 2: Using Column Alias

Alternatively, you can specify an alias for the count column in your SQL query:

$sql = "SELECT COUNT(*) AS cnt FROM news";
$count = mysqli_fetch_assoc($result)['cnt'];

Solution 3: Using Numerical Arrays

Another option is to use mysqli_fetch_row() to retrieve a numerical array:

$count = mysqli_fetch_row($result)[0];

PHP 8.1 Optimization

If using PHP 8.1 or later, you can simplify the operation:

$count = mysqli_fetch_column($result);

Avoid Using mysqli_num_rows

While it may seem tempting to use mysqli_num_rows, this function has significant limitations and should be avoided for counting rows.

OOP Approach

For those who prefer object-oriented programming, the same code could be written as:

$count = $con->query($sql)->fetch_row()[0];

Prepared Statements

When using queries with variables, you should employ prepared statements for improved security and efficiency:

$stmt = $con->prepare("SELECT COUNT(*) FROM news WHERE category=?");
$stmt->bind_param('s', $category);
$stmt->execute();
$count = $stmt->get_result()->fetch_row()[0];

The above is the detailed content of How to Accurately Count MySQL Rows Using PHP?. For more information, please follow other related articles on the PHP Chinese website!