How to Efficiently Count Rows in a MySQL Table Using PHP?

How to Count Rows in a MySQL Table Using PHP

To obtain the total count of rows in a MySQL table, PHP provides several methods. Let's delve into the most common approaches.

Using Associative Arrays

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

Using Column Alias

$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 Simplification

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

It's crucial to avoid using mysqli_num_rows, as it fetches all matching records, which can be inefficient. Instead, delegate the counting task to MySQL and retrieve the count as shown above.

Object-Oriented Approach

$sql = "SELECT COUNT(*) FROM news";
$count = $con->query($sql)->fetch_row()[0];
echo $count;

With prepared statements, you can count rows with dynamic queries.

$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;

By following these methods, you can efficiently obtain row counts in MySQL tables using PHP.

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