Using CheckBoxes with PHP and MYSQL

[g0dfaTHer]

I have a registration form that takes info such as:

username
realname
pw
email
etc
etc

than i have a question such as:

what colors do you like?

blue []
green[]
yellow[]
red[]

now my question is this. in my data base do i list each color along with the above info on the same table

or

do i create another table for just the colors

and create 2 fields:

colorcode varchar (20)
colorname varchar (20)

i cant seem to get this check box info to read in the data base and than show up on another page with the other info

THanks in adavnce for any help. I hope this makes sense because my mind is jello....

[jatar_k]

Welcome to WebmasterWorld g0dfaTHer,

first things first

can they select many colours or only one single colour?

do i create another table for just the colors

depends on whether you want to generate the checkboxes dynamically and add or remove choices for colours over time. If you are just going to keep those 4 choices and have them statically in the html form then no need for a new table.

[g0dfaTHer]

they can select one color or 2 colors or none or all 4 colors. once they are selected, or non-selected they hit submit. than they can go to the registered person page. Click on the username on the registered person page and than everything that was filled out on the form shows up.

ex:

username: joe
realname: Joe smith
email: [e-mail]
age: 24
location: United States
colors: blue
green
red

everything on the form goes to the registered person page but the colors.

i have tired having the colors in the same table as the rest and nothing shows up. than i read that all i have to do is put colors in the table and implode the info.

i hope this makes sense. thanks

[jatar_k]

well there are a few ways to go about it

you could have 4 columns each with yes/no or 0/1, or whatever you like, to symbolize whether checked or not.

you could use a token and implode the four values and stash them in a single column giving something like this

blue¦green¦yellow¦red
or
green¦red
etc

I would guess that it is the method you are trying to display them.

Are you sure the data has been inserted properly?
Are you just trying to list the colours or put the checkboxes on the page and have the appropriate ones checked?

[g0dfaTHer]

Im not sure if the data has been entered properly. I initially had it like this....

DB = table called users.....
fields:
id ¦ username ¦ realname ¦ contact ¦ pass ¦ location ¦ website ¦ age ¦ red ¦ blue ¦ green ¦ yellow ¦ purple ¦ orange

now i have it where the fields are:
id ¦ username ¦, etc, etc ¦ colors

im trying to list the colors on another page of what was selected....

thanks

[g0dfaTHer]

to help out more i hope

on the users page where i am wanting this cehck box inf to go... my query is like this

$query1 = "Select id, username, realname, contact, website, location, age, colors FROM users Where id = '$id'";

so colors should be a field in my table correct?

[jatar_k]

ok, I am with you now, let's take a stab at this

given this structure

id ¦ username ¦ realname ¦ contact ¦ pass ¦ location ¦ website ¦ age ¦ colours

this select query

$query1 = "Select id, username, realname, contact, website, location, age, colors FROM users Where id = '$id'";

and using a colon to seperate colors
and the data in the column looking like so

blue:green:yellow:red

$query1 = "Select id, username, realname, contact, website, location, age, colors FROM users Where id = '$id'";
$q = mysql_query($query1);
$row = mysql_fetch_array($q);

$selcols = explode(':',$row['colors']);

foreach($selcols as $onecol) {
echo "<p>",$onecol;
}

make sense?

[httpwebwitch]

If you feel like keeping a separate table with the item names in them, I'd recommend it. Each item can have a unique (autoincrementing!) ID, and those IDs can be used to construct your checkboxes, and then you store the item IDs instead of the colour names. It's a strategy that will serve you well with larger, more complex data structures.

Remember, another way to store a bunch of binary (yes/no) data in a single record is to change it into a binary number! Map your items to binary digits, thus:

red=1
blue=2
green=4
orange=8
violet=16

etc.

Then you can store the choices as a sum of the parts. For instance, 13 is 8+4+1, so 13 means a choice of orange, green, and red. 14 is orange, green and blue. 4 is just green. 5 is green and red. You get the idea. You are probably familiar with this because it's the most common way to store file permission settings.

I rarely do that. When I want to store a list of items or selections, I put them in the database as a comma-delimited string.

The advantage of having your items in another table is that you can store the choices as "3,4,8,12,55" instead
of "orange,chartreuse,indigo,brown,limegreen". You're less likely to exceed the maximum alloted string length.

[g0dfaTHer]

yes the code make sense there. so in my data base instead of listing out all the colors separatly... i can name a field "colors". that answers that question about the data base now how about the code from the actual registration page. how does this look.

$ecode = $_POST['code'];
$ec = $_POST['prob'];
if (isset($ec)){
echo "Selected Items:&lt,br/&gt,",

foreach ($ec as $value) {

$query="insert into colors values('$ecode','$value')";

$result=mysql_query($query) or die ("query not made");

echo"{$value}&lt,br/&gt;";
}
print("entry successfully made....!")
}
else {
echo "User did not select any item.";
}
?&gt;

i hope this helps to make sense of everthing that i am trying to explain. THanks alot for this help

[g0dfaTHer]

webwitch thank you....

so if i do this your way, than my query would be different from the above right, if that is the case how about the code from the registration above...

thanks for all this imput

[g0dfaTHer]

jatar_k i sent you a sticky email.......

webwitch if i do it your way than do i follow the same code as i have written above that is on the registration page...

getting an error on line 103 a parse error and line 103 is the end of the page

[jatar_k]

look for mismatched braces or a missing closing brace

[g0dfaTHer]

well it has to be somewhere in here but I dont see any non closed or mismatched. Im going to just put the code that I have in here. The subject isnt colors its Games.....

I was using colors because I had some other sample code that was with colors and thought that if I stayed on the same page as that I would get it. So maybe when I translate it over to what I have Im not setting it right.

Did you happen to scroll up and take a look at what I have for the registration page it self. Thanks again. I hope to get this soon.

$sqlc2 = mysql_connect($host,$user,$sqlpass) or die("Unable to connect to host");

mysql_select_db($db) or die("Unable to select dB");

$query1 = "SELECT id, username, realname, contact, website, location, age, Games FROM users WHERE id = '$id'";

$q = mysql_query($query1);
$row = mysql_fetch_array($q);

$setcols = explode(':',$row['games']);

foreach($selgams as $onegam) {
echo "<p>",$onegam;
$result = mysql_query($query1) or die("Unable to execute query");

$row = mysql_fetch_object($result);
mysql_close($sqlc2);

?>

[g0dfaTHer]

another question to make sure I have the DB correct.

the field GAMES should be what doing it this way..

GAMES = text?
GAmes - varchar (30)

[ikbenhet1]

hi, i think you forgot a bracket just like Jatar is saying.

foreach($selgams as $onegam) {
echo "<p>",$onegam;
$result = mysql_query($query1) or die("Unable to execute query");
$row = mysql_fetch_object($result);

} //you forgot this one , the closing bracket at the end of your code for the foreach

and i don't understand why don't you use this in stead of foreach, but if it works it works:
$col1=$selgams[0];
$col2=$selgams[1];
$col3=$selgams[2];
$col4=$selgams[3];

[g0dfaTHer]

Ok i see that now....

error now is :

Page displays but at the top i get this error,,,,

Warning: Invalid argument supplied for foreach() in /home/****xx/xxxxxxx/xxxx/xxxxx/user.php on line 14

Line 14 is the foreach line

[g0dfaTHer]

Ok i think i need to start over...... ok registration page has this .....

*username:
real name:
*e-mail address:
*confirm e-mail:
*password:
*confirm password:
*age:
*Location: (eg: City, State)
Website :

DB: table users is this:

*NULL*

id smallint(5) UNSIGNED No auto_increment Change

username text No Change Drop

realname text No Change Drop

contact varchar(255) Yes NULL Change Drop

pass text No Change Drop

location text No Change Drop

website text No Change Drop

age text No Change Drop

Games varchar(50) No
//// I dont know if i have the GAMES set up right as varchar"
//// Before the games and the check boxes were added everything worked fine. GAMES is a new field I added recently because i used to have field names with all the games name

Now the code for the registration page:

break;
case 'register':

$ecode = $_POST['code'];
$ec = $_POST['prob'];
if (isset($ec)) {
echo "Selected Items:&lt;br /&gt;";

foreach ($ec as $value) {

$query = "insert into colors values('$ecode','$value')";
$result = mysql_query($query) or die ("query not made");
echo "{$value}&lt;br /&gt;";
}
print("entry successfully made....!");
}
else {
echo "User did not select any item.";

///other part of the code where it sends it to the user.php

$query = "INSERT INTO users (username, realname, contact, pass, website, location, age, Games) VALUES ('$username', '$realname', '$email', '$pass', '$website', '$location', '$age', '$Games')";

////Code from Users where the info should be displayed. Everything displays escept for what games were checked.....

$query1 = "SELECT id, username, realname, contact, website, location, age, Games FROM users WHERE id = '$id'";
$q = mysql_query($query1);
$row = mysql_fetch_array($q);

$setcols = explode(':',$row['games']);

foreach($selgams as $onegam) {
echo "<p>",$onegam;
}
$result = mysql_query($query1) or die("Unable to execute query");

$row = mysql_fetch_object($result);
mysql_close($sqlc2);

?>

And this is where I am stuck.... The other info was easy but i dont think i have the DB set correctly to take the info of the checkboxes and whatever was checked be displayed on the other page. That is where I am stuck. Does anyone see any way to display this from both pages...

Im sorry to be buggin, I know I have sat at this computer to long..... Thanks again