How to insert multiple rows into Mysql table using Bootstrap select boxes and PHP prepared statements?

Question

I'm trying to insert multiples rows into MySql, using PHP prepared Statements and Boostrap Select Picker. I tried to research some questions here but unfortunately i couldn't solve this code problem. Below are the questions that i tried to solve my doubt but without success:

Just for resume my problem, i have two MySql tables that call:

(tbl_colors)

Collunm: ColorID - Primary Key - AI
Collunm: color - Varchar - 100

colorID|color
-------------
01     |White
02     |Black
03     |Blue

and (tbl_colors_options)

Collunm: ID - Primary Key - AI
Collunm: colorFK - INT 11 - INDEX - ForeingKey from **(tbl_colors)**

ID |colorFK| 
------------
01 |  1 
02 |  3
03 |  2 
03 |  2 
03 |  3 
03 |  3

After i created that 2 tables above into MySql, i created a page with a dropdown list that display all values from (tbl_colors). I used a Bootstrap Multiple Select Boxes from this website here https://developer.snapappointments.com/bootstrap-select/examples/ to display on page. Into this select box i have this values from (tbl_colors): 1, 2 and 3 where i can select all of them or some of them to insert that values through submit button to (tbl_colors_options).

Below i show the code that i'm using to insert multiple row on (tbl_colors_options):

<!-- Jquery and Bootstrap 4 CDN's -->

<script src="https://code.jquery.com/jquery-3.4.0.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.7/umd/popper.min.js"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js"></script>
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css" />

<!-- Bootstrap Select CSS Box-->
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap-select@1.13.9/dist/css/bootstrap-select.min.css">

<!-- Bootstrap Select JS -->
<script src="https://cdn.jsdelivr.net/npm/bootstrap-select@1.13.9/dist/js/bootstrap-select.min.js"></script>

and HTML and PHP code that display the dropdown list (Bootstrap Multiple Select box):


<div class="container">
<br>
<form method="post" id="option_form" action="add.php">
<div class="form-row">
<div class="col-md-8 mb-3" >

<label><b>Options to select</b></label>


<?php

include 'db.php';
$stmt = $connection->prepare('SELECT * FROM tbl_colors');
$stmt->execute();
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
?>

<select name="colorFK" id="colorFK" class="form-control selectpicker" multiple>
<?php foreach($results as $row): ?>
<option value="<?= $row['colorID']; ?>"><?= $row['colorID']; ?></option>    
<?php endforeach ?>
</select> 


</div>
</div>
<div class="form-row">
<div class="col-md-8 mb-3" align="right">
<input type="submit" name="submitbtn" id="submitbtn" class="btn btn-success" value="Add Options" />
</div>
</div>

</form>

</div>

below is my DB connection PHP file (db.php):

<?php

$username = 'root';
$password = '';
$connection = new PDO( 'mysql:host=localhost;dbname=system;charset=utf8;', $username, $password );

?>

and finally, below is the add.php file that i'm using to insert row into (tbl_colors_options):

<?php

include 'db.php';

if(isset($_POST["submitbtn"]))
{
    if($_POST["submitbtn"])
    {

        $statement = $connection->prepare("
            INSERT INTO tbl_colors_option (colorFK) 
            VALUES (:colorFK)
        ");

        $result = $statement->execute(
            array(
                ':colorFK'  =>  $_POST["colorFK"],
            )
        );

        if(!empty($result))
        {
            echo 'option successfully added!';
        }

}}
?>

The add.php code above works fine and just insert one row into tbl_colors_option. How can i improve the add.php code to insert multiple rows that i selected on Bootstrap Multiple Select Box?

score 0 · Answer 1 · answered Aug 05 '19 at 16:13

I found a solution for this doubt. I modified the add.php file with the PHP PDO code below and now the code below insert multiple records into MySql table based on values selected in Boostrap Multiple Selected Boxes and now works fine:

<?php
    $db_host = "localhost";
    $db_name = "system";
    $db_user = "root";
    $db_pass = "";

    try{
        $db_con = new PDO("mysql:host={$db_host};dbname={$db_name}",$db_user,$db_pass);
        $db_con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch(PDOException $e){
    echo $e->getMessage();
}

try{

if(isset($_POST['submitbtn'])){

    $multi=$_POST['colorFK'];

foreach ($multi as $multir){

$stmt=$db_con->prepare("INSERT INTO tbl_colors_options (colorFK) VALUES(:colorFK)");
$stmt->bindParam(":colorFK", $multir);
$stmt->execute();

}
}
}
    catch(PDOException $e){
    echo $e->getMessage();

}

?>

If someone has some good improve for this code above it will be nice :)

How to insert multiple rows into Mysql table using Bootstrap select boxes and PHP prepared statements?

1 Answers1