PHP/MySQL problem driving me insane

[Techmonkey]

Ok so I am trying to get in to PHP and MySQL properly, however I have run in to a little problem that I just cannot get my head round.

I have 2 tables

h2h_gameids
- game_id = Auto incrementing number
- name = name of the game (ie BF2, WOW, etc)
- img = url for image link

h2h_gamesplayed
- user_id = user id of the person
- 1 = corresponds to game_id in the h2h_gameids table
- 2 = corresponds to game_id in the h2h_gameids table
- 3 = corresponds to game_id in the h2h_gameids table
- etc

So new games can be added to the h2h_gameids table, which then adds a new column to the h2h_gamesplayed table with the name of the game_id.

This bit works fine.

The user can then bring up the list of games with a checkbox next to each one so they can select the games that they play.

Again this bit works fine.

However the user then clicks submit and it should add a 1 or 0 in to the column corresponding to game_id selected. I just cannot work this bit out at all.

Here is the code so far for the selection screen:

Code:

<?php

$DBhost = "dbhost";
$DBuser = "dbuser";
$DBpass = "dbpass";
$DBName = "dbnamet";

mysql_connect($DBhost,$DBuser,$DBpass) or die("Unable to connect to database");

@mysql_select_db("$DBName") or die("Unable to select database $DBName");


if (!isset($_POST['submit'])) {

echo '

<table>
    <tr>
        <td colspan="3">Please select the games you play from the list below:</td>
    </tr>
    <tr>
        <td colspan="3"><hr /></td>
    </tr>

<form action="" method="post" name="selectitems">';

$result = mysql_query("SELECT * FROM h2h_gameids ORDER BY name ASC");

while ($row = mysql_fetch_assoc($result)) {
    $gamename = $row['name'];
    $gameimg = $row['img'];
    $gameid = $row['id'];
    echo '<tr><td>' . $gamename . '</td><td><img src="' . $gameimg . '" alt="' . $gamename . '" title="' . $gamename . '"></td> <td><input type="checkbox" value="1" name="' . $gameid .'"></td></tr>';
}
echo '
<tr>
    <td colspan="2"><hr /></td><td colspan="2"><input type="submit" name="submit" value="Submit"></td></form>
</tr>
</table>

';
}
?>

Any of you uber coding monkeys know what I can do?

If the columns in h2h_gamesplayed were static it would be simple, but I want it to expandable.

[Martin]

Rather than having a column for each game in the h2h_gamesplayed table, can't you just have a row for each game instead?

So the only columns you'd have in the link table would be:
user_id - Corresponds to the user id
game_id - Corresponds to the game id

Then for your checkbox you'd have..

Code:

<input type="checkbox" name="gameid[]" value="' . $gameid . '" />

The benefit of having the checkbox name "gameid[]" is that when the form is posted, it treats the checkboxes like an array. So you could, for example, when inserting into your database do:

Code:

<?php

$userid = $_GET['userid']; // Or however you would retrieve your user_id.

// Submit button press logic
if(isset($_POST['submit'])) {

    // Make sure the gameid's are in an array
    if(is_array($_POST['gameid'])) {

        // Loop through the array, and add each game_id to the table along with the user_id
        foreach($_POST['gameid'] as $gameid) {

            mysql_query("INSERT INTO h2h_gamesplayed (user_id, game_id) VALUES (".$userid.", ".$gameid.")") or die(mysql_error());

        }

    }

}

?>

Just a thought.

[Techmonkey]

Thanks for the help Martin,

I managed get it working in a similar manner to what mentioned (without the array though).

Although I then had to scrap the project as I was planning on making a plugin for the H2H site then cadet found one which was far better than anything I had planned, so no point re-inventing the wheel

[Cadet]

3 cheers for Cadet ?

[Techmonkey]

cheers? you mean slaps?

[Spid]

Can I do it?