CODEWITHSUNDEEP
phpmysql
Jochem Kuijpers
Jochem Kuijpers

Reputation: 1797

PHP/MySQL blog system

I'm making a blog system and I want to add 'tags' to my blogposts. These are similar to the tags you see here, they can be used to group posts with similar subjects.

I want to store the tags in the database as a comma-separated string of words (non-whitespaced strings). But I'm not quite sure how I would search for all posts containing tag A and tag B.

I don't like a simple solution that works with a small database where I retrieve all data and scan it with a PHP loop, because this won't work with a large database (hundreds if not thousands of posts). I do not intend to make this many blogposts, but I want the system to be solid and save worktime on the PHP scripts by getting right results straight from the database.

Let's say my table looks like this (it's a bit more complex actually)

blogposts:
id   | title         | content_html                  | tags
0    | "hello world" | "<em>hello world!</em>"       | "hello,world,tag0"
1    | "bye world"   | "<strong>bye world!</strong>" | "bye,world,tag1,tag2"
2    | "hello you"   | "hello you! :&gt;"            | "hello,tag3,you"

How would I be able to select all posts that contain "hello" as well as "world" in the tags? I know about the LIKE statement, where you can search for substrings, but can you use it with multiple substrings?

Upvotes: 0

Views: 841

Answers (2)

Zhube
Zhube

Reputation: 504

You can't index a field of csv values in a meaningful way, and SQL doesn't support being able to find a unique value in a field of CSV values. Instead, you'll want to set up two more tables, and make the following alteration to your table.

blogposts:

id | title | content_html

tags:

id | tag_name

taxonomy table:

id | blogpost_id | tag_id

When you add a tag to a blog post, you will insert a new record into the taxonomy table. When you query for data, you'll join across all three tables to get the information similar to this:

SELECT `tag_name` FROM `blogposts` INNER JOIN `blogposts_taxonomy` ON
`blogposts`.`id`=`blogposts_taxonomy`.`blogpost_id` INNER JOIN `blogpost_tags` ON
`blogposts_taxonomy`.`tag_id`=`blogpost_tags`.`id` WHERE `blogposts`.`id` = someID;

//UPDATE

Setting up the N:M relationship gives you a lot of options during the build out of your application. For example, say you wanted to be able to search for blogposts that were all tagged "php." You could do that as follows:

SELECT `id`,`html_content` FROM `blogposts` INNER JOIN `blogposts_taxonomy` ON
`blogposts`.`id`=`blogposts_taxonomy`.`blogpost_id` INNER JOIN `blogposts_tags` ON
`blogposts_taxonomy`.`tag_id`=`blogposts_tags`.`id` WHERE `blogposts_tags`.`tag_name`="php";

That will return all blogposts that have been tagged with the "php" tag.

Cheers

Upvotes: 1

barryhunter
barryhunter

Reputation: 21091

If you really wanted to store the data like this the FIND_IN_SET mysql function would be your friend.

Have the function twice in the where clause.

But it will perform horribly - having a linked table one-to-many style as already suggested is MUCH better idea. If you have lots of the same tags a many-to-many could be used. Via a 'post2tag' table.

Upvotes: 0

Related Questions