How I select several values from the same table

Question

How do I select the values 1, 2, 3, 4 and 5 of the same table without doing that lot of SELECT ?

<?php
$rateone    = 1;
$ratetwo    = 2;
$ratethree  = 3;
$ratefour   = 4;
$ratefive   = 5;

$query = "SELECT COUNT(rate) as rate FROM tb_comment WHERE id_mark=:post_id AND rate=:rateone";

    $resultone = $conexao->prepare($query);
    $resultone->bindParam(':post_id', $post_id, PDO::PARAM_STR);
    $resultone->bindParam(':rateone', $rateone, PDO::PARAM_INT);
    $resultone->execute();
    $count = $resultone->rowCount();

    if($count =1){
        $loop = $resultone->fetchAll();
        foreach ($loop as $show) {
            $Crateone = $show['rate'];
        }
    }

echo $Crateone;
?>

Answer 1

If you want each rate come in a separate line, that’s enough:

SELECT      rate, COUNT(*) AS row_count
FROM        tb_comment
WHERE       id_mark=:post_id
GROUP BY    rate

Note that I changed the AS rate for AS rate_sum, and added the rate original in SELECT. Avoid using AS with names that already exist in the table, not to confuse. If necessary, I put example of COUNT() also.

If you prefer you can add one ORDER BY rate, or ORDER BY rate DESC to determine the order from minor to major, or the inverse.

Remember to change the loop to pick up all the lines.

if($count > 0){
    $loop = $resultone->fetchAll();
    foreach ($loop as $show) {
        echo $show['rate'] . ' - ' . $show['row_count'];
    }
}

Can also simplify:

while ( $show = $resultone->fetch( PDO::FETCH_ASSOC ) ) {;
   echo $show['rate'] . ' - ' . $show['row_count'];
}

Assembling the code:

$query =
 'SELECT rate, COUNT(*) AS row_count FROM tb_comment WHERE id_mark=:post_id GROUP BY rate';

$resultone = $conexao->prepare($query);
$resultone->bindParam(':post_id', $post_id, PDO::PARAM_STR);
$resultone->execute();

while ( $show = $resultone->fetch( PDO::FETCH_ASSOC ) ) {
   echo $show['rate'] . ' - ' . $show['row_count'] . "<br>\n";
}

And yet, if you want to use the results separately:

$query =
 'SELECT rate, COUNT(*) AS row_count FROM tb_comment WHERE id_mark=:post_id GROUP BY rate';

$resultone = $conexao->prepare($query);
$resultone->bindParam(':post_id', $post_id, PDO::PARAM_STR);
$resultone->execute();

$ratings = array( 0, 0, 0, 0, 0 );
while ( $show = $resultone->fetch( PDO::FETCH_ASSOC ) ) {
   $ratings[ (int) $show['rate'] ] = $show['row_count'];
}
for ( $i = 1; $i <= 5; $i++ ) {
     echo $i . ' - ' . $ratings[$i] . "<br>\n"; 
}