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 6 of 6
  1. #1
    Regular Coder
    Join Date
    Dec 2010
    Location
    Kent, UK
    Posts
    573
    Thanks
    23
    Thanked 10 Times in 10 Posts

    LEFT JOIN not working

    hi guys, im still kind of new to table joins, however i do know that this join im using has to be left join as if one fails i still need it to connect the others. Im just having a bit of trouble with this one, i believed this is how i would join them all togeter, but its not working, so could someone just have a look through the join, tell me where ive gone wrong, and explain i to me so that i know for next time please, as so i dont make the mistake again

    PHP Code:
    $querynew=mysql_query("SELECT u.*,co.country,c.*,p.protection,w.weapon,ui.*,r.rank FROM users AS u LEFT JOIN countries AS co ON u._countryid=co.id LEFT JOIN crews c on c.id = u.crew LEFT JOIN protection AS p ON u._protectionid=p.id LEFT JOIN weapons AS w ON u._weaponid=w.id LEFT JOIN user_info AS ui ON ui.username=u,username LEFT JOIN ranks AS r ON u._rankid=r.id WHERE u.username='$fetch->username' LIMIT 1");
    $fetchnew mysql_fetch_object($querynew); 
    thanks guys

    Dan
    http://360-tactics.co.uk/forum/index.php

    Crime-Wave

    please post your code wrapped in tags
    please post your PHP wrapped in tags

  • #2
    New Coder
    Join Date
    Dec 2010
    Posts
    32
    Thanks
    5
    Thanked 2 Times in 2 Posts

    coder

    hey,

    PHP Code:
    $querynew=mysql_query("SELECT u.*,co.country,c.*,p.protection,w.weapon,ui.*,r.rank FROM users AS u LEFT JOIN countries AS co ON u._countryid=co.id LEFT JOIN crews c on c.id = u.crew LEFT JOIN protection AS p ON u._protectionid=p.id LEFT JOIN weapons AS w ON u._weaponid=w.id LEFT JOIN user_info AS ui ON ui.username=u,username LEFT JOIN ranks AS r ON u._rankid=r.id WHERE u.username='$fetch->username' LIMIT 1");
    $fetchnew mysql_fetch_object($querynew); 
    Try again with that

  • #3
    Regular Coder
    Join Date
    Dec 2010
    Location
    Kent, UK
    Posts
    573
    Thanks
    23
    Thanked 10 Times in 10 Posts
    i tried that, but im getting the same error:

    Code:
    Warning: mysql_fetch_object(): supplied argument is not a valid MySQL result resource in /home/crimewav/domains/crime-wave.co.uk/public_html/includes/inc-footer.php on line 7
    http://360-tactics.co.uk/forum/index.php

    Crime-Wave

    please post your code wrapped in tags
    please post your PHP wrapped in tags

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,664
    Thanks
    80
    Thanked 4,643 Times in 4,605 Posts
    DEBUG DEBUG DEBUG.

    And make the code readable.

    PHP Code:
    $sql "SELECT u.*,co.country,c.*,p.protection,w.weapon,ui.*,r.rank "
         
    " FROM users AS u LEFT JOIN countries AS co ON u._countryid=co.id "
         
    " LEFT JOIN crews c on c.id = u.crew " 
         
    " LEFT JOIN protection AS p ON u._protectionid=p.id "
         
    " LEFT JOIN weapons AS w ON u._weaponid=w.id "
         
    " LEFT JOIN user_info AS ui ON ui.username=u,username "
         
    " LEFT JOIN ranks AS r ON u._rankid=r.id "
         
    " WHERE u.username='" $fetch->username "' LIMIT 1";

    echo 
    "<hr>DEBUG SQL: " $sql "<hr/>\n";

    $querynew=mysql_query$sql ) or die( mysql_error() ); 
    Take the SQL that is echoed and copy/paste it into some query tool so you get a real error message.

    HINT: I see at least one error, just on inspection. Should I tell you?

    Oh, w.t.h. You have a comma in "u,username" instead of a period in "u.username".

    See it?

    And *NEVER* omit the "or die( ... )".
    [Or, if you do omit it, make a separate check that the query succeeded. Never assume a query will succeed.]
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    Regular Coder
    Join Date
    Dec 2010
    Location
    Kent, UK
    Posts
    573
    Thanks
    23
    Thanked 10 Times in 10 Posts
    thanks Old Pedant, it was the u,username that was causing the problem, how silly of me not to see it in the first place.

    just as to clarrify, is the way im writing these left joins, together, the right way?

    Cheers.

    Dan
    http://360-tactics.co.uk/forum/index.php

    Crime-Wave

    please post your code wrapped in tags
    please post your PHP wrapped in tags

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,664
    Thanks
    80
    Thanked 4,643 Times in 4,605 Posts
    Quote Originally Posted by Dan13071992 View Post
    just as to clarrify, is the way im writing these left joins, together, the right way?
    It can be. It may or may not be.

    Is it possible, for example to have a USER record that has no associated USER_INFO?

    If not, you probably should INNER JOIN to USER_INFO instead of LEFT JOIN. In which case it should be the *first* table joined to.

    But if it is possible that *none* of those other tables would have records, then yes.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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