How to Update Multiple Select Box Values in PHP MySQL

how to update multiple select box values in php mysql

<form method='POST' action='somepage.php'>
    <select name="specialization[]" multiple>
        <option value=""></option>
        <option value="United States" selected>United States</option>
        <option value="United Kingdom">United Kingdom</option>
        <option value="Afghanistan" selected>Afghanistan</option>
        <option value="Aland Islands">Aland Islands</option>
        <option value="Albania">Albania</option>
        <option value="Algeria">Algeria</option>
        <option value="American Samoa">American Samoa</option>
        <option value="Andorra">Andorra</option>
    </select>
    <input type='submit' value='submit'>
</form>

somepage.php

<?
$specialization = isset($_POST['specialization']) ? $_POST['specialization'] : [] ;
$totalSpec = sizeof($specialization);
for($i = 0; $i < $totalSpec; $i++){
    $spec = $specialization[$i];
    $query="INSERT INTO TableName SET SpecializationColumnName='$spec'";
}?>

OR

<?
$specialization = isset($_POST['specialization']) ? implode(",",$_POST['specialization']) : '';
$query = "";
if(!empty($specialization)){
   $query = "INSERT INTO TableName SET SpecializationColumnName='$specialization'";
}?>

Update MySQL with multi select

You have to cahnge 2 things:

1. Your ajax code - you need to fetch all of the user's company IDs.
2. Your JavaScript code - you need to adjust your code for multiple company values.

I've seperated your ajax code into 2 queries. The first fetches the employee's data, and the second fetches his companies.

if (isset($_POST["employee_id"])) { 
    $return = [];
    $query = "SELECT * FROM employee WHERE employee_id = " . (int)$_POST["employee_id"];
    $result = mysqli_query($connect, $query);

    if ($employee = $result->fetch_assoc()) {
        $companyIdList = [];

        $query = "SELECT company_id FROM employee_company WHERE employee_id = $employee[id]";
        $result = mysqli_query($connect, $query);

        while ($company = $result->fetch_assoc()) {
            $companyIdList[] = $company["company_id"];
        }

        $return = array_merge($employee, ["companies" => $companyIdList]);
    }

    echo json_encode($return);
}

Then replace this line:

$('#company').val(data.company);

With this code:

$.each(data.companies, function(i, e) {
    $("#company option[value='" + e + "']").prop("selected", true);
});

Which will iterate through the array of company IDs, and will select the <option>s accordingly.

Additionaly, right after this line:

$(document).on('click', '.edit_data', function(){

Insert this code, in order to reset the selected <option>s:

$("#company option").prop("selected", false);

How to update multiple rows in mysql and php with a different values using variables and different files?

I was able to figure out a solution by doing this:

<?php
$servername = "xxxt";
$username = "xxx";
$password = "xxx";
$dbname = "xxx";
$command = $_POST['command'];
$text = $_POST['text'];
$id = $_POST['id'];
$disable = $_POST['disabled'];
$count = count($id);
for ($x = 0; $x <= $count; $x++) {
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}

$sql = "UPDATE dashboard SET Command='$command[$x]', Text='$text[$x]', Disabled='$disable[$x]' WHERE Id='$id[$x]'";

if ($conn->query($sql) === TRUE) {
    echo "Record updated successfully";
  } else {
    echo "Error updating record: " . $conn->error;
  }

$conn->close();
}
header("Location: /");
?>

So i decided to do a for loop and count how many inputs i have, so for each input, it automatically creates a new query updating it.

update multiple select values in mysql database using codeigniter

First delete the categories and then insert

$options = array(
    'category_Id' => $_POST['product_category_id'][0],
    'product_id'  =>  'product id here'/* first category will be inserted only*/
);

$this->db->query("DELETE FROM product_categories WHERE product_id=". $options['product_id']); /* this will delete all categories assigned to $options['product_id']*/

$this->db->insert('product_categories', $options); /* will insert the first category from $_POST['product_category_id']*/

If you want a multi update then try this

$this->db->query("DELETE FROM product_categories WHERE product_id='product id here'";
foreach($_POST['product_category_id'] as $key => $product_category_id){


$options = array(
    'category_Id' => $product_category_id,
    'product_id'  =>  'product id here' /* first category will be inserted only*/
);    
    $this->db->insert('product_categories', $options);
}

UPDATE an imploded multiple selection into database

There are few issues with your code, such as:

• $tags is not an array. See the below statement in your while() loop,

  $tags = $row['tags'];
  

  So you can't use it in foreach loop like that. Use explode() function to split the string and get the tags in an array, like this:

  $tags = explode(",",$row['tags']);
  

  And then use this $tags array in your form, which is explained below.

• Syntax error here,

  $tags = implode(",",$_POST['tags'];
                                    ^ missing closing )
  

• Even you get the tags as an array(as pointed above), you don't have to use that foreach loop either, it will unnecessarily append additional/redundant tags in your <select> element. better use in_array() function to check the tag value is present in $tags array or not and make it selected accordingly

• value attribute is missing from <option> tags.

• Place the SELECT operation below the UPDATE operation, otherwise you'll get old tag values from the SELECT operation even if you update the tags using the form.

So your code should be like this:

if(isset($_POST['update'])){
    $tags = implode(",",$_POST['tags']);
    $query = "UPDATE data SET tags= '$tags' WHERE id = $id";
    mysqli_query($dbc, $query);
}

$query = "SELECT * FROM data WHERE id = $id";
$edit = mysqli_query($dbc, $query);
while($row = mysqli_fetch_assoc($edit)){
    $tags = explode(",",$row['tags']);
}

<form method="post" action="index.php">
    <select id="tags" name="tags[]" multiple="multiple">
        <option value="tag1"<?php if(in_array('tag1', $tags)){ echo ' selected="selected"'; } ?>>tag1</option>
        <option value="tag2"<?php if(in_array('tag2', $tags)){ echo ' selected="selected"'; } ?>>tag2</option>
        <option value="tag3"<?php if(in_array('tag3', $tags)){ echo ' selected="selected"'; } ?>>tag3</option>
        <option value="tag4"<?php if(in_array('tag4', $tags)){ echo ' selected="selected"'; } ?>>tag4</option>
    </select>
    <button type="submit" name="update">Submit</button>
</form>

Updating select tag from database with multiple values

You need to loop through the size array right after explode.

    $arr = $row["item_size"];  
    $exp = explode("," , $arr);

echo "<select name='product_size'>";

foreach($exp as $key=>$val) {
        echo "<option value='" . $val . "'>" . $val . "</option>";
}

echo "</select>";

Working Demo

---

Related Topics