Link to home
Start Free TrialLog in
Avatar of KABOOM
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
Avatar of a.marsh
a.marsh

First of all use:

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
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
To be honest you would actually have three tables - but that is just to do with database design.

: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($CRegion);$i++)
   {
        if($regions=="")
            $regions = $CRegion[$i];
        else
            $regions = $regions.",".$CRegion[$i];
   }

   $query = "INSERT INTO clients VALUES (null,'$username','$regions')";

What I am suggesting here is to put the regions as a comma seperated string into a single field in the database.
Avatar of KABOOM

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
ykf200, that is VERY poor relational database design - a field should have only one value...

: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
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
Finally, to execute the insert query, you can then use:

$insertresult = mysql_query($insertquery);

which only needs to run the once.

:o)

Ant
ASKER CERTIFIED SOLUTION
Avatar of Snigdha
Snigdha

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
You must have an older version of MySQL.

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
I'm surprised you selected that answer..... it is very inefficient...

:o\

Ant
thanks Kaboom.

Ant, I guess Kaboom is using an older version of MySQL too

:)

Snigdha