How to Efficiently Count MySQL Rows Using PHP?

Retrieve Row Count from MySQL Table in PHP

Question:

How can I count the number of rows in a MySQL table using PHP?

Answer:

To retrieve the count of rows in a MySQL table using PHP, you can utilize the following methods:

Procedural Code:

Follow these steps:

1. Prepare a SQL query using the COUNT(*) function:

   $sql = "SELECT COUNT(*) FROM news";

2. Execute the query using mysqli_query():

   $result = mysqli_query($con, $sql);

3. Fetch the result as an associative array using mysqli_fetch_assoc():

   $count = mysqli_fetch_assoc($result);

4. Access the value of the COUNT(*) column to obtain the row count:

   echo $count['COUNT(*)'];

Alternative Procedural Code:

Alternatively, you can use the following code to achieve the same result:

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

Using Numerical Array:

Another approach is to fetch the result as a numerical array:

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

Using PHP 8.1 (Simplified):

If you're using PHP 8.1, you can simplify the process using mysqli_fetch_column():

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

Object-Oriented Style (OOP):

In OOP, you can achieve this using the following code:

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

Using Prepared Statements:

If your query employs 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;

Caution:

Avoid using mysqli_num_rows for row counting as it retrieves all matching records, which can be inefficient.

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