SQL COUNT not showing result on PHP Table?-CodePudding

My SQL table:

| user | bln | tipe |
|----- |-----| -----|
|  A   |  1  |  2   |
|  A   |  1  |  2   |
|  B   |  1  |  2   |
|  A   |  1  |  1   |
|  C   |  1  |  1   |
|  D   |  1  |  1   |

Using COUNT, in PHP I want to list the table like this:

| user |COUNT(tipe)|
|----- |-----------|
|  A   |  2  |
|  B   |  1  |
|  C   |  0  |
|  D   |  0  |

$sql = mysqli_query($link, "SELECT user, COUNT(tipe) FROM keg where bln=1 and tipe=2 GROUP BY user order by id asc;");
if(mysqli_num_rows($sql) == 0){
    echo '<tr><td colspan="8">Tidak ada data.</td></tr>';
}else{
    $no = 1;
    while($row = mysqli_fetch_assoc($sql)){
        echo '
        <tr>
            <td>'.$no.'</td>
            <td>'.$row['user'].'</td>
            <td>'.$row['tipe'].'</td>
        </tr>
        ';
        $no  ;
    }
}
?>

But the table output is like this:

| user |COUNT(tipe)|
|----- |-----------|
|  A   |           |
|  B   |           |

Is my problem in $row = mysqli_fetch_assoc($sql)?

I tried the SQL in an SQL windows and it's showing the output normally, but it doesn't in PHP.

CodePudding user response：

There are two issues here. First, from the PHP side, you're trying to access the column tipe in the result set, but there is not such column - it's called COUNT(tipe). This can be solved by using an alias.
Second, your where clause removes all the rows that don't have bln=1 and tipe=2, so you aren't getting any results for users C and D. One solution would be to not use a where clause, but move the condition to a case expression for the count function to evaluate:

SELECT   user, COUNT(CASE WHEN bln = 1 AND tipe = 2 THEN 1 END) AS tipe
FROM     keg 
GROUP BY user 
ORDER BY id ASC;