Tags from Blog Comma Separated Help

[abrahamgarcia27]

So i have a blog that has the tags store in one field in my blog table. So tags are saved as this in each blog post with its respective tags. 

 

Tags: john, carlos, the great tag, this is a tag

 

What i want to do is get random tags from each blog post and display them. I am not sure how i can begin to make this query. Do you guys have an idea on how to do this? 

 

 

[requinix]

So i have a blog that has the tags store in one field in my blog table.

That's a problem. You need to use a separate table that has the post ID and the tag ID/name, with one post getting multiple rows for multiple tags.

post_id | tag
--------+----
    123 | john
    123 | carlos
    123 | the great tag
    123 | this is a tag

Once you have that the rest should be easy(ier). Though I'm not sure what you mean by getting random tags from each post... A single random tag? All posts or one at a time?

[abrahamgarcia27]

Yeah i was afraid that i needed to separate the tags table. Well i was thinking that i could do the following

 

*bold are the ones that the random query would retrieve

 

Blog Post 1 | john,carlos,micheal

Blog Post 2 | test, test2, test3

Blog Post 3 | php, testing, blog post

 

Result Tags carlos, test3, php

I know separating would make this taks really easy

[requinix]

As long as you agree that you need another table,

 

You "have" to do it in your code. Use a query to get the posts and tags, explode() to get each tag, then array_rand to get (the key of) a random value.

[abrahamgarcia27]

I did this. Do you think this can cause me a problem? Now i see that once their is a new tags for a new row it does not separate it with a comma i guess i have to do a tags table. Or do you see any way i can fix this. 

function getBlogTags2(){

	$db = Database::obtain(DB_SERVER, DB_USER, DB_PASS, DB_DATABASE); 
	$sql = "SELECT tags FROM `".TABLE_BLOG."` ORDER BY RAND()"; 
	$rows = $db->fetch_array($sql); 
	
	$data = array();
	
	foreach ($rows as $t) {
		$theTags .= $t['tags'];
	}
	$theTags = explode(',', $theTags);

	foreach ($theTags as $value) {
		$data[] = $value;
		
	}
	return $data;

}

Then to display i did this

$tags = getBlogTags2();

$random_keys=array_rand($tags,5);
foreach ($random_keys as $key => $value) {
	echo $tags[$value];
	echo "<br>";
}

[requinix]

Even though the code can be fixed (it's also particularly... wordy) yes, you should go to that new table.

function getBlogTags2() {
	$db = Database::obtain(DB_SERVER, DB_USER, DB_PASS, DB_DATABASE);
	$sql = "SELECT tags FROM `" . TABLE_BLOG . "` ORDER BY RAND() LIMIT 1";
	$row = $db->fetch_array($sql);
	return explode(",", $row["tags"]);
}

So to be clear, this will get 5 random tags (however not in random order) from a single randomly-chosen blog post.

[Barand]

DATA:

mysql> SELECT * FROM blog;
+---------+----------+
| blog_id | blogname |
+---------+----------+
|       1 | Blog 1   |
|       2 | Blog 2   |
|       3 | Blog 3   |
|       4 | Blog 4   |
|       5 | Blog 5   |
+---------+----------+

mysql> SELECT * FROM blogtag;
+------------+---------+----------+
| blogtag_id | blog_id | tag      |
+------------+---------+----------+
|          1 |       1 | any      |
|          2 |       1 | baker    |
|          3 |       1 | canary   |
|          4 |       2 | able     |
|          5 |       2 | beta     |
|          6 |       2 | canada   |
|          7 |       3 | alpha    |
|          8 |       3 | banana   |
|          9 |       3 | cup      |
|         10 |       4 | arachnid |
|         11 |       4 | beetle   |
|         12 |       5 | anchor   |
+------------+---------+----------+

QUERY (to select one random tag for each blog):

SELECT b.blog_id
 , blogname
 , SUBSTRING_INDEX(GROUP_CONCAT(tag ORDER BY RAND()),',',1) as tag
FROM blog b
    LEFT JOIN blogtag USING (blog_id)
GROUP BY blog_id;

RESULT:

+---------+----------+--------+
| blog_id | blogname | tag    |
+---------+----------+--------+
|       1 | Blog 1   | canary |
|       2 | Blog 2   | able   |
|       3 | Blog 3   | banana |
|       4 | Blog 4   | beetle |
|       5 | Blog 5   | anchor |
+---------+----------+--------+