Clever Geek Handbook

Grouping a request with php

The main question!

I have 2 tables

PRODUCE

   +-----+--------------+  
   | id  |  fruit_name  |
   +--------------------+
   | 1   |   Apple      |
   | 2   |   Banana     |
   | 3   |   Carrot     |
   +-----+--------------+

VARIETIES

   +-----+---------------+----------------+
   | id  |  fk_fruit_id  |  variety_name  |
   +-----+---------------+----------------+
   | 1   |   1           |    Cox         |
   | 2   |   1           |    Braeburn    |
   | 3   |   2           |    Chester     |
   | 4   |   3           |    Kotaka      |
   | 5   |   3           |    Imperial    |
   | 6   |   3           |    Oneal       |
   +-----+---------------+----------------+

I would like to list the varieties for each fruit, for example.

APPLE - Cox, Braeburn

BANANA - Chester

CARROT - Kotaka, Imperial, Oneal

My current code

$query   = "SELECT * FROM produce, varieties WHERE produce.id = varieties.fk_fruit_id"; 

$result  = mysql_query($query) or die('Error : ' . mysql_error()); 
while ($row     = mysql_fetch_array($result, MYSQL_ASSOC)) { 

$produce_fruit_code   = $row['fruit_code']; 
$variety_name   = $row['variety_name']; 

echo $produce_fruit_code.' - '.$variety_name.'<br/>';

}

which outputs:

Apple - Cox
Apple - Braeburn
Banana - Chester
Carrot - Kotaka
Carrot - Imperial
Carrot - Oneal

Not a million miles, but still not there. Any help is greatly appreciated, thanks!

+3


source to share


6 answers


It won't help you completely, but you will get the most of what you want. There are some edge cases that are problematic.



$query   = "SELECT * FROM produce, varieties WHERE produce.id = varieties.fk_fruit_id"; 
$result  = mysql_query($query) or die('Error : ' . mysql_error()); 

$produce_fruit_code = "";
while ($row     = mysql_fetch_array($result, MYSQL_ASSOC)) { 
  if ($produce_fruit_code != $row['fruit_code'])
  {
    $produce_fruit_code = $row['fruit_code'];
    echo "<br/>".$produce_fruit_code." - ". $row['variety_name'];
  } else {
    echo ", ".$row['variety_name']; 
  }
}
0


source


You could probably get one short sql expression to do this for you, but I would choose to manipulate data arrays.

For example (not tested and sorry formatting):



$query   = "SELECT * FROM produce, varieties WHERE produce.id = varieties.fk_fruit_id";     
$result  = mysql_query($query) or die('Error : ' . mysql_error()); 

$res=array();

while ($row     = mysql_fetch_array($result, MYSQL_ASSOC)) { 

    $produce_fruit_code   = $row['fruit_code']; 
    $variety_name   = $row['variety_name']; 

    if(isset($res[$produce_fruit_code])){
        $res[$produce_fruit_code]+=','.$variety_name;
    }else{
        $res[$produce_fruit_code]=$variety_name;
    }

}        
print_r($res);
+1


source


If you are using MySQL you can use the group_concat extension for grouping. Something like:

SELECT 
   f.fruitname as fruit, 
   GROUP_CONCAT(distinct v.varietyname separator ',') as variety  
FROM fruit f JOIN varieties v ON produce.id = varieties.fk_fruit_id;

or similar. Sorry my sql is a little rusty pretty now. More about this article http://www.mysqlperformanceblog.com/2006/09/04/group_concat-useful-group-by-extension/ and of course here: http://dev.mysql.com/doc/refman /5.0/en/group-by-functions.html

If you are not working with MySQL and your database does not support group_concat, consider buffering these results. In large databases and with many concurrent users, your application can slow down significantly when loading all the data and saving it locally every time.

+1


source 


$query   = "SELECT * FROM produce, varieties WHERE produce.id = varieties.fk_fruit_id"; 

echo "<dl>";

$result  = mysql_query($query) or die('Error : ' . mysql_error()); 
while ($row     = mysql_fetch_array($result, MYSQL_ASSOC)) { 

    if($row['fruit_name'] != $current_fruit_name) {
        $current_fruit_name = $row['fruit_name'];
        echo "<dt>$current_fruit_name</dt>"; 
     }
echo "<dd>" . $row['variety_name'] . "</dd>";

}

echo "";

If you need CSS that makes the definition list look Name - X,Y,Z

like the question, please let me know.

0


source


You can request this directly

SELECT 
   f.fruitname as fruit, 
   GROUP_CONCAT(distinct v.varietyname separator ',') as variety  
FROM fruit f JOIN varieties v ON produce.id = varieties.fk_fruit_id;
GROUP BY produce.id
0


source


Sort everything into arrays in a loop while

. This should work:

while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) { 

   $produce_fruit_code   = $row['fruit_code']; 
   $variety_name   = $row['variety_name']; 

   if ($produce_fruit_code == "Apple") {
   $apple_array[] = $variety_name;
   }
   if ($produce_fruit_code == "Banana") {
   $banana_array[] = $variety_name;
   }
   if ($produce_fruit_code == "Carrot") {
   $carrot_array[] = $variety_name;
   }

}

echo "Apples:" . implode(", ", $apple_array) . "<br/>";
echo "Bananas:" . implode(", ", $bananas_array) . "<br/>";
echo "Carrots:" . implode(", ", $carrots_array) . "<br/>";
-3


source






More articles:


All Articles