One to many relationship. Tricky for me

[figo2476]

Hi:

 

Wordpress offers extra fields, so I follow its idea and create 2 tables

 

Editions table:

id name

1  edition1

2  edition2

 

Extra field table:

(Extend the table above. Offer customized fields, for different projects needs)

edition_id key    value

1          venue  city

1          state  abc

1          club  A

2          venue  city_2       

2          state  bbb

2          club  B

 

I want to group those fields, so I can get something like

id name      venue  state club

1  edition1  city  abc  A

 

It may not be possible, but the idea is able to group information. any hint?

[Gamic]

Your query will likely have to look something like this:

<?php
$tableName="extra";
$sql="select 
	a.edition_id as id, 
	d.name, 
	a.value as venue, 
	b.value as state, 
	c.value as club
from $tableName a, $tableName b, $tableName c, editions d
where a.edition_id=b.edition_id
and c.edition_id=b.edition_id
and d.id=a.edition_id
and a.key='venue'
and b.key='state'
and c.key='club';";
?>

 

If, however, your table looked like this:

extras:
edition_id | venue | state | club
1          | city  | abc   | A
2          | city_2| bbb   | B 

Then the query would be much simpler to create. If you are not planning on having different amounts of extra information for each edition then it may be better to go this way.

[Barand]

SELECT e.id, e.name, GROUP_CONCAT (x.value SEPARATOR ' ') as xdata

FROM editions e JOIN extra x ON e.id = x.edition_id

GROUP BY e.id