How to Efficiently Count Rows in MySQL Tables Using PHP?

How to Retrieve Row Counts from MySQL Tables in PHP

Your PHP code, which aims to count rows in a MySQL table and store the result in $count, encountered difficulties. In this article, we will explore how to effectively achieve this using PHP's procedural style.

Procedural Row Counting

To obtain the row count, consider the following modifications to your code:

$sql = "SELECT COUNT(*) FROM news";
$result = mysqli_query($con, $sql);
$count = mysqli_fetch_assoc($result)['COUNT(*)'];
echo $count;

In this version, we access the result of COUNT(*) directly by selecting the appropriate column from the associative array returned by mysqli_fetch_assoc().

Alternative Methods

Apart from the above method, here are some additional options:

• Using Column Aliasing:

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

• Using Numerical Arrays:

$sql = "SELECT COUNT(*) FROM news";
$result = mysqli_query($con, $sql);
$count = mysqli_fetch_row($result)[0];
echo $count;

• PHP 8.1 and Later:

$sql = "SELECT COUNT(*) FROM news";
$result = mysqli_query($con, $sql);
$count = mysqli_fetch_column($result);
echo $count;

Avoid Using mysqli_num_rows

Contrary to popular advice, mysqli_num_rows should not be used for row counting. It retrieves all matching records, which can be inefficient and resource-intensive.

Prepared Statements for Parameterized Queries

If your query involves dynamic variables, consider using prepared statements:

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

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