Error in showing error message when everything returns zero?

Navigation
5

I have an error in wanting to add an error message when there are no votes for any product.

When there are no votes in a product, all the variables in the query return the print value of 0 , apparently taking the value 0 as a valid data as if it were a vote of value 0

How can I create a condition for the variables or the while ($stmt->fetch()) that if all the values in the query return the value 0 this shows the error message?

  

Note: That the condition or parameter is only executed if the entire query of its variables returns value 0 if there is any numeric value of 0.5 not executed. 

$id_product = 1;
$stmt = $con->prepare("SELECT SUM(rating IN (4.5, 5))/COUNT(*)*100 AS pct_5_star,
       SUM(rating IN (3.5, 4))/COUNT(*)*100 AS pct_4_star,
       SUM(rating IN (2.5, 3))/COUNT(*)*100 AS pct_3_star,
       SUM(rating IN (1.5, 2))/COUNT(*)*100 AS pct_2_star,
       SUM(rating IN (0.5, 1))/COUNT(*)*100 AS pct_1_star,
       SUM(CASE WHEN rating IN (4.5, 5) THEN rating ELSE 0 END) AS in_5_star,
       AVG(rating) AS avg_rating
FROM ratings
WHERE id_product=?");

$stmt->bind_param("i",$id_product);
$stmt->execute();

$stmt->bind_result($pct_5_star, $pct_4_star, $pct_3_star, $pct_2_star, $pct_1_star, $in_5_star, $avg_rating);

while ($stmt->fetch()) {
    echo "5 estrellas " . number_format($pct_5_star). "% <br />";
    echo "4 estrellas " . number_format($pct_4_star). "% <br />";
    echo "3 estrellas " . number_format($pct_3_star). "% <br />";
    echo "2 estrellas " . number_format($pct_2_star). "% <br />";
    echo "1 estrellas " . number_format($pct_1_star). "% <br /><br />";
    echo "<b>Promedio:</b> " . number_format($avg_rating,1). "% <br />";
} else {
  echo "No existe votos de este producto";
}
    
asked by AmLy 17.10.2017 в 05:22
source

2 answers

1

I hope to see the question well understood.

Add all the variables and if the sum is zero send the error. 

while ($stmt->fetch()) {
    if (($pct_1_star + $pct_2_star + $pct_3_star + $pct_4_star + $pct_5_star) == 0) {
         echo "No existe votos de este producto";
        continue;
    }
    echo "5 estrellas " . number_format($pct_5_star). "% <br />";
    echo "4 estrellas " . number_format($pct_4_star). "% <br />";
    echo "3 estrellas " . number_format($pct_3_star). "% <br />";
    echo "2 estrellas " . number_format($pct_2_star). "% <br />";
    echo "1 estrellas " . number_format($pct_1_star). "% <br /><br />";
    echo "<b>Promedio:</b> " . number_format($avg_rating,1). "% <br />";
}

However if from the query you do not want those results use HAVING 

HAVING (pct_1_star +  pct_2_star + pct_3_star + pct_4_star + pct_5_star ) > 0

Asi: 

SELECT SUM(rating IN (4.5, 5))/COUNT(*)*100 AS pct_5_star,
       SUM(rating IN (3.5, 4))/COUNT(*)*100 AS pct_4_star,
       SUM(rating IN (2.5, 3))/COUNT(*)*100 AS pct_3_star,
       SUM(rating IN (1.5, 2))/COUNT(*)*100 AS pct_2_star,
       SUM(rating IN (0.5, 1))/COUNT(*)*100 AS pct_1_star,
       SUM(CASE WHEN rating IN (4.5, 5) THEN rating ELSE 0 END) AS in_5_star,
       AVG(rating) AS avg_rating
FROM ratings
WHERE id_product=?
HAVING (pct_1_star +  pct_2_star + pct_3_star + pct_4_star + pct_5_star ) > 0
    
answered by 21.10.2017 / 18:13
source
1

You could use the calculation of AVG that you are already using to obtain the average of the total result, in case you did not find results the variable $avg_rating will be 0 also, for this you create a check if said variable is greater than 0 to show your results otherwise you throw error message.

Example: 

while ($stmt->fetch()) {
    //Si el promedio es mayor a 0, muestras resultados.
    if ($avg_rating > 0) {
        echo "5 estrellas " . number_format($pct_5_star). "% <br />";
        echo "4 estrellas " . number_format($pct_4_star). "% <br />";
        echo "3 estrellas " . number_format($pct_3_star). "% <br />";
        echo "2 estrellas " . number_format($pct_2_star). "% <br />";
        echo "1 estrellas " . number_format($pct_1_star). "% <br /><br />";
        echo "<b>Promedio:</b> " . number_format($avg_rating,1). "% <br />";
    } else { //Mensaje de error, el promedio dio 0 como resultado.
        echo "No existe votos de este producto";
    }

} 
$stmt->close();


Now you can also calculate the votes cast, that is, if there is a vote, show the statistics in case there is no vote, you send the error message.

In this way you also get the total votes cast for your statistics, I have simply added to your sentence one more calculation, COUNT(*) votos .

Alternative example: 

$id_product = 1;
//He añadido COUNT(*) votos
$stmt = $con->prepare("SELECT SUM(rating IN (4.5, 5))/COUNT(*)*100 AS pct_5_star,
       SUM(rating IN (3.5, 4))/COUNT(*)*100 AS pct_4_star,
       SUM(rating IN (2.5, 3))/COUNT(*)*100 AS pct_3_star,
       SUM(rating IN (1.5, 2))/COUNT(*)*100 AS pct_2_star,
       SUM(rating IN (0.5, 1))/COUNT(*)*100 AS pct_1_star,
       SUM(CASE WHEN rating IN (4.5, 5) THEN rating ELSE 0 END) AS in_5_star,
       AVG(rating) AS avg_rating,
       COUNT(*) votos
       FROM ratings
       WHERE id_product=?");

$stmt->bind_param("i",$id_product);
$stmt->execute();
//He añadido $votos para COUNT(*) votos.
$stmt->bind_result($pct_5_star, $pct_4_star, $pct_3_star, $pct_2_star, $pct_1_star, $in_5_star, $avg_rating,$votos);    

if ($stmt->fetch()) {
    //Si existe algun voto, muestras estadísticas.
    if ($votos > 0) {

        echo "5 estrellas " . number_format($pct_5_star). "% <br />";
        echo "4 estrellas " . number_format($pct_4_star). "% <br />";
        echo "3 estrellas " . number_format($pct_3_star). "% <br />";
        echo "2 estrellas " . number_format($pct_2_star). "% <br />";
        echo "1 estrellas " . number_format($pct_1_star). "% <br /><br />";
        echo "<b>Promedio:</b> " . number_format($avg_rating,1). "% <br />";
        echo "<b>Total votos emitidos: $votos</b>";

    } else { //Existe 0 votos, muestras mensaje de error.
        echo "No existe votos de este producto";
    }

} 
$stmt->close();
    
answered by 21.10.2017 в 19:07
Get all javascript errors Can this code be optimized? Graphics WinForms Vb.Net