How to get count of rows in MySQL table using PHP?

Question

I simply want to use PHP to get a count of the total number of rows in a MySQL table, and store the number in a variable called $count.

I prefer procedural code since my mind doesn't work in object-oriented fashion.

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

The above is the latest I tried. Instead of giving me a number, it gives me the word "Array".

Answer 1

You have a couple of options how to get the value of COUNT(*) from the SQL. The easiest three are probably this:

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

or using column alias:

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

or using numerical array:

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

Do not use mysqli_num_rows to count the records in the database as suggested in some places on the web. This function has very little use, and counting records is definitely not one of them. Using mysqli_num_rows you would be asking MySQL to retrieve all matching records from database, which could be very resource consuming. It is much better to delegate the job of counting records to MySQL and then just get the returned value in PHP as shown in my answer.

I would also recommend to learn OOP, which makes your code cleaner and easier to read. The same with OOP could be done as follows:

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

If your query uses variables, then you could do a similar thing, but 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;

Answer 2

The following PHP can be used to execute the query to calculate the amount of results:

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

Answer 3

It's better to use COUNT(*) instead of selecting all rows and using mysqli_num_rows().

Your basic idea was correct, you just needed to fetch the result properly.

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