HOW to use MySQL COUNT() function using PHP

Question

I have been trying this code for some time now, and I cant figure it out.

   <?php
$link = mysqli_connect("localhost", "h4g54453g5234", "23j4hbjh243v535", "3j45hv6j3h45v645");
if($link === false){
    die("ERROR: Could not connect. " . mysqli_connect_error());
}
$sql = 'SELECT COUNT(*) FROM users WHERE phone=9876543210 AND status=1';
if($result = mysqli_query($link, $sql)){
    if(mysqli_num_rows($result) > 0){
        while($row = mysqli_fetch_array($result)){
echo "" . $row['COUNT(*)'] . "";                  
        }
        mysqli_free_result($result);
    } else{
        echo "No records matching your query were found.";
    }
} else{
    echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
}
mysqli_close($link);

?>

table sample

The result of above code will be "4", because the column "status" only 4 data with the number "1".

now I can use echo $row['COUNT(*)']; to display the number "4".

But, I want to show results like following echo "$status1; (which will show "4") echo "$status2; (which will show "4") echo "$status3; (which will show "2")

How can I do that? Using the above code, I can only show the result of status column with value 1.

`SELECT status, COUNT(*) AS count FROM people WHERE phone='9876543210' GROUP BY status` — Nick, May 12 '20 at 04:28
@Nick ,this shows the results of all status, can you tell me how to echo only the count of status "3" for example ? — Mithun, May 12 '20 at 04:51
`SELECT COUNT(*) FROM people WHERE phone='9876543210' AND status=3` — Nick, May 12 '20 at 05:28
@Nick, thanks for the reply, that really works. But I also want to show the results of status 1 and status 2 at the same page, just like status 3. how can I shows all at the same time, but like echo $status1; echo $status2; echo $status3; etc. — Mithun, May 12 '20 at 05:49

score 0 · Answer 1 · 2020-05-12T05:28:53.510

0

SELECT status, phone, COUNT(*) AS count FROM people GROUP BY phone, status

SELECT status, phone, COUNT(*) AS count FROM people WHERE status=3 GROUP BY phone, status

SELECT status, phone, COUNT(*) AS count FROM people WHERE phone='9876543210' GROUP BY phone, status

edited May 12 '20 at 05:28

answered May 12 '20 at 04:30

this shows the results of all status, can you tell me how to echo only the count of status "3" for example ? – Mithun May 12 '20 at 04:53
Use where clause. – May 12 '20 at 04:57
but if I user WHERE status=3, it shows the results from multiple phone n umbers. I only want to result the statuses of one phone number, which is "9876543210". and not only one status, I want to display status of all 0,1,2 and 3 – Mithun May 12 '20 at 05:25
So add `WHERE phone='9876543210'` condition – May 12 '20 at 05:29
@ Pajfs An, yes, that is what I am doing now. but the result is showing as an array. the result is "141051". 14, 10,5,1 are different values. how to only echo 14? like echo $status1; echo $status2; echo $status3; etc.. – Mithun May 12 '20 at 05:42
`$row['count']` – May 12 '20 at 05:47
if I use it with SELECT COUNT(*) FROM people WHERE phone='9876543210' AND status=3, this will only result 1 value. I will have to call teh sql again and again multiple times to show data of all Status 0,1,2 and 3 – Mithun May 12 '20 at 06:01
So use first query `SELECT status, phone, COUNT(*) AS count FROM people GROUP BY phone, status`. I don't understand what's problem with first query. – May 12 '20 at 06:03
using SELECT status, phone, COUNT(*) AS count FROM people GROUP BY phone, status gives me a black page – Mithun May 12 '20 at 06:21
Your questions are unclear. – May 12 '20 at 06:23
I am sorry, may be my question is not clean. You were really trying to help me. give me 10 minutes, I will edit the question and add examples. – Mithun May 12 '20 at 06:24
please take a look at the questions one more time. I have edited it. – Mithun May 12 '20 at 06:35
`SELECT status, phone, COUNT(*) AS count FROM people WHERE phone='9876543210' GROUP BY phone, status` and run your code. You can assign values to array $status[] = $row['count']. You should learn more how php and mysql works. – May 12 '20 at 06:50

HOW to use MySQL COUNT() function using PHP

1 Answers1