Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 10 of 10
  1. #1
    New Coder
    Join Date
    Jul 2006
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Unhappy join over two tables throwing an error

    hi
    am trying to select from 2 tables by making joins and arrrgghhh i cant get it to work. pls help

    Code:
    $sql=select 
    topic_title, 
    topics.mod_id, 
    topic_creator, 
    topics.role, 
    topics.post_timestamp, 
    post_creator, 
    posts.role, 
    posts.post_timestamp  
    from 
    topics, 
    posts 
    where topics.topic_ic=posts.topic_id, 
    topics.mod_id=$mod , 
    posts.post_timestamp 
    between '$start' and '$end'";
    
    i tried running it in a vba sql tester thing thats how i got the msg below, but intially whn i run it it just says cannot connect

    Syntax error in query expression 'topics.topic_ic=posts.topic_id, topics.mod_id=$mod , posts.post_timestamp between '$start' and '$end'"'.
    Last edited by guelphdad; 08-21-2006 at 12:24 AM. Reason: proper thread title

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,631
    Thanks
    4
    Thanked 147 Times in 138 Posts
    Did you try hard coding values in there for $start and $end? If so were the values returned what you expected?

  • #3
    New Coder
    Join Date
    Jul 2006
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts
    what do u mean by hard coding is it to put direct values in the stmnt?

  • #4
    New Coder
    Join Date
    Jul 2006
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts
    i echoed the sql stmnt
    and this it below
    select topic_title, topics.mod_id, topic_creator, topics.role, topics.post_timestamp, post_creator, posts.role, posts.post_timestamp from topics, posts where topics.topic_ic=posts.topic_id, topics.mod_id=ISA401 , posts.post_timestamp between '2006-08-07' and '2006-08-19' cannot connect
    the cannot connect is my error handling message.

  • #5
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,631
    Thanks
    4
    Thanked 147 Times in 138 Posts
    then the problem is you aren't connecting to your server or your database, not the query itself.

    note you should use
    Code:
    . mysql_error();
    appended to your connection call and your database call so that you know the exact error message occuring rather than use your own error message.

  • #6
    New Coder
    Join Date
    Jul 2006
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts
    hi
    i can connect to the databse is just my error message is mis leading so i changed it to cannot run query.
    i added the mysql_error(); but i think the sql stmnt is wrong.
    can i have 2 sql stmnts, though and have a loop to display them separately?

  • #7
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,631
    Thanks
    4
    Thanked 147 Times in 138 Posts
    would you like people to guess at the problem? how about you actually post what you are running, including your connection statement. Then show us the actual mysql error that is occuring and not an error message that you may be having the script print out.

  • #8
    Senior Coder
    Join Date
    Sep 2005
    Posts
    1,791
    Thanks
    5
    Thanked 36 Times in 35 Posts
    Should the 'where' part of the query not have connecting ANDs or ORs between the parts rather than commas?
    Code:
    where topics.topic_ic=posts.topic_id AND
    topics.mod_id=$mod AND
    posts.post_timestamp between '$start' and '$end'";

  • #9
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,631
    Thanks
    4
    Thanked 147 Times in 138 Posts
    I hate missing the obvious.

  • #10
    Senior Coder
    Join Date
    Jul 2005
    Location
    New York, NY
    Posts
    1,084
    Thanks
    4
    Thanked 19 Times in 19 Posts
    2 problems:

    if this is your query echoed by PHP:
    Code:
    select 
    topic_title, 
    topics.mod_id, 
    topic_creator, 
    topics.role, 
    topics.post_timestamp, 
    post_creator, 
    posts.role, 
    posts.post_timestamp 
    from topics, 
    posts 
    where topics.topic_ic=posts.topic_id, 
    topics.mod_id=ISA401 , 
    posts.post_timestamp between '2006-08-07' and '2006-08-19'
    The part I put in red is a string, not an int, and therefore needs to be in quotes.

    Further, are you sure it's topic_ic and not topic_id?
    Last edited by guelphdad; 08-21-2006 at 03:39 PM.


  •  

    Posting Permissions

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