Link to home
Start Free TrialLog in
Avatar of wiggy353
wiggy353

asked on

Using PHP and MySQL, how can I insert multiple records based on check boxes?

I have a form in php that has users insert information for an upcoming event, say date, time, and city. Currently, the city option is a radio button with 5 possible selections. The code works fine and inserts the record with the selected city. Now, however, I need to change the radio button to a check box (easy enough in the form) but then I don't want to enter the multiple selections into the one 'city' field. This would make it difficult to select records that match each city. What I would like to do is create a record for each city in the database. So instead of the entry looking like this:

date           time            city
July 22       noon           Tucson, Phoenix

I would get this:

date          time            city
July 22       noon          Tucson
July 22       noon          Phoenix

I'm thinking like a for each loop or something, but can't figure it out.
Avatar of hielo
hielo
Flag of Wallis and Futuna image

>> I'm thinking like a for each loop or something, but can't figure it out.
Yes.  You need to add brackets to the name of the checkbox --ex:
<input name="city[]" value="1"/>Tucson
<input name="city[]" value="2"/>Phoenix

Then on the server $_POST['city'] IS an array of the selected cities (assuming you selected at least one).
if( array_key_exists('city',$_POST) )
{
   //connect to the db first.  
   $link = mysqli_connect(...);

   $sql = 'INSERT INTO TableName(`city`) VALUES ';
   foreach( $_POST['city'] as $k=>$value)
   {
       $sql .= sprintf('(%s),', mysqli_real_escape_string($link, $value) );
   }
   $sql = trim($sql, ',');
   echo $sql;//show show you the INSERT query
}

Open in new window

Avatar of wiggy353
wiggy353

ASKER

That makes sense. So if I want to insert all 3 fields for each record, I would do something like this?:

if( array_key_exists('city',$_POST) )
{
   //connect to the db first.  
   $link = mysqli_connect(...);

   $sql = 'INSERT INTO TableName('date','time','city') VALUES ';
   $date = $_POST['date'];
   $time = $_POST['time'];
   foreach( $_POST['city'] as $k=>$value)
   {
       $sql .= sprintf('(%s),', mysqli_real_escape_string($link, $date, $time, $value) );
   }
   $sql = trim($sql, ',');
   echo $sql;//show show you the INSERT query
}

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of hielo
hielo
Flag of Wallis and Futuna image

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
Perfect, thanks!