CODEWITHSUNDEEP
phpmysql
user3272462
user3272462

Reputation: 73

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

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)";

Upvotes: 7

Views: 49319

Answers (2)

bart2puck
bart2puck

Reputation: 2522

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.

Upvotes: 0

Armin
Armin

Reputation: 1756

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.

Upvotes: 18

Related Questions