Level 104
What is this; why are they different?
/COUNT(*)100
and
COUNT()100
and
COUNT()*100
Jan 14, 2022
6
Level 1
How to Convert QUERY MySQL to Laravel 8
Hi everybody, Can anyone help me please, i want to convert this query to Laravel:
$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,
AVG(rating) AS avg_rating
FROM ratings
WHERE id_product = ?');
$stmt->bind_param('i', $id_product);
$stmt->execute();
$percents = array(;
$stmt->bind_result($percents[5], $percents[4], $percents[3], $percents[2], $percents[1], $avg_rating);
if ($stmt->fetch()) {
for ($i in array(5, 4, 3, 2, 1)) {
echo "%i stars: " . round($percents[$i]) . "%<br>";
}
echo "Average rating: $avg_rating<br>";
} else {
echo "This product is not yet rated<br>";
Sorry i'm still newbie. Thank u so much for anyhelp.
Level 1
@tykus sorry the code i didn't test yet, 'cause i don't know how to convert to laravel, i'll test the query first, and i'll be back if i have a trouble. sorry for my wrong. Actually the code i got from the internet.
Level 73
Use three back ticks to show your code.
Is it this query you want to convert to query builder or Eloquent?
You know that you can just use DB::select('<your query here>', <bind variables here>)
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,
AVG(rating) AS avg_rating
FROM ratings
WHERE id_product = ?
Btw the SQL code is wrong. You need to tell it what to count.
Level 1
Hello Mr, actually my point is to make a rating system like UDEMY's site, so I have got a code how to make it with full PHP and is not converted yet to Laravel, this is the code and work perfectly as I want, BTW this code I got from the internet.
$star1 = 0;
$star2 = 5;
$star3 = 7;
$star4 = 0;
$star5 = 8;
$tot_stars = $star1 + $star2 + $star3 + $star4 + $star5;
for ($i = 5; $i >= 1; --$i) {
$var = "star$i";
$count = $$var;
$percent = $count * 100 / $tot_stars;
for ($j = 1; $j <= 5; ++$j) {
echo $j <= $i ? "<i class='mdi mdi-star me-n1 text-warning'></i> " : "<i class='mdi mdi-star me-n1 text-light'></i>";
}
printf("%2d (%5.2f%%)", $count, $percent, 2);
}
and below is the result as I want
★★★★★8 (40.00%)
★★★★☆0 ( 0.00%)
★★★☆☆7 (35.00%)
★★☆☆☆5 (25.00%)
★☆☆☆☆0 ( 0.00%)
but as you know the above $tot_stars value come from static and is not from the database, so I want to give $tot_stars value from the database, and below my code, i put in controller.
//For Rating
$star5 = Review::where('rating', '4.5')
->orWhere('rating', '5')
->get();
$star4 = Review::where('rating', '3.5')
->orWhere('rating', '4')
->get();
$star3 = Review::where('rating', '2.5')
->orWhere('rating', '3')
->get();
$star2 = Review::where('rating', '1.5')
->orWhere('rating', '2')
->get();
$star1 = Review::where('rating', '0.5')
->orWhere('rating', '1')
->get();
$tot_stars = $star1->sum('rating') + $star2->sum('rating') +
$star3->sum('rating') + $star4->sum('rating') + $star5->sum('rating');
for ($i = 5; $i >= 1; --$i) {
$var = "star$i";
$count = $$var;
$percent = $count * 100 / $tot_stars;
for ($j = 1; $j <= 5; ++$j) {
echo $j <= $i ? "<i class='mdi mdi-star me-n1 text-warning'></i> " : "<i class='mdi mdi-star me-n1 text-light'></i>";
}
printf("%2d (%5.2f%%)", $count, $percent, 2);
}
from the above code, I got the error that said Unsupported operand types: Illuminate\Database\Eloquent\Collection * int. Can u help me with how to convert the above code to laravel. sorry, I'm not a native speaker and I'm still a newbie.
Please or to participate in this conversation.