How To Count The Number Of rows in database php

Question

Am trying to count how many rows have a particular word in database using php. and when i use this code below to do it, it only returns (1) even when there are more than 1

 $sql="SELECT COUNT(*) FROM user_contact_us where status='unread'";
   
 $result=mysqli_query($conn,$sql);
        
 $numR=mysqli_num_rows($result);
       
 echo $numR;

I also did it like this

$sqlr = $conn->query("SELECT COUNT(*) FROM user_contact_us where status='read'");
$numR = $sqlr->num_rows;
echo $numR;

and it keeps returning 1 as the value when its supposed to be 2 or 3. Please how do i fix this

A `SELECT COUNT(*).....` will always return ONE ROW. One row with the count in it. So fetch the result and find out the actual count — RiggsFolly, Aug 22 '20 at 21:37
@RiggsFolly when its 'read' its supposed to return 3 but if its unread its supposed to return 2 since there are five data(rows) in the database — GrandFenrir, Aug 22 '20 at 21:38
@RiggsFolly Thanks a lot i used this method and it worked > $sql = $conn->prepare("SELECT * FROM user_contact_us where status='unread'"); $sql->execute(); $res = $sql->get_result(); $numR = $res->num_rows; — GrandFenrir, Aug 22 '20 at 21:41

score 0 · Answer 1 · answered Aug 22 '20 at 21:36

COUNT(*) just returns a single row of results. The count of the number of rows matching the condition is in that row. You need to fetch the result, and the count will be in that.

$sql="SELECT COUNT(*) as count FROM user_contact_us where status='unread'";
$result=mysqli_query($conn,$sql);
$row = mysqli_fetch_assoc($result);
$echo $row['count'];

How To Count The Number Of rows in database php

1 Answers1