How to insert multiple select option in PHP MySQL when multiple=’multiple’ is enabled

I have a FORM with method POST. In the form I have two select drop-down with multiple=’multiple’ enabled in both so that user can make multiple selections.

Now I am using MySQL database to store data.

I am using foreach loop to iterate all the selections made.

Here is the image Select drop-down image

My code

$cid = $row['cid'];
$work_location = $_POST['work_location'];
$interest = $_POST['interest'];

foreach ($work_location as $value) {
    $work = $value;
}

foreach ($interest as $value) {
    $int = $value;
}

$insert_oth_data = "INSERT INTO resume_spec_data
                            (cid, work_location, interest) 
                    VALUES('$cid', '$work', '$int')";
mysqli_query($con, $insert_oth_data);

echo var_export($work_location);
echo var_export($interest);
echo var_export($work);
echo var_export($int);

echo var_export( $insert_oth_data );

Here work_location and interest are the fields that will carry the multiple select values. I don’t want to use implode function because it will make data extraction difficult as table grows. I want to store the values in saperate rows of database.

The problem I am facing is that data is being inserted, but the problem is when I select multiple options only one data is being inserted not all.

The result I get after inserting the data is:

array ( 0 => 'Delhi', 1 => 'NCR', )array ( 0 => 'Software development', 1 => 'Business analyst', 2 => 'Web development', )'NCR''Web development''INSERT INTO resume_spec_data(cid, work_location, interest) VALUES('1', 'NCR', 'Web development')'

As you can compare from the image I have made multiple selections. But single data is being taken here

For reference

$work_location contains array ( 0 => 'Delhi', 1 => 'NCR', )

$interest contains 

array ( 0 => 'Software development', 
        1 => 'Business analyst', 
        2 => 'Web development', 
)

$work contains NCR

$int contains Web development only

$work and $int should also contains same as $work_location and $interest respectively.

One thing that I found here is that I have applied Insert query outside the foreach loop maybe that is why only one value is being taken. Since I am using multiple select drop-down here with multiple=’multiple’ enabled, I don’t know how to do this to achieve the result I want.

Any help would be appreciated.

Answer

Your INSERT command happens once, and it occurs after your loops have all finished. So in the loops you’re pointlessly re-assigning new values to the same variable, and then discarding them again without using them. Obviously if you wait until after a loop has finished, then you can’t use the values which are present when the loop is running. You’ll only ever see the last one which was assigned in the last run of the loop.

And you’ve explained that you’re struggling to fix this because you don’t know how to include the insert query inside the loops, because there are 2 different foreach loops.

---

So really I think the root cause here is that your database structure is flawed. It seems you permit different numbers of values in your form (e.g. 2 entries for work locations, then 3 entries for domains of interest, per your screenshot), and there’s no particular relationship between them (e.g. does “Delhi” belong with “software development, or “business analyst”, or the other one? There’s no logic to explain that).

And yet, you’re trying to store them in a table which inherently relates them together by storing them next to each other in rows. This will result in poor data quality because a) there’s no way to decide which belongs with which, and b) if you have different numbers of entries in each field then you’ll end up with gaps.

You need separate tables to store each list – one table for locations, and one table for interests, both with the Cid to link them to the master record.

This will then make it easy to use the data in you loops e.g.

foreach ($work_location as $value) {
    $work = $value;
    //...write query code to insert into work locations table
}

foreach ($interest as $value) {
    $int = $value;
    //...write query code to insert into interests table
}