Mysql array data manipulation

Question

I have an articles table with a tags column. Each row in the column contains a string that looks like:

tag1, tag3, tag5, tag0 // separated via a comma

OR

tag1 tag6 tag5 tag8 // separated via a space

Note that the number appended to "tag" signifies a different word. I need to compare each row for duplicates and return the amount of times a word was duplicated, and then put it into a loop like:

tag0 (1 times)
tag1 (2 times)
tag3 (1 times)
tag6 (1 times)
tag8 (1 times)

If you can help me, you will have my genuine thanks, after 6 hours of failed attempts. I did ask this same question earlier, but accepted the answer too soon. Mysql Results and array manipulation

Take each of the tag fields, explode them join as 1 big list, then count by tag — BugFinder
– BugFinder, Commented Apr 5, 2011 at 20:42

Marc B · Accepted Answer · 2011-04-05 20:43:22Z

2

This is why a denormalized database design fails you - you lose any ability to easily do relational queries. Given that SQL databases have horrible string manipulation facilities compared to 'external' programming languages, you'll want to suck out all those tag strings into PHP and do the deconstruction/counting there.

Had your database been properly normalized, with a seperate sub-table to link tags<->articles, the query would've been a trivial

SELECT tag.name, count(articles_tags.article_id) AS cnt
FROM tags
LEFT JOIN article_tags ON tags.id = articles_tags.tag_id
GROUP BY tag.id
ORDER BY tag.name

Now you're stuck with

SELECT id, tags from ARTICLES

and

$tag_count = array();
while($row = mysql_fetch_assoc($result)) {
   $tags = preg_split('/[ ,]/', $row['tags']);
   foreach($tags as $tag) {
       $tag_count[$tag]++;
   }
}
sort($tag_count);

answered Apr 5, 2011 at 20:43

Marc B

362k44 gold badges433 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Lea Over a year ago

Of course, I completely agree. But I didnt create the system, and I am just working with whats there. Had I created it, then I would do exactly as you have described.

Lea Over a year ago

If you were near me I would worship at your feet. Its 7am, I can now goto sleep. THANK YOU SO MUCH

Collectives™ on Stack Overflow

Mysql array data manipulation

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related