How to Retrieve Row Count in MySQL Table Using PHP Procedurally

You seek to determine the total number of rows in a MySQL table and store it in a variable, $count. Your initial attempt yielded the word "Array" instead.

The solution involves utilizing mysqli_fetch_assoc($result) to retrieve the count value. Here are three ways to do so:

1. Using Column Alias:

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

2. Using Numerical Array:

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

3. PHP 8.1 and Above:

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

Additionally, it's recommended to learn OOP (Object-Oriented Programming) for cleaner and more readable code. The OOP version of your code:

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

For queries with variables, prepared statements can be employed:

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