[thelist] sql group one field

[Richard Bennett]

On Wednesday 01 September 2004 08:31, Burhan Khalid wrote:
> I'm sure there is a one-shot way of doing this, but here would be my
> attempt (PHP example to show algorithm -- without error checking):
>
> $results = mysql_query("SELECT UNIQUE model FROM `tablename`");
> $output = array();
> while($row = mysql_fetch_assoc($results))
> {
>    $query = "SELECT serie FROM `tablename` WHERE model =
> '".$row['model']."'";
>    $r2 = mysql_query($query);
>    while($row2 = mysql_fetch_assoc($r2))
>    {
>       $temp[] = $row2['serie'];
>    }
>    $output[$row['model']] = implode(",",$temp);
>    $temp = array();
> }
>
> This would give you:
>
> echo $output['abc'];
>
> 1234,1233,1222

Thanks for that example.
I found mySQL 4.1 has something called GROUP_CONCAT() to do what I needed, and 
there is a nice workaround for older SQL versions using mysql variables given 
here:
http://www.experts-exchange.com/Databases/Mysql/Q_20955939.html

But in the end I decided to use 2 queries, one to display a list of models as 
hyperlinks, and the display the associated series seperately, if a model was 
clicked. Seemed a bit cleaner...

Thanks to all for your ideas,

Richard.