Viewing 12 posts - 1 through 12 (of 12 total)
  • MySQL SELECT from multiple table doing my head in.
  • PJay
    Free Member

    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?

    MrBlond
    Free Member

    Does MYSQL support ANSI joins?

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

    geoffj
    Full Member

    you need a join in there somewhere I reckon.

    Fresh Goods Friday 696: The Middling Edition

    Fresh Goods Friday 696: The Middlin...
    Latest Singletrack Videos
    simonfbarnes
    Free Member

    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

    MrBlond
    Free Member

    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

    HTTP404
    Free Member

    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

    PJay
    Free Member

    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.

    soma_rich
    Free Member

    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…

    HTTP404
    Free Member

    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.

    PJay
    Free Member

    $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.

    geoffj
    Full Member

    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).

    HTTP404
    Free Member

    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. 😀

Viewing 12 posts - 1 through 12 (of 12 total)

The topic ‘MySQL SELECT from multiple table doing my head in.’ is closed to new replies.