Advice: Joining the best solution here?

Databases
1

Hello, I’m working on developing a simple CMS for a personal website and I’m encountering a problem. I want to be able to pass variables to display posts of a certain type on a particular page but I’m struggling with the sql logic behind it.

I have three tables:
Categories - Contains name, id of category
Posts - contains name, title, content, date, etc of posts made
Relationships - Contains categoriy_id and post_id to match posts to multiple categories if applicable.

My code as it was is just:

$result = mysql_query("SELECT category_name FROM categories WHERE category_id=$catID");
				
				while($row = mysql_fetch_array($result)) {
					$id = stripslashes($row['post_id']);
					$title = stripslashes($row['post_title']);
					$content = stripslashes($row['post_content']);

But that query won’t return the post details and no info concerning what category a post belongs to exists on the post table. So I’m assuming I need to do a JOIN?

I’ve never had to do a JOIN before so is this the right method I should be aiming for and can anyone advise me where to start?

EDIT: The relationship table

2

for someone who has never done a join before, you have certainly nailed the design of the relationship table accurately – no auto_increment id, and a composite primary key consisting of both foreign keys

well done :slight_smile:

your join query would look like this –

SELECT categories.category_name 
     , posts.name
     , posts.title
     , posts.content
     , posts.postdate
  FROM categories 
LEFT OUTER
  JOIN relationships 
    ON relationships.category_id = categories.category_id
LEFT OUTER
  JOIN posts 
    ON posts.post_id = relationships.post_id
 WHERE categories.category_id = $catID

i used LEFT OUTER JOINs in case you select a category that has no posts in it – if every category has at least one post in it, use INNER JOINs instead

one minor point – BIGINT is probably too big, use INTEGER throughout

3

I understand the concept at least its the coding part I have no clue on. Thanks for the code provided, I have to head out but I will try incorporating it when I get back and see how it goes.

Thanks R937

4

Ha ha, it works, that’s amazing. The code seems so simple but put a gun to my head I wouldn’t have a clue about LEFT OUTER or INNER. Amazing how powerful that is. I also took your advice on BIGINT, I read up on it and INT seems more than enough.

Thank you very much for the help.

5

another thread, another SQL convert

:smiley: