...

View Full Version : Compare and get result from two tables



ptmuldoon
11-20-2007, 06:59 PM
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.



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

JordanW
11-20-2007, 07:04 PM
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.



$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

ptmuldoon
11-20-2007, 07:56 PM
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?



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

JordanW
11-20-2007, 08:13 PM
You missed out the comma between the two tables. That may be the problem!




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

ptmuldoon
11-20-2007, 08:44 PM
Edit:

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



$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."' "));



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum