Hi,

My sql query is giving me multiple records - producers, masters and
models. I want to display all models belonging to a particular
producer in a table column seperated by comma. However, I am unable to
do so. All my records get displayed in a seperate row. How can I get
this done.

eg.

Table Producers
producer_id producer_name
1 A
2 B

Table Models
model_id
producer_id model_name
1
1 ABC
2
1 DEF
3
1 QWE
4
2 ASD
5
2 ZXC

I want results to be in the format

Producer Name Model Name
A ABC,
DEF,QWE
B ASD, ZXC


TIA

Pankaj wrote:
> Hi,
>
> My sql query is giving me multiple records - producers, masters and
> models. I want to display all models belonging to a particular
> producer in a table column seperated by comma. However, I am unable to
> do so. All my records get displayed in a seperate row. How can I get
> this done.
>
First you have to join the tables

SELECT * FROM Producers, Models WHERE Producers.producer_id=Models.producer_id

When you loop throw the results for the query you will then need to store
those values into a array

while($row=....) {
$array[$row['producer_name']][]=$row['model_name'];
}

Now you have a multidimensional array, with model_names linked to each
producer, all you have to do is to implode() the subarray with the
model_name's and you get your comma separated strings of models.


--

//Aho

Pankaj <panahuja [at] gmail.com> wrote:
> My sql query is giving me multiple records - producers, masters and
> models. I want to display all models belonging to a particular
> producer in a table column seperated by comma. However, I am unable to=

> do so. All my records get displayed in a seperate row. How can I get
> this done.
>
> eg.
>
> Table Producers
> producer_id producer_name

> Table Models
> model_id producer_id model_name

> I want results to be in the format
>
> Producer Name Model Name
> A ABC, DEF, QWE
> B ASD, ZXC

No PHP question, but just for the heck of it, something like this will =

work in MySQL (untested):

SELECT
p.`producer_name` as 'name',
GROUP_CONCAT(m.`model_name` SEPARATOR ', ') as 'models'
FROM `Producers` p
LEFT JOIN `Models` m
ON m.`producer_id` =3D p.`producer_id`
GROUP BY `producer_name`

-- =

Rik Wasmus