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++;
}

 

[marc2003]

^^ that there is a lesson in how not to display results from a database.... horrible! go back and look at your branch details thread and see how mysql_fetch_assoc is used.....

and for the main problem, what's the plan? do you have a column to keep running totals or something? or will this be added to another table so you keep records of each individual form submission?

 

[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?

 

[unknowndomain]

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

while ($data = mysql_fetch_assoc($result)) {

	echo '	</tr>
		<td>' . $data['name'] . '</td>
		<td>' . $data['img'] . '</td>
		<td><input type="checkbox" value="game" name="' . $data['game_id'] . '[]">
		</tr> ';
}

Some thing like this I would say

EDIT: You beat me to it.

 

[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.

 

[marc2003]

well the the name of each checkbox should be the gameid. and the value should be 1. those column names in the 2nd table are actually named after gameid right? not just numbered....

 

[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.

 

[marc2003]

the fact that your game list is dynamic is complicating things a little. i would have hard referenced each game in the form processing section something like.....

$userid= $_SESSION['userid']; //i guess you're using sessions for logged in users?
$game1 = $_POST['game1'];
$game2 = $_POST['game2'];
mysql_query("INSERT INTO tbl(userid, game1, game2) VALUES('$userid', '$game1', '$game2')");

but i guess that's not viable now....

 

[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.

 

[marc2003]

this needs to be looked at differently i think. you don't want to be adding columns dynamically. now i'm a complete database noob but howabout....

a table for games

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

a table with user details.....

- userid
- name
- etc

and a 3rd table (games played) which matches userid with game id. just 2 columns in this

-userid
-gameid

with data added it would look like this....

userid-------------gameid
1--------------------1
1--------------------2
1--------------------3
1--------------------5
2--------------------1
2--------------------3

much easier to work with that i think.

 

[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?

 

[marc2003]

well now we're using a different method i would actually have the checkbox with the gameid as the name AND the value. that way we can loop through the whole $_POST array and only add data to the database if the name equals the value. this would only happen if the checkbox was ticked. it will ignore other post values. something like this

if($_POST['submit']) {
    $userid = //session or post userid?
    //we don't want duplicate results in the table. remove previously submitted results by this user
    mysql_query("DELETE FROM gamesplayed WHERE userid = '$userid'");
    foreach($_POST as $key => $value) {
        if($key == $value) {
            mysql_query("INSERT INTO gamesplayed(userid, gameid) VALUES('$userid',  '$value')");
        }
    }
}