KABOOM
asked on
Multiple checkbox inserts into mysql database
I have a series of checkboxes as my form as follows (signup.php):
<INPUT TYPE="CHECKBOX" NAME="CRegion" Value="Banff">Banff
<INPUT TYPE="CHECKBOX" NAME="CRegion" Value="Calgary">Calgary
<INPUT TYPE="CHECKBOX" NAME="CRegion" Value="Edmonton">Edmonton
Now in my (signupverify.php) I want to do an insert into my database. I know that each of the checkboxes are named the same and I realize this might be an issue. Further more, here's my database structure and if possible I would like it to look as follows (if possible):
TABLE: clients
ID UserName Region
1 myuen Calgary
2 myuen Banff
3 puffy Calgary
4 puffy Banff
5 puffy Edmonton
It's been suggested that I make multiple Region fields but the problem is, if a person is only in charge of one region. The rest of the fields are NULL and that's precisely what I want to avoid whenever possible.
Any ideas?
Thanks,
KABOOM
<INPUT TYPE="CHECKBOX" NAME="CRegion" Value="Banff">Banff
<INPUT TYPE="CHECKBOX" NAME="CRegion" Value="Calgary">Calgary
<INPUT TYPE="CHECKBOX" NAME="CRegion" Value="Edmonton">Edmonton
Now in my (signupverify.php) I want to do an insert into my database. I know that each of the checkboxes are named the same and I realize this might be an issue. Further more, here's my database structure and if possible I would like it to look as follows (if possible):
TABLE: clients
ID UserName Region
1 myuen Calgary
2 myuen Banff
3 puffy Calgary
4 puffy Banff
5 puffy Edmonton
It's been suggested that I make multiple Region fields but the problem is, if a person is only in charge of one region. The rest of the fields are NULL and that's precisely what I want to avoid whenever possible.
Any ideas?
Thanks,
KABOOM
Slight error there, try:
$query = "insert into clients values";
for($i = 0; $i < sizeof($CRegion); $i++){
$query .= " (null, " . $username . ", " . $CRegion[$i] . ")";
if(($i + 1) < sizeof($CRegion)){
$query .= ",";
}
}
Ant
$query = "insert into clients values";
for($i = 0; $i < sizeof($CRegion); $i++){
$query .= " (null, " . $username . ", " . $CRegion[$i] . ")";
if(($i + 1) < sizeof($CRegion)){
$query .= ",";
}
}
Ant
To be honest you would actually have three tables - but that is just to do with database design.
:o)
Ant
:o)
Ant
hi try this:
<INPUT TYPE="CHECKBOX" NAME="CRegion[]" Value="Banff">Banff
<INPUT TYPE="CHECKBOX" NAME="CRegion[]" Value="Calgary">Calgary
<INPUT TYPE="CHECKBOX" NAME="CRegion[]" Value="Edmonton">Edmonton
then in your accepting page:
<?php
$regions = "";
for($i=0;$i<sizeof($CRegio n);$i++)
{
if($regions=="")
$regions = $CRegion[$i];
else
$regions = $regions.",".$CRegion[$i];
}
$query = "INSERT INTO clients VALUES (null,'$username','$region s')";
What I am suggesting here is to put the regions as a comma seperated string into a single field in the database.
<INPUT TYPE="CHECKBOX" NAME="CRegion[]" Value="Banff">Banff
<INPUT TYPE="CHECKBOX" NAME="CRegion[]" Value="Calgary">Calgary
<INPUT TYPE="CHECKBOX" NAME="CRegion[]" Value="Edmonton">Edmonton
then in your accepting page:
<?php
$regions = "";
for($i=0;$i<sizeof($CRegio
{
if($regions=="")
$regions = $CRegion[$i];
else
$regions = $regions.",".$CRegion[$i];
}
$query = "INSERT INTO clients VALUES (null,'$username','$region
What I am suggesting here is to put the regions as a comma seperated string into a single field in the database.
ASKER
Question again from a beginner!
Here's what i've got so far. However, it's still inserting the first city over and over again. I know it has to do with they way the CRegion is set up but how do I fix that?
$insertquery = "INSERT INTO special (CUserName, CPassword, CEmail, CFirstName, CLastName, CRegion) VALUES ('$User', '$Password', '$Email', '$FirstName', '$LastName', '$CRegion')";
for($j=0; $j<sizeof($CRegion); $j++)
{
$insertresult = mysql_query($insertquery);
}
Thanks,
KABOOM
Here's what i've got so far. However, it's still inserting the first city over and over again. I know it has to do with they way the CRegion is set up but how do I fix that?
$insertquery = "INSERT INTO special (CUserName, CPassword, CEmail, CFirstName, CLastName, CRegion) VALUES ('$User', '$Password', '$Email', '$FirstName', '$LastName', '$CRegion')";
for($j=0; $j<sizeof($CRegion); $j++)
{
$insertresult = mysql_query($insertquery);
}
Thanks,
KABOOM
ykf200, that is VERY poor relational database design - a field should have only one value...
:o\
Ant
:o\
Ant
What is wrong with the code I gave you earlier?!?!?!
Did you actually read my comment??!?!
Here it is again, adjusted with the last question you asked:
$insertquery = "INSERT INTO special (CUserName, CPassword, CEmail, CFirstName, CLastName, CRegion) VALUES";
for($i = 0; $i < sizeof($CRegion); $i++){
$query .= " ('$User', '$Password', '$Email', '$FirstName', '$LastName', '" . $CRegion[$i] . "')";
if(($i + 1) < sizeof($CRegion)){
$query .= ",";
}
}
Ant
Did you actually read my comment??!?!
Here it is again, adjusted with the last question you asked:
$insertquery = "INSERT INTO special (CUserName, CPassword, CEmail, CFirstName, CLastName, CRegion) VALUES";
for($i = 0; $i < sizeof($CRegion); $i++){
$query .= " ('$User', '$Password', '$Email', '$FirstName', '$LastName', '" . $CRegion[$i] . "')";
if(($i + 1) < sizeof($CRegion)){
$query .= ",";
}
}
Ant
And you won't want to do it like that anyway - you are going to have lots of duplicates of each username etc.
I suggest you read up on basic relational database design - you are going to cause yourself some REAL headaches otherwise.
:o)
Ant
I suggest you read up on basic relational database design - you are going to cause yourself some REAL headaches otherwise.
:o)
Ant
Finally, to execute the insert query, you can then use:
$insertresult = mysql_query($insertquery);
which only needs to run the once.
:o)
Ant
$insertresult = mysql_query($insertquery);
which only needs to run the once.
:o)
Ant
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You must have an older version of MySQL.
Works perfectly fine in version 3.22.32
:o)
Ant
Works perfectly fine in version 3.22.32
:o)
Ant
It is always much more efficient to keep the number of calls to the database as small as possible.
:o)
Ant
:o)
Ant
I'm surprised you selected that answer..... it is very inefficient...
:o\
Ant
:o\
Ant
thanks Kaboom.
Ant, I guess Kaboom is using an older version of MySQL too
:)
Snigdha
Ant, I guess Kaboom is using an older version of MySQL too
:)
Snigdha
NAME="CRegion[]"
The [] will allow PHP to make an array for you. Then in your code use something like:
$query = "insert into clients values";
for($i = 0; $i < sizeof($CRegion); $i++){
$query .= " (null, " . $username . ", " . $CRegion[$i] . ")";
}
Then you can pass the query string to the database as usual.
:o)
Ant