Issue sorting inside while loop

Question

I have a strange issue happening. I've got a table where I want to order the number of times a product was sold. I'll post the query, but the issue is inside the while loop.

Query:

$cat = $db->query("SELECT *, COUNT(id_produto) as quantos FROM produtos p JOIN pedidos m ON p.id_prod = m.id_produto GROUP BY id_produto ORDER BY quantos +0 DESC");
$cat->execute();

Now the loop:

while($res_cat = $cat->fetch(PDO::FETCH_ASSOC)){
$quantidade_ok = $res_cat['quantos'] * $res_cat['qtde'];
$quanti = array($quantidade_ok);
rsort($quanti);
foreach($quanti as $ord){
echo $ord."<br>";
}

The output is: 40 50 4 1 3 2 10

But I want it to be: 50 40 10 4 3 2 1

I'll be happy for any help.

I have to sort it inside the while loop. Is there another way I should do it? — Mimi spo
– Mimi spo, Commented Jun 17, 2021 at 12:03
What is the output of $quanti after complete the while loop? Give me the output of print_r($quanti) after finishing while loop. — Anisur Rahman
– Anisur Rahman, Commented Jun 17, 2021 at 12:03
Array ( [0] => 40 ) 50 Array ( [0] => 50 ) 4 Array ( [0] => 4 ) 1 Array ( [0] => 1 ) 3 Array ( [0] => 3 ) 2 Array ( [0] => 2 ) 10 Array ( [0] => 10 ) — Mimi spo
– Mimi spo, Commented Jun 17, 2021 at 12:06

Andrea Olivato · Accepted Answer · 2021-06-17 12:08:29Z

1

You're sorting within a loop (so your sorting already happened), and you're sorting an array with just 1 value $quanti, so your sort does nothing.

You have 2 ways to approach this properly: edit your query to actually sort how you wish, or sort the PHP array before looping it.

Option 1: Edit your query

Based on your code it seems clear that you wish to sort by the product of quantos times qtde. So you can simply edit your query as follows:

SELECT *, COUNT(id_produto) as quantos 
FROM produtos p JOIN pedidos m ON p.id_prod = m.id_produto 
GROUP BY id_produto 
ORDER BY (quantos*qtde) DESC

Option 2: Sort via PHP

If you prefer to sort via PHP as you don't want to change your query, you can simply populate a temporary array, the product of quantos times qtde as key and then use krsort to sort the array.

In code:

$array = [];
while ($res_cat = $cat->fetch(PDO::FETCH_ASSOC)) {
    $key = $res_cat['quantos'] * $res_cat['qtde'];
    $array[$key] = $res_cat;
}
krsort($array);
foreach ($array as $ord => $res_cat) {
    echo $ord."<br>";
}

answered Jun 17, 2021 at 12:08

Andrea Olivato

2,5452 gold badges19 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Mimi spo Over a year ago

The query solution looks nice too. But how would I output (quantos*qtde) inside my table?

Andrea Olivato Over a year ago

You can add it as an additional field, (quantos*qtde) as resulting_product

Mimi spo Over a year ago

So sorry, but I didn't understand this

Mimi spo Over a year ago

The query sort worked for me too. This is how it looks like now: $cat = $db->prepare("SELECT *, COUNT(id_produto) as quantos, COUNT(id_produto)*qtde as quantos_sim FROM produtos p JOIN pedidos m ON p.id_prod = m.id_produto GROUP BY id_produto ORDER BY quantos_sim DESC"); Thanks, @Andrea Olivato!

Tobias · Accepted Answer · 2021-06-17 12:07:41Z

0

You overwrite $quanti each time so it always has a single value.

Try this:

$quanti = [];
while($res_cat = $cat->fetch(PDO::FETCH_ASSOC)) {
    $quantidade_ok = $res_cat['quantos'] * $res_cat['qtde'];
    $quanti[] = $quantidade_ok;
}

rsort($quanti);

foreach($quanti as $ord){
    echo $ord."<br>";
}

answered Jun 17, 2021 at 12:07

Tobias

1,0051 gold badge12 silver badges22 bronze badges

Collectives™ on Stack Overflow

Issue sorting inside while loop

2 Answers 2

Option 1: Edit your query

Option 2: Sort via PHP

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Option 1: Edit your query

Option 2: Sort via PHP

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related