[SOLVED] List of data - Easy Method?

[hkothari]

I have two tables full of data, one of the has a field corresponding to the primary keys of rows of the other table, in some cases there are more than one row they correspond to, so is there an easy way to add a list of corresponding values, or would I have to find a way to do that on my own, eg. using a string and making it comma delimited and than parsing it in php?

[kickstart]

Hi

 

The function you need is group_concat.

 

For a rough example if you had a table of employees and a table containing their addresses with one row per address line order by line number, something like:-

 

SELECT Name, group_concat(AddressLine ORDER BY LineNo SEPARATOR ',')

FROM Employees

LEFT OUTER JOIN EmployeerAddress ON Employees.Id = EmployeerAddress.EmployeeId

GROUP BY Name

 

(of course grouping by name is a bit silly as there could potentially be duplicate names, but this is just to give you the idea).

 

All the best

 

Keith

[hkothari]

Thank you, but I was looking around and it looks like I would have multiple rows for the same data then with group_concat, except the field with multiple values, correct.?

Also, how would I enter the data into the database for this to work?

[kickstart]

Hi

 

That would come down to what fields you want and how you do the grouping.

 

Can't really say more without knowing the table layouts, what you want to extract and sme example data.

 

All the best

 

Keith

[hkothari]

I'll try to elaborate, hopefully, that will help.

 

I have table_thing consisting of:

id, title, description

and table_children consisting of:

id, title, belongs

 

'belongs' in table_children corresponds to the id's of things in table_thing.

So an example of the tables would be like:

 

table_thing

1JimA person

2SamAnother person

3Soupa food

 

table_children

1people1,2

2stuff that starts with s2,3

 

And I want to be able to search through these using like SELECT * FROM table_children WHERE belongs=2, showing children that belong to item 2

[kickstart]

Hi

 

Not really possible. There are bodge ways around it but you would land up with hideously unreadable and complicated code.

 

The real solution is to split off the belong column onto a seperate table, with one row per item per child:-

 

table_belongs

id child_id thing_id

1 1 1

2 1 2

3 2 2

4 2 3

 

All the best

 

Keith

[hkothari]

Would it be possible to use a text datatype for "belongs" and when I search though it, just use

WHERE belongs LIKE "2,%" OR  belongs LIKE "%,2,% OR belongs LIKE "%,2" OR belongs="2"

[kickstart]

Hi

 

Possible but slow and combersome, and prevents you simply doing a join onto table_thing should you want all the things that a person has in belongs.

 

All the best

 

Keth

[hkothari]

ok, thank you very much.