How to insert data in PHP MySQL form data which has multiple selects with multiple='multiple' enabled

Question

I have a form with POST method with two select dropdown. In both select multiple selections can be made. Now I am using foreach loop insert data in MySQL. But it only works with single select dropdown not with 2nd select dropdown. I used foreach(){ foreach(); } loop with again it doesn't work. How to insert data with multiple select dropdown which has multiple='multiple' enabled.

This is my select dropdown in same FORM

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

foreach ($work_location as $work) {
    foreach ($interest as $int) {
        $insert_oth_data = "INSERT INTO resume_spec_data(work_location,interest) 
        VALUES($work, $int)";
        mysqli_query($con, $insert_oth_data);
    }
}

Also, I don't want to use implode function as it will make data extraction difficult as table grows. I want to store all select options in separate row of MySQL database.

**Warning:** You are wide open to [SQL Injections](https://stackoverflow.com/a/60496/1839439) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) — Dharman, Aug 20 '20 at 12:31
What do you mean it only works with a single dropdown? What happens right now? Are you getting errors? — Dharman, Aug 20 '20 at 12:34
I am not getting any error just the data is not getting inserted into database. Working with single means if I use only one drop-down select not both. So I want a code to insert both select drop-down values when multiple is enabled — Ayush Mayank, Aug 20 '20 at 13:26
Do you have error reporting switched on? [How to get the error message in MySQLi?](https://stackoverflow.com/a/22662582/1839439) — Dharman, Aug 20 '20 at 13:42
Yes, every error reporting is enabled. Previously when I was trying to insert data without foreach loop array to string conversion error was showing. Now after applying foreach no eror is showing but data is also not getting inserted — Ayush Mayank, Aug 20 '20 at 14:00
Then make sure that your `foreach` loops execute at least once. Are you sure there is data in these two arrays? `var_export($work_location)` and `var_export($interest)` — Dharman, Aug 20 '20 at 14:02
Yess when I select only one options from drop down ```array ( 0 => 'Andhra Pradesh', )array ( 0 => 'Software development', )``` this results are showing — Ayush Mayank, Aug 20 '20 at 14:12
Have you implemented prepared statements? Can you show the updated code. See my first comment, it looks like you haven't fixed the SQL injection yet. — Dharman, Aug 20 '20 at 14:13
When I select multiple options in both work_location and interest I am getting ```array ( 0 => 'Delhi', 1 => 'NCR', )array ( 0 => 'Software development', 1 => 'Web development', )``` — Ayush Mayank, Aug 20 '20 at 14:14
Also I haven't applied prepared statement yet. First I want to build the logic to insert data correctly then I will rectify the sql injection code — Ayush Mayank, Aug 20 '20 at 14:18
That is the whole point. To insert data correctly you need to use prepared statements. Your current method with `mysqli_query` will not work! See https://stackoverflow.com/questions/7537377/how-to-include-a-php-variable-inside-a-mysql-statement — Dharman, Aug 20 '20 at 14:19
OK OK Can you please suggest me the code to insert data using foreach loop with prepared statement. I will try to implement it — Ayush Mayank, Aug 20 '20 at 14:22
For example here: https://stackoverflow.com/a/63318861/1839439 — Dharman, Aug 20 '20 at 14:23
I tried to use your suggested code for prepared statement which is something like this ```$work_location = $_POST['work_location']; $interest = $_POST['interest']; $mysqli->begin_transaction(); $insertStatement = $mysqli->prepare('INSERT INTO resume_spec_data(work_location, interest) VALUES(?,?)'); $insertStatement->bind_param($work, $int); foreach ($work_location as $work) { $insertStatement->execute(); } foreach ($interest as $int) { $insertStatement->execute(); } $mysqli->commit();``` but got error Notice : Undefined variable: mysqli in — Ayush Mayank, Aug 20 '20 at 14:34

score -1 · Answer 1 · edited Aug 20 '20 at 12:30

-1

Use a foreach loop per dropdown

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

After this add the insert into your db.

edited Aug 20 '20 at 12:30

Dharman

30,962
25
85
135

answered Aug 20 '20 at 12:18

Taariq Solomons

87
6

I applied your method ```foreach ($work_location as $value) { $value = $work; } foreach ($interest as $value) { $value = $int; } $insert_oth_data = "INSERT INTO resume_spec_data(cid, work_location, interest) VALUES($cid, $work, $int)"; mysqli_query($con, $insert_oth_data);``` But I get error Notice: Undefined variable: work in... Notice: Undefined variable: int in.... – Ayush Mayank Aug 20 '20 at 14:08

How to insert data in PHP MySQL form data which has multiple selects with multiple='multiple' enabled

1 Answers1