MySQL SELECT from multiple table doing my head in.

I've been slowly trying to learn a bit of php and MySQL and have written a small script to read the first few topics from a phpbb forum database to display on the homepage. I'm finding MySQL a struggle but have manged to select data from two seperate tables, phpbb_posts (where I've selected all the data) and topic_id and topic_name from phpbb_topics, where the topic_id in the post table matches topic_id in the topics table (it took a while but I was actually quite pleased by this). The actual selet statement is:

$result = mysql_query("SELECT phpbb_posts.*, phpbb_topics.topic_title, phpbb_topics.topic_replies FROM phpbb_posts, phpbb_topics WHERE phpbb_posts.topic_id = phpbb_topics.topic_id ORDER BY post_time DESC LIMIT 50");

Now I want to take things a bit further. The phpbb_topics table holds a value forum_id which matches forum_id in the php_forums table. I want to extract data from the phpbb_forums table based on the forum_id in the phpbb_topics table matched to the topics_id in the php_posts table.

In psuedo-plain English the logic is fairly straight forward – I want to select all the fields in the phpbb_posts table and phpbb.topics_topic_id and phpbb_topics.topic_name from the phpbb_topics table where phpbb_posts.topic_id matches phpbb_topics.topic_id and the phpbb_forums.forum_id and phpbb_forums.forum_name where phpbb_forums.forum_id = phpbb_topics.forum_id

but I just can't work out how to write this correctly in MySQL. I feel that an AND is needed somewhere and possibly a second where clause, but I seem to have drawn a blank. I've tried looking at the online MySQL manual before asking people, but to a newbie it's pretty heavy going.

Am I on the write tracks and could someone give me some pointers?

Does MYSQL support ANSI joins?

Hated them at first but these days separating joins form your conditions makes more sense to me.

you need a join in there somewhere I reckon.

1) put phpbb_posts as po, phpbb_topics as top and use the abbrieviations
2) you can only have one "where" but say (condition_a) and (condition_b) after it

select po.*,fo.forum_name from phpbb_posts as po, phpbb_topics as top, phpbb_forums as fo where po.topic_id=top.topic_id and fo.forum_id = top.forum_id etc

or even neater, use a join

Not really understanding what you're asking, but ANSI version would be something like this:

SELECT
phpbb_posts.*,
phpbb_topics.topic_id,
phpbb_topics.topic_name,
phpbb_topics.topic_title,
phpbb_topics.topic_replies
FROM phpbb_posts
JOIN phpbb_topics ON phpbb_posts.topic_id = phpbb_topics.topic_id
JOIN phpbb_forums ON phpbb_forums.forum_id = phpbb_topics.forum_id
ORDER BY post_time DESC LIMIT 50

Your original query:-

SELECT phpbb_posts.*,
phpbb_topics.topic_title,
phpbb_topics.topic_replies
FROM phpbb_posts, phpbb_topics
WHERE phpbb_posts.topic_id = phpbb_topics.topic_id
ORDER BY post_time DESC LIMIT 50

At a guess becomes this:-

SELECT phpbb_posts.*,
phpbb_topics.topic_title,
phpbb_topics.topic_replies,
phpbb_forums.forum_id,
phpbb_forums.forum_name
FROM phpbb_posts, phpbb_topics, phpbb_forums
WHERE phpbb_posts.topic_id = phpbb_topics.topic_id
AND phpbb_forums.forum_id = phpbb_topics.forum_id
ORDER BY post_time DESC LIMIT 50

I'm assuming a straightforward table structure of:-

FORUM –< TOPIC –< POSTS

You might want to consider using table alias' as well as it makes
the SQL more readable.

SELECT pp.*,
pt.topic_title,
pt.topic_replies,
pf.forum_id,
pf.forum_name
FROM phpbb_posts pp,
phpbb_topics pt,
phpbb_forums pf
WHERE pp.topic_id = pt.topic_id
AND pf.forum_id = pt.forum_id
ORDER BY pp.post_time DESC LIMIT 50

Thanks, I'll have a play. I haven't got as far as JOIN yet, I'm finding the whole thing rather heavy going I'm not really cut out to be a programmer but I ought to be able to work it out if I go slowly.

HTTP404's solution is pretty much what I was thinking (without having looked at JOINs) but I do think I see how the JOIN thingy works too.

hmmm something like this?

select phpbb_posts.* , phpbb.topics_topic_id , phpbb_topics.topic_name
from the phpbb_topics, phpbb , phpbb_topics
where phpbb_posts.topic_id = phpbb_topics.topic_id
and
phpbb_forums.forum_id = phpbb_forums.forum_name
and
phpbb_forums.forum_id = phpbb_topics.forum_id

Thats assuming phpbb.topics_topic_id is a field in table phpBB but that looks like the database name to me….

Hard to see what you want without a look at the tables…

The "Joiney" thing operates on the the intersection of a union.
If you remember Venn diagrams? or look at one it's fairly self-explanatory.

$result = mysql_query("SELECT phpbb_posts.*, phpbb_topics.topic_title, phpbb_topics.topic_replies,phpbb_forums.forum_id,phpbb_forums.forum_name
FROM phpbb_posts, phpbb_topics, phpbb_forums WHERE phpbb_posts.topic_id = phpbb_topics.topic_id AND phpbb_topics.forum_id = phpbb_forums.forum_id
ORDER BY post_time DESC LIMIT 50");

Seems to do the trick, although I'll need to do a bit of testing, and I do understand the logic there, I'll need to explore JOINs too though I suspect. Maybe it was a bit foolish to leap in and try and write a real-world solution (albeit a very simple one) and try and work out what to do as I went along but I do seem to remember things a little easier when I've worked out what to do rather than just followed examples in a book. I don't think I'm ever going to be very good at this sort of stuff though.

Thanks for the help.

Maybe it was a bit foolish to leap in and try and write a real-world solution (albeit a very simple one) and try and work out what to do as I went along

Not foolish at all, if you have no need to do it, it doesn't get done (usually).

Understanding the structure of your tables and structuring them relationally (ie using primary / foreign keys) – the rest just comes with a little practice.

and use table alias' in your sql. 😀