[SOLVED] Query containing EXPLODE for tag cloud?

[kingnutter]

Hi everyone,

 

I am trying to create a tag cloud from all rows of field 'moj_genre' in table 'mojocd' which contains tags separated by commas, splitting them all up in one neat query.

 

I have written the query below in hybrid MySql/English. Is this possible and, if so, what would be the correct syntax?

 

"SELECT moj_genre FROM mojocd ***EXPLODE moj_genre on comma*** GROUP BY ***EXPLODEd results of moj_genre*** ORDER BY count DESC"

[trq]

Storing values in one field comma separated is a pretty poor design and your not going to get it done in a simple query. You'll need to use PHP to split the results yourself.

[kingnutter]

Would a separate table for tags be the right move then?

[trq]

Indeed.

[kingnutter]

Cheers. I'm not having much luck Googling this.

 

How does a tag table work? (This is where I got stuck on a previous project)

 

I am thinking that the field in my entry form should be the same as present (tags split by commas). I then split this into an array to enter into the tags table.If DISTINCT it creates a new entry but what happens to a duplicate tag? Is another instance created reffing a project_id? Or is there only one instance of each tag each having an expandable array of project_ids?

 

Just a pointer to a good tutorial would be really helpful.

 

Thanks.

[trq]

You would have a table of tags.

 

[pre]

id      name

1      food

2      drink

3      car

[/pre]

 

Then a table of articles.

[pre]

id      title

1      sandwhich

2      fish with wine

3      eggs benadict

4      audi

[/pre]

 

Then, a table to join them.

[pre]

id      tag_id      article_id

1          1                1

2          1                2

3          2                2

4          3                3

[/pre]

 

Its a horrible example (its real late here) but can you see the relationships? Notice that article number 2 (fish with wine) has two tags? Both food and drink.

[kingnutter]

Excellent and simple explanation.

 

Thank you very much.