Cross Table Reverse Lookup ?

[nextgengames]

Yeah it sounds complicated to me to hence why i am posting it in here.

I have 3 tables a users, a games, and my games.

users = user data
games = game data
my_games = relationships user id and game id

So i need to know how I can take the data in the my games table and kind of reverse lookup the gid or uid to return the correct record.

What im trying to do is on user profiles have a list of there games there playing

Any Help really appreciated !

[Trs2988]

Ok, I understand what you're trying to do. It's not really as complicated as it sounds. Let's see how I can help...

When you're generating someone's profile, you should have their $uid. So all you have to do is loop through a query that selects all the $gids that go along with that $uid in my_games. Once you have the $gids, you should be able to pull out the game name from the games table. Here's an example.

echo "I play these games:<ul>";
$result = mysql_query("SELECT gid FROM my_games WHERE uid='$uid'");
while($row = mysql_fetch_assoc($result))
{
extract($row);
extract(mysql_fetch_assoc(mysql_query("SELECT game_name FROM games WHERE gid='$gid'")));
echo "<li>$game_name (GID# $gid)</li>";
}
echo "</ul>";

Make sense? Let me know how it goes.

[nextgengames]

Ok thanks, Ill give this a go and let you know how it goes

[mmonaco]

Your queries can handle multiple tables by aliasing them

<?
$SQL = "SELECT g.name FROM games g, my_games m WHERE m.user_id='$currentuserid' AND m.game_id=g.id";
?>

This query will select a list of games with a my_games relationship using an id stored in $currentuserid.

[nextgengames]

Right the 2nd one looks very complex,

I messed with the tables I gave, but heres what i wrote, not getting any errors but no results.

	<?php  

	echo "I play these games:<ul>"; 
	$result = mysql_query("SELECT game FROM wi_user_games WHERE user='uid'"); 
	while($row = mysql_fetch_assoc($result)) 
	{ 
	extract($row); 
	extract(mysql_fetch_assoc(mysql_query("SELECT game FROM wi_games WHERE gid='$game'"))); 
	echo "<li>$game_ (GID# $game)</li>"; 
	} 
	echo "</ul>";  

	?>

[Trs2988]

Change

WHERE user='uid'

to

WHERE user='$uid'

and

<li>$game_

to

<li>$game

🙂

[nextgengames]

Right ive got it working, I cant get the $uid to call but its working with a number I think the $uid is something my end mind.

This is the code we use to check logins

$a = $l->ReturnSessionData();

$row = mysql_fetch_array(mysql_query("SELECT * FROM `wi_user` WHERE uid='" . $a['UID'] . "' LIMIT 1"));

}

so i assume it should be $UID ?

Also once it executes this query the rest of the page aint parsing.

[Trs2988]

It won't be in $UID unless you use extract(). It will either be in $a['UID'] or $row['UID'].

Not sure about code execution stopping without seeing more code. From what I can see, I'm guessing that maybe since the new code is overwriting your $row variable, it can't access the old $row. Might want to change the new one to like $row2 or something.

[nextgengames]

yay got it working with the help of a friend thanks for your help !