SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 28
  1. #1
    SitePoint Wizard Darren884's Avatar
    Join Date
    Aug 2003
    Location
    Southern California, United States
    Posts
    1,616
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Help With MySQL Query

    SELECT * FROM $topics_table,$replies_table WHERE $topics_table.board_id = '{$row2['board_id']}' AND $replies_table.board_id = '{$row2['board_id']}'

    What is the problem there? There is a topic in the topics table but not in the replies table. I am doing a mysql count and it is turning up 0 results found.
    Have a good day.

  2. #2
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,706
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    SELECT * FROM 
    $topics_table
    LEFT OUTER JOIN $replies_table 
    ON $topics_table.board_id = $replies_table.board_id 
    AND $topics_table.board_id = '{$row2['board_id']}'

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    left outer join is the solution, here are minor corrections only --

    SELECT * FROM
    $topics_table
    LEFT OUTER JOIN $replies_table
    ON $topics_table.board_id = $replies_table.board_id
    WHERE $topics_table.board_id = {$row2['board_id']}
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Wizard Darren884's Avatar
    Join Date
    Aug 2003
    Location
    Southern California, United States
    Posts
    1,616
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Wow thanks guys.
    Have a good day.

  5. #5
    SitePoint Wizard Darren884's Avatar
    Join Date
    Aug 2003
    Location
    Southern California, United States
    Posts
    1,616
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Argh ok guys now I am doing an echo of the results but I get the error:
    Could not retrieve the last post. Column: 'date' in order clause is ambiguous. Please email technical support here.

    I changed the query to this:
    SELECT * FROM $topics_table LEFT OUTER JOIN $replies_table ON $topics_table.board_id = $replies_table.board_id WHERE $topics_table.board_id = '{$row2['board_id']}' ORDER BY date DESC LIMIT 1

    What could be the problem? I want to order them by date, where date is in both tables.
    Have a good day.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    then you must qualify which date column to sort on

    ... ORDER BY $replies_table.`date`

    by the way, DATE is a reserved word, and if you're going to use it as a column name (not recommended) then you should always backtick it
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard Darren884's Avatar
    Join Date
    Aug 2003
    Location
    Southern California, United States
    Posts
    1,616
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hi how would I order by the date by both tables?
    Have a good day.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    you will have to give an example of what you mean

    here are some rows with two dates on them, how do you want them sorted?

    2005-01-28 2005-02-03
    2005-02-01 2005-02-02
    2005-01-27 2005-01-29
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Wizard Darren884's Avatar
    Join Date
    Aug 2003
    Location
    Southern California, United States
    Posts
    1,616
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I want to sort the results by both tables field date.
    Have a good day.

  10. #10
    SitePoint Wizard Darren884's Avatar
    Join Date
    Aug 2003
    Location
    Southern California, United States
    Posts
    1,616
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok is this even possible? I did what you did and low and behold I cant even echo a result. ????
    Have a good day.

  11. #11
    SitePoint Wizard Darren884's Avatar
    Join Date
    Aug 2003
    Location
    Southern California, United States
    Posts
    1,616
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here is my php code

    PHP Code:
    <?php

    // Connect is already above here

                    
    $last_post_query "SELECT * FROM $topics_table LEFT OUTER JOIN $replies_table ON $topics_table.board_id = $replies_table.board_id WHERE $topics_table.board_id = '{$row2['board_id']}' ORDER BY $topics_table.date DESC LIMIT 1";
                    if (
    $last_post_query_result = @mysql_query ($last_post_query)) {
                        if (
    mysql_num_rows ($last_post_query_result) == "0")
                        {
                            
    $last_post "<i>None</i>";
                        }
                        else
                        {
                            while (
    $row3 = @mysql_fetch_array ($last_post_query_result)) {
                                
    $last_post $row3['subject'];
                            }
                        }    
                    }
    echo 
    $last_post;


    ?>
    Have a good day.

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    sorry, i don't know why your code isn't working

    what's up with LIMIT 1? or is that just for testing purposes?

    as far as sorting by dates of both tables, that doesn't make sense

    you are joining the topics table and the replies table

    the topics date will be on the same row as the replies date

    i.e. two dates on the same row, like i showed in post #8
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Wizard Darren884's Avatar
    Join Date
    Aug 2003
    Location
    Southern California, United States
    Posts
    1,616
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    HI the query seems to be correct but I cannot output any information from it...
    Have a good day.

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    have you tried running the query outside of your php script? in a front end like phpmyadmin or something similar?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SitePoint Wizard Darren884's Avatar
    Join Date
    Aug 2003
    Location
    Southern California, United States
    Posts
    1,616
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I will try that one second.
    Have a good day.

  16. #16
    SitePoint Wizard Darren884's Avatar
    Join Date
    Aug 2003
    Location
    Southern California, United States
    Posts
    1,616
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    AH yes I see it joins them and then there are two of the same fields. How would I output a certain field when I do my while statement. One of the fields has stuff in it whil ethe second is NULL. How would I do that?
    Have a good day.

  17. #17
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    do you know how a LEFT OUTER JOIN works? all the fields that come from the right table will be null whenever there's a row from the left table that doesn't have a match based on your ON condition

    at this point, and in view of the LIMIT 1 that you haven't explained, i would be inclined to ask you where you got your query and why you're running it

    what are you actually trying to do?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  18. #18
    SitePoint Wizard Darren884's Avatar
    Join Date
    Aug 2003
    Location
    Southern California, United States
    Posts
    1,616
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I am trying to display the last post of a custom forum I am doing by combining the topics table with the replies table so tehy are one table. So I want to take the last post that was based, a topic or reply it doesnt matter, and display it.
    Have a good day.

  19. #19
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    please, help me out here

    your query uses the terms "topic", "reply" and "board"

    in your last post you mention "post" and "forum"

    it's awfully hard to visualize what's going on without seeing the actual table names and column names

    if all you want is the last post, why do you need a join? why not just get the last post from the posts table?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  20. #20
    SitePoint Wizard Darren884's Avatar
    Join Date
    Aug 2003
    Location
    Southern California, United States
    Posts
    1,616
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    because the topics_table and the post_table are the posts. I would like to put them together and search them as if they were 1 table.
    Have a good day.

  21. #21
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,706
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Why are you reluctant to actually show your tables here with some sample data and then basically start a new thread a slightly similar query where you're going to be asked the same thing so folks can visualize what you are after?

    1) show your tables with three or four rows in each
    2) show your sample output with four or so rows of what you want to display.

  22. #22
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Darren884
    because the topics_table and the post_table are the posts. I would like to put them together and search them as if they were 1 table.
    okay, now i sort of understand

    first, we'll treat them as if they were one table

    that would be UNION, not JOIN

    then we'll just select the latest date from them
    Code:
    select max(thedate) as maxdate
      from (
           select `date` as thedate
             from topics_table
           union
           select `date` as thedate
             from post_table
           ) as onetable
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  23. #23
    SitePoint Wizard Darren884's Avatar
    Join Date
    Aug 2003
    Location
    Southern California, United States
    Posts
    1,616
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi I am doing this one now that I read the manual:
    SQL-query:
    (
    SELECT topic_id
    FROM topics_support
    WHERE board_id = '2'
    )
    UNION (

    SELECT reply_id
    FROM replies_support
    WHERE board_id = '2'
    )

    However it only displays 1 result, and there is actually 2 results. It only displays the results from the topics table, not the replies table. What could be wrong?
    Have a good day.

  24. #24
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    topic_id and reply_id are numbers

    if they are the same number, i.e. the result of the UNION is duplicate rows, then UNION removes one of them
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  25. #25
    SitePoint Wizard Darren884's Avatar
    Join Date
    Aug 2003
    Location
    Southern California, United States
    Posts
    1,616
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    How do I make it so that it does not do that?
    Have a good day.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •