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 5 of 5
  1. #1
    Regular Coder
    Join Date
    Feb 2005
    Posts
    663
    Thanks
    5
    Thanked 14 Times in 14 Posts

    Compare and get result from two tables

    I'm trying to compare the id from the users table to the id of the white_player in the games table, and provide me with the users name. I think I am close, but unsure of how to actually get the result. Pretty sure the query is good, but not sure what the second line should look like.

    PHP Code:
    $wplayer mysql_fetch_object(mysql_query("SELECT ".$this->settings->mysql_prefix."users.nick," .$this->settings->mysql_prefix."users.id," .$this->settings->mysql_prefix."game.white_player from ".$this->settings->mysql_prefix."users ".$this->settings->mysql_prefix."game WHERE " .$this->settings->mysql_prefix."users.id = " .$this->settings->mysql_prefix."game.white_player "));
    $wplayer $wplayer->nick

  • #2
    Regular Coder
    Join Date
    Jul 2007
    Location
    United Kingdom
    Posts
    159
    Thanks
    2
    Thanked 15 Times in 15 Posts
    http://uk2.php.net/mysql_fetch_object


    Take a quick look. That should explain it. I wouldn't use $wplayer twice like that, it might confused it. i.e.

    PHP Code:
    $wplayer mysql_fetch_object(mysql_query("SELECT ".$this->settings->mysql_prefix."users.nick," .$this->settings->mysql_prefix."users.id," .$this->settings->mysql_prefix."game.id from ".$this->settings->mysql_prefix."users ".$this->settings->mysql_prefix."game WHERE " .$this->settings->mysql_prefix."users.id = " .$this->settings->mysql_prefix."game.id "));
    $nickname $wplayer->nick
    Where nick is the column name in which the usernames are stored.



    ~Jordan
    Remember The Thank You Button Is Your Friend =]

    [ PHP / Javascript / VisualBasic / HTML / DHTML / CSS / .NET / MySQL ]

  • #3
    Regular Coder
    Join Date
    Feb 2005
    Posts
    663
    Thanks
    5
    Thanked 14 Times in 14 Posts
    Thanks for the help. I also noticed I was missing part of my query. But by following your link, I was reading that mysql_fetch_object may have an issue with duplicate field names (id in my case) and need to rename each field.

    I still haven't gotten it to work yet, but this is what I am showing. See anything wrong with it?

    PHP Code:
    $wplayer mysql_fetch_object(mysql_query("SELECT ".$this->settings->mysql_prefix."users.id as user_id," .$this->settings->mysql_prefix."users.nick as nick," .$this->settings->mysql_prefix."game.id as game_id," .$this->settings->mysql_prefix."game.white_player as white_player from ".$this->settings->mysql_prefix."users ".$this->settings->mysql_prefix."game WHERE user_id = white_player AND game_id='".$this->gid."' "));
    $white $wplayer->nick

  • #4
    Regular Coder
    Join Date
    Jul 2007
    Location
    United Kingdom
    Posts
    159
    Thanks
    2
    Thanked 15 Times in 15 Posts
    You missed out the comma between the two tables. That may be the problem!


    PHP Code:
    $wplayer mysql_fetch_object(mysql_query("SELECT ".$this->settings->mysql_prefix."users.id as user_id," .$this->settings->mysql_prefix."users.nick as nick," .$this->settings->mysql_prefix."game.id as game_id," .$this->settings->mysql_prefix."game.white_player as white_player FROM ".$this->settings->mysql_prefix."users, ".$this->settings->mysql_prefix."game WHERE user_id = white_player AND game_id='".$this->gid."' "));
    $white $wplayer->nick
    Remember The Thank You Button Is Your Friend =]

    [ PHP / Javascript / VisualBasic / HTML / DHTML / CSS / .NET / MySQL ]

  • #5
    Regular Coder
    Join Date
    Feb 2005
    Posts
    663
    Thanks
    5
    Thanked 14 Times in 14 Posts
    Edit:

    I got it finally. Here's the correct query:

    PHP Code:
    $wplayer mysql_fetch_object(mysql_query("SELECT users.id, users.nick, game.id, game.white_player FROM ".$this->settings->mysql_prefix."users AS users, ".$this->settings->mysql_prefix."game as game WHERE users.id = game.white_player AND game.id='".$this->gid."' ")); 
    Last edited by ptmuldoon; 11-20-2007 at 10:31 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
    •