Cannot get my head round this? (PHP/MySQL)

[The_KiD]

What I am trying to do?

Basically a database that holds a list of items, then displays the items on a page with a checkbox next to each so the user can check the ones that apply to them.

The problem
I want the list of items to be dynamic, so through an admin panel new items can be added and when the user views the page the new items are shown all with check boxes.

That much I can do

However what I cant work out is how, when the submit button is clicked it writes the yes/no or 1/0 entries back to the database for the dynamic list.

Hopefully this makes some sense.

Here is the code that displays the list of items (in this case games played):

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

$rows = mysql_num_rows($result);
  
$i = 0;

while ($i < $rows) {

	$gamename = mysql_result($result,$i,"name");
	$gameimg = mysql_result($result,$i,"img");
	$gameid = mysql_result($result,$i,"game_id");

	echo '	</tr>
		<td>' . $gamename . '</td>
		<td>' . $agmeimg . '</td>
		<td><input type="checkbox" value="game" name="'. $gameid .'[]">
		</tr> ';
	$i++;
}

 

[The_KiD]

Ok re-wrote again and slapped myself for copying and pasting from tutorials

new code

<?php

$DBhost = "mysql1031.servage.net";
$DBuser = "cadettest";
$DBpass = "Bilbo123";
$DBName = "cadettest";

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

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


echo '

<table>
	<tr>
		<td colspan="2">Please select the games you play from the list below:</td>
	</tr>
	<tr>
		<td colspan="2"><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'];
	echo '<tr><td>' . $gamename . '</td><td><input type="checkbox" value="game" name="gamechecked"></td></tr>';
}
echo '
<tr>
	<td colspan="2"><hr /></td>
</tr>
<tr>
	<td colspan="2"><input type="submit" name="go" value="Submit"></td></form>

</tr>
</table>

';
?>

I have to tables in the database (but please tell me if there is a better way to do this)

Gameids - columns are: game_id, name and img
This stores the name of the game and an image url for the game

Games_played - columns are: user_id, game1, game2, game3 etc depending on how many game have been added.
This stores the userid of the person doing the selection and a 1 or 0 for each of the game played.

So when the submit button is checked it wants to see which of the check boxes are ticked then write the info in to the games_played table.

Make sense?

 

[The_KiD]

ok so as you see here I re-wrote the code and made it nicer.

<?php

$DBhost = "serveradd";
$DBuser = "user";
$DBpass = "password";
$DBName = "database";

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

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


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="game" name="game' . $gameid .'"></td></tr>';
}
echo '
<tr>
	<td colspan="2"><hr /></td><td colspan="2"><input type="submit" name="go" value="Submit"></td></form>
</tr>
</table>

';
?>

So it makes a page with the info from the db, and puts a checkbox next to each one so the user can select which items they are interested in.

However I cant for the life of me work out how to do the post bit, so they select the ones they want then when submit is clicked it puts a 1 or 0 in the db depending on if they had that item selected.

Can someone please help me with this code I have been messing with it for a week now and am going round in circles.

 

[The_KiD]

Sorry telling you how the database is laid out might help.

basically there are 2 tables:

h2h_gameids
- game_id - Auto incrementing no
- name - Name of the game
- img - link to image

h2h_gamesplayed
- user_id - user id of the person selecting the games
- 1 - column for 1/0 relating to the game_id in h2h_gameids
- 2 - column for 1/0 relating to the game_id in h2h_gameids
- 3 - column for 1/0 relating to the game_id in h2h_gameids
-etc.

So when the person clicks submit, it puts their user id in the h2h_gamesplayed table then puts a 1 or 0 in each of the numbered columns dependign on which they had selected.

 

[The_KiD]

well the the name of each checkbox should be the gameid. and the value should be 1.

So more like this:

echo '<tr><td>' . $gamename . '</td><td><img src="' . $gameimg . '" alt="' . $gamename . '" title="' . $gamename . '"></td> <td><input type="checkbox" value="1" name="' . $gameid .'"></td></tr>';

those column names in the 2nd table are actually named after gameid right? not just numbered....

Indeed I have another form that allows the user to add another game in to the the list, it then creates a new column in this table using the game_id as the column name.

 

[The_KiD]

Yeah I know what you mean, if they were set in stone life would be easier, but I want it so new games can be added and old games removed.

So the second table needs to be dynamic.

 

[The_KiD]

ok so assuming I go for this option,

What would the end of the script look like?

What I dont know as I havent really worked with check boxes before is how I would go about entering the values in based on whether the checkbox is ticked or not.

In my mind it would be a long insert command possibly preceeded by a complicated if statement?