php/mysql usage = display full list, checkbox each record where user is 'yes'

[quasiman]

Hey guys, I can't wrap my head around how to make this work right...

 

I have three tables:

CREATE TABLE `games` (
  `g_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(150) DEFAULT NULL,
  PRIMARY KEY (`g_id`));

CREATE TABLE IF NOT EXISTS `game_player` (
  `r_id` int(11) NOT NULL AUTO_INCREMENT,
  `p_id` int(11) DEFAULT NULL,
  `g_id` int(11) DEFAULT NULL,
  `bool` int(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`r_id`));

CREATE TABLE IF NOT EXISTS `players` (
  `p_id` int(11) NOT NULL AUTO_INCREMENT,
  `playerid` varchar(150) NOT NULL,
  PRIMARY KEY (`p_id`),
  UNIQUE KEY `playerid` (`playerid`));

 

The players table is my list of users, and they're tied to the list of games via the game_player table.

 

So here's my issue... I'm trying to show the full list of games, and then check mark each record where the player does play it. 

This is what I have so far - it shows all the games,  but it's not checking the boxes.

 

$result = mysql_query("SELECT * FROM games") or die(mysql_error());
while($row = mysql_fetch_array($result))
{
$newquery = "SELECT * FROM game_player, players
			WHERE game_player.p_id = players.p_id
			AND game_player.g_id = ".$row['g_id']. "
			AND players.playerid = {$userid}";
$query = mysql_query($newquery) or die(mysql_error());
	if($query['bool'] == 1) { $set_checked = " CHECKED";
	} 
	else{ $set_checked = ""; 
	}
	echo "<input type=\"checkbox\" name=\"box1\" value=\"".$query['g_id']."\"" . $set_checked . "/>".$row['name']."<br />\n";
}

[quasiman]

Ok, now that the topic has been moved to a php section, can someone answer the mysql question? I think a left outer join would do it, but I haven't been able to make it work yet.

[quasiman]

bump

[quasiman]

bump

[xyph]

I'm not sure why this was moved to PHP, when it's a MySQL issue.

 

Here's the query you want

SELECT g.name, gp.bool
FROM games as g
   LEFT JOIN game_player as gp
   ON g.g_id = gp.g_id AND gp.p_id = 1

 

Here's my sample data

INSERT INTO `games` (`g_id`, `name`) VALUES
(1, 'game1'),
(2, 'game2'),
(3, 'game3'),
(4, 'game4');

INSERT INTO `game_player` (`r_id`, `p_id`, `g_id`, `bool`) VALUES
(1, 1, 1, 1),
(2, 1, 3, 1),
(3, 1, 4, 0),
(4, 2, 2, 1),
(5, 2, 3, 0);

INSERT INTO `players` (`p_id`, `playerid`) VALUES
(2, 'quasiman'),
(1, 'xyph');

 

Here's what the results look like.

mysql> SELECT g.name, gp.bool
    -> FROM games as g
    ->    LEFT JOIN game_player as gp
    ->    ON g.g_id = gp.g_id AND gp.p_id = 1;
+-------+------+
| name  | bool |
+-------+------+
| game1 |    1 |
| game2 | NULL |
| game3 |    1 |
| game4 |    0 |
+-------+------+
4 rows in set (0.00 sec)

 

mysql> SELECT g.name, gp.bool
    -> FROM games as g
    ->    LEFT JOIN game_player as gp
    ->    ON g.g_id = gp.g_id AND gp.p_id = 2;
+-------+------+
| name  | bool |
+-------+------+
| game1 | NULL |
| game2 |    1 |
| game3 |    0 |
| game4 | NULL |
+-------+------+
4 rows in set (0.00 sec)

[quasiman]

AH!  That's perfect, thank you!  I think my mistake was assuming that I needed to incorporate the players table, and I was making it harder on myself.

 

Thanks again!