insert data to mysql database from multiple select list (html form)

Question

I make a form, where there is ID of a shop:

<input type="text" name="shopId">

and there is a multiple choice select:

<select name="cars" multiple required>

after i get selected options, i have to pass them to a table in the database; table consists of 2 columns: shopId and car. The thing is it passes only one option and it is impossible to have a few rows added to the table like in one shop two or three models. I suppose i have to pass the data like an array or something. Can you help me, please.

$shopId = $_GET["shopId"]; $cars = $_GET["cars"];

this is a query:

$query = "INSERT INTO shops (shopId, car) VALUES ($shopId, $cars)";

Armin · Accepted Answer · 2014-02-04 21:31:43Z

I'd say given the constraints, the only option you have is to combine all the selected options into a single comma separated string (using PHP's built-in function called implode http://php.net/implode), then insert the shopID and the comma-separated-list of cars into a new row. I'd do it like this:

<?php if ($_POST) { $cars_string = implode(', ', $_POST['cars']); $sql = ' INSERT INTO `my_table` ( `shopID`, `cars` ) VALUES ( '. $_POST['shopID'] .', "'. $cars_string .'" ) '; mysql_query($sql) OR die(mysql_error()); } ?> <form method="post" action=""> Shop ID: <input type="text" name="shopID"/> - <select name="cars[]" multiple="multiple"> <option value="volvo">Volvo</option> <option value="saab">Saab</option> <option value="honda">Honda</option> <option value="audi">Audi</option> <option value="bmw">BMW</option> </select> <input type="submit" name="Submit"/> </form>

This is the best solution given the constraints you've provided. However, I do not see the logic in only being able to add a single row per form submit. That is not a good database design, especially in the long-term.

Please notice how the <select> element has the name of name="cars[]" and pay close attention to the open/close square brackets after the word cars[]. This will allow multiple options to be passed through the form, instead of only one. This is a critical difference and it should not be overlooked, as @bart2puck mentions in his solution. Also, the most browser-friendly way to allow users to select multiple options is to use the attribute multiple="multiple" in your <select> element.

Saty · Accepted Answer · 2015-08-31 08:07:18Z

you are getting only 1 insert because you are setting the value of $_GET['cars'] to the last selected item in your multiple. to acheive what you are looking for set the select name of cars to cars[]. When you goto process this you will now have an array in $_GET data.

then loop through that to do your inserts.

 $shopId = $_GET['shopId']; foreach ($_GET['cars'] as $value) { $ins = "INSERT INSERT INTO shops (shopId, car) VALUES ($shopId, $value)"; }

if you can only have 1 insert, which seems odd, then do something like:

 $cars = ""; foreach ($_GET['cars'] as $value) { $cars .= $value . ","; } $cars = substr($cars,0,-1); //to remove the last comma

then

 $ins = "INSERT INSERT INTO shops (shopId, car) VALUES ($shopId, $cars)";

you are going to end up with a field like 'honda,mazda,toyota' and this doesn't seem very efficient.

He mentions "it is impossible to have a few rows added to the table" so this would not be a valid solution.

Collectives™ on Stack Overflow

insert data to mysql database from multiple select list (html form)

2 Answers 2

Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Linked

Related