kingnutter Posted August 27, 2009 Share Posted August 27, 2009 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" Link to comment https://forums.phpfreaks.com/topic/172112-solved-query-containing-explode-for-tag-cloud/ Share on other sites More sharing options...
trq Posted August 27, 2009 Share Posted August 27, 2009 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. Link to comment https://forums.phpfreaks.com/topic/172112-solved-query-containing-explode-for-tag-cloud/#findComment-907465 Share on other sites More sharing options...
kingnutter Posted August 27, 2009 Author Share Posted August 27, 2009 Would a separate table for tags be the right move then? Link to comment https://forums.phpfreaks.com/topic/172112-solved-query-containing-explode-for-tag-cloud/#findComment-907471 Share on other sites More sharing options...
trq Posted August 27, 2009 Share Posted August 27, 2009 Indeed. Link to comment https://forums.phpfreaks.com/topic/172112-solved-query-containing-explode-for-tag-cloud/#findComment-907472 Share on other sites More sharing options...
kingnutter Posted August 27, 2009 Author Share Posted August 27, 2009 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. Link to comment https://forums.phpfreaks.com/topic/172112-solved-query-containing-explode-for-tag-cloud/#findComment-907480 Share on other sites More sharing options...
trq Posted August 27, 2009 Share Posted August 27, 2009 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. Link to comment https://forums.phpfreaks.com/topic/172112-solved-query-containing-explode-for-tag-cloud/#findComment-907562 Share on other sites More sharing options...
kingnutter Posted August 27, 2009 Author Share Posted August 27, 2009 Excellent and simple explanation. Thank you very much. Link to comment https://forums.phpfreaks.com/topic/172112-solved-query-containing-explode-for-tag-cloud/#findComment-907705 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.