[thelist] sql group one field
Richard Bennett
richard.bennett at skynet.be
Wed Sep 1 04:19:50 CDT 2004
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.
More information about the thelist
mailing list