Home > Software engineering >  sum value column if id not change
sum value column if id not change

Time:03-31

I have a table obtained from a join where there are x number of bicycles with their id and their sizes

this is a table

ID NAME SIZE
1 Front Susp. mtb - fork 100mm L
1 Front Susp. mtb - fork 100mm M
1 Front Susp. mtb - fork 100mm S
2 Full susp. e-mtb - fork 180mm XL
2 Full susp. e-mtb - fork 180mm M
2 Full susp. e-mtb - fork 180mm M
3 Gravel Cinelli Zydeco XS
3 Gravel Cinelli Zydeco L
3 Gravel Cinelli Zydeco M
4 Ciclotour E-bike L
4 Ciclotour E-bike L
4 Ciclotour E-bike M

what i want to get is an array with the bike name and its sizes to send as ajax reply es:

response:
{
 Front Susp. mtb - fork 100mm => size=>{L,M,S},
 Full susp. e-mtb - fork 180mm=> size=>{XL,M},
 Gravel Cinelli Zydeco        => size=>{XS,L,M},
 Ciclotour E-bike             => size=>{L,M}
}

what's the best way to do it?

at the moment I acted this way:

$groupbike=$mysqli4->query("select categories.id,categories.tipo,bikes.category_id,bikes.taglia from bikes INNER JOIN categories ON bikes.category_id = categories.id group by bikes.id" );
            
            while($b =$groupbike->fetch_array(MYSQLI_ASSOC)){
                $idb=$b["id"];
                $cat=$b["category_id"];
                $tipobike=$b["tipo"];
                $tagliab=$b["taglia"];
                
                
                
                if ($b["tipo"]=$b["tipo"]) {
                    
                    if (!in_array($b["taglia"],$taglie)) {
                            
                            array_push($taglie,$b["taglia"]);
                        }
                    
                    $bikeok1[$tipobike]=array(
                        "taglia"=>$taglie,
                    );
                }
                };

CodePudding user response:

Looks pretty basic "array" manipulation:

$your_table = $mysqli4->...;
$your_result = [];
foreach($your_table as $prd){
  $your_result[$prd['name']][] = $prd['size'];
}

however, instead of name, I would recommend using "id" as a key, since there is no guarantee that the same name might have 2 ids associated... and obviously in your response you can't have 2 equal key...

In case there is also no guarantee on the pair (id, size) you might want to make them unique:

$your_table = $mysqli4->...;
$your_result = [];
foreach($your_table as $prd){
  $your_result[$prd['name']][] = $prd['size'];
}
foreach($your_result as $k => $prd){
  $your_result[$k] = array_unique($prd);
}
  •  Tags:  
  • php
  • Related