mysql organization help

[randomfool]

I'm working on a website and trying to figure out a way to organize my db and dbs are my specialty. What I'm tryng to figure out is how I can tag posts with categories and then find those posts later usnig the categories. Right now I have three tables. As of right now none of this is coded it's all on paper but eventually it'll be done using mysql 4.1.

 

Posts
title	pid	content
Test post	01	lorem ipsum....

Categories
name	tid
cat1	01
cat2	02

linkp2c
pid	tid
01	01
01	02

I'm trying to figure out how I can use the categories to pick a post for example I want to find test post using category 01 and 02. Does this make any sense? Thanks!

 

-Ryan

[Wildbug]

SELECT DISTINCT title,content FROM posts,categories,linkp2c WHERE linkp2c.tid=categories.tid AND posts.pid=linkp2c.pid AND (categories.name='cat1' OR categories.name='cat2');

 

or maybe

 

SELECT DISTINCT title,content
FROM
categories JOIN linkp2c ON categories.tid=linkp2c.tid
JOIN posts ON posts.pid=linkp2c.pid
WHERE
categories.name='cat1'
OR categories.name='cat2'

 

[Barand]

If you already know the category ids are 01,02, as the question implies, then you don't need the category table in the query

SELECT DISTINCT p.title, p.content
FROM
posts p INNER JOIN linkp2c l ON p.pid = l.pid
WHERE
l.tid IN ('01' , '02')

[randomfool]

Thanks for the replies.

 

This is my first project that requires a lot of database access involved. I want to make sure that what I'm doing makes sense before I write a lot of code and find out I'm doing something stupid. In practice I won't know the categories until someone picks them. The idea is to allow people to filter posts by multiple categories so they can select cat1, cat2, or cat 1 & 2 and only see posts that match all of the selected categories.

 

-Ryan