PHP Batch Insertion from Array Tutorial

Submitted by oretnom23 on Tuesday, April 5, 2022 - 17:10.

In this tutorial, we will tackle Inserting the Data from Array in MySQL Database using PHP. Here, I will show you a technique to easily insert multiple data rows into the database. This technique or idea can be helpful for mass data insertion of a form for some feature of a PHP Projects such as POS or Cashiering System, Shopping Cart, and more.

For this tutorial, we will be creating a simple web application that consists of a feature that we want to achieve on this tutorial. The application will have a simple database table structure where our data will be inserted. Here, I will be using Bootstrap v5 for the design of the user interface and jQuery Library for the UX of the Form that we will use. The application has a single page that lists all the inserted data and has a form modal.

Getting Started

Download the following to your local machine:

The latest version of XAMPP
Bootstrap v5
jQuery Library

Install the XAMPP into your local machine. After that, open the XAMPP's Control Panel and start the Apache and MySQL Server.

Compile the Bootstrap and jQuery files into your source code directory. Make sure to put your source code directory inside the XAMPP's htdocs directory otherwise, you will make some configuration.

Open your preferred browser and browse the XAMPP's PHPMyAdmin by browsing http://localhost/phpmyadmin.

Creating the Database

Create a new database naming dummy_db. After that, navigate the page into the SQL tab of your newly created database. Then, paste the SQL Script below into the provided text field and click the Go button below to execute the SQL Script.

CREATE TABLE `member_list` (
  `id` int(30) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `first_name` varchar(250) NOT NULL,
  `middle_name` varchar(250) NOT NULL,
  `last_name` varchar(250) NOT NULL,
  `contact` varchar(100) NOT NULL,
  `address` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Creating the Database Connection File

Open your preferred text editor such as notepadd++ or sublime text. Create a new PHP File naming db-connect.php. Save the file inside your source code directory's root path. Then, copy/paste the script below.

 
<?php
$host ="localhost";
$username ="root";
$pw ="";
$db ="dummy_db";
 
$conn = new mysqli($host, $username, $pw, $db);
if(!$conn){
    die("Database Connection Failed.");
}

Creating the Page Interface

Next, create a new PHP File naming index.php and save it into your source code directory's root path. This file contains the HTML Script and PHP Scripts that displays all the inserted data into a table and a membership form inside a popup window or modal. Copy the script below and make sure to replace the external CSS and JS paths according to the location of each file on your end.

 
<!--
This is a simple Web App. This was developed for educational purposes only. 
Author: oretnom23
Email: [email protected] 
 --> 
<?php 
session_start();
// Require/Include DB Connection
require_once('./db-connect.php');
?><!DOCTYPE html>
<html lang="en">
 
<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>PHP Batch Insertion</title>
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.1.1/css/all.min.css" integrity="sha512-KfkfwYDsLkIlwQp6LFnl8zNdLGxu9YAA1QvwINks4PhcElQSvqcyVLLD9aMhXd13uQjoXtEKNosOWaZqXgel0g==" crossorigin="anonymous" referrerpolicy="no-referrer" />
    <link rel="stylesheet" href="./css/bootstrap.min.css">
    <link rel="stylesheet" href="./css/styles.css">
    <script src="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.1.1/js/all.min.js" integrity="sha512-6PM0qYu5KExuNcKt5bURAoT6KCThUmHRewN3zUFNaoI6Di7XJPTMoT6K0nsagZKk2OB4L7E3q1uQKHNHd4stIQ==" crossorigin="anonymous" referrerpolicy="no-referrer"></script>
    <script src="./js/jquery-3.6.0.min.js"></script>
    <script src="./js/bootstrap.min.js"></script>
    <script src="./js/script.js"></script>
 
</head>
 
<body class="bg-gradient bg-dark bg-opacity-50">
    <script>
        start_loader()
    </script>
    <main>
        <div class="col-lg-12">
            <h1 class="fw-bolder text-center" id="project-title">PHP Batch Insertion</h1>
        </div>
        <div class="container w-100">
            <?php if(isset($_SESSION['error'])): ?>
                <div class="alert alert-danger rounded-0">
                    <p><?= $_SESSION['error'] ?></p>
                </div>
            <?php unset($_SESSION['error']) ?>
            <?php endif; ?>
            <div class="text-end mb-3">
                <button class="btn btn-primary btn-sm bg-gradient rounded-0" type="button"  data-bs-toggle="modal" data-bs-target="#MemberFormModal"><i class="fa fa-plus"></i> Add Member(s)</button>
            </div>
            <div class="row">
                <div class="col-lg-12 col-md-12 col-sm-12 col-xs-12">
                    <div class="card rounded-0 shadow">
                        <div class="card-body">
                            <div class="w-100 overflow-auto">
                                <table class="table table-stripped table-bordered" id="item-list">
                                    <colgroup>
                                        <col width="10%">
                                        <col width="50%">
                                        <col width="20%">
                                        <col width="20%">
                                    </colgroup>
                                    <thead>
                                        <tr class="bg-primary bg-gradient text-light">
                                            <th class="text-center">#</th>
                                            <th>Name</th>
                                            <th>Contact</th>
                                            <th>Address</th>
                                        </tr>
                                    </thead>
                                    <tbody>
                                        <?php 
                                        $i =1;
                                        $qry = $conn->query("SELECT * FROM `member_list`");
                                        while($row = $qry->fetch_assoc()):
                                        ?>
                                        <tr>
                                            <td class="px-2 py-1 text-center"><?= ($i++)  ?></td>
                                            <td class="px-2 py-1"><?= ucwords($row['last_name']. ", " . $row['first_name'] . " ". $row['middle_name']) ?></td>
                                            <td class="px-2 py-1"><?= $row['contact'] ?></td>
                                            <td class="px-2 py-1"><?= $row['address'] ?></td>
                                        </tr>
                                        <?php endwhile; ?>
                                        <?php $conn->close() ?>
                                    </tbody>
                                </table>
                            </div>
                        </div>
                    </div>
                </div>
            </div>
        </div>
        <div class="modal fade" id="MemberFormModal" data-bs-backdrop="static" data-bs-keyboard="false" tabindex="-1" aria-labelledby="MemberFormModallabel" aria-hidden="true">
            <div class="modal-dialog modal-dialog-centered modal-dialog-scrollable modal-lg">
                <div class="modal-content rounded-0">
                    <div class="modal-header">
                        <h5 class="modal-title" id="MemberFormModallabel">New Member Form</h5>
                        <button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button>
                    </div>
                    <div class="modal-body">
                        <form action="batch_insert.php" method="POST" id="new_member">
                            <div class="border-top border-bottom item py-2">
                                <div class="row">
                                    <div class="col-md-4">
                                        <div class="form-group mb-3">
                                            <label for="first_name" class="control-label">First Name</label>
                                            <input type="text" class="form-control form-control-sm rounded-0" id="first_name" name="first_name[]" required="required">
                                        </div>
                                    </div>
                                    <div class="col-md-4">
                                        <div class="form-group mb-3">
                                            <label for="middle_name" class="control-label">Middle Name</label>
                                            <input type="text" class="form-control form-control-sm rounded-0" id="middle_name" name="middle_name[]">
                                        </div>
                                    </div>
                                    <div class="col-md-4">
                                        <div class="form-group mb-3">
                                            <label for="last_name" class="control-label">Last Name</label>
                                            <input type="text" class="form-control form-control-sm rounded-0" id="last_name" name="last_name[]" required="required">
                                        </div>
                                    </div>
                                    <div class="col-md-4">
                                        <div class="form-group mb-3">
                                            <label for="contact" class="control-label">Contact #</label>
                                            <input type="text" class="form-control form-control-sm rounded-0" id="contact" name="contact[]" required="required">
                                        </div>
                                    </div>
                                    <div class="col-md-4">
                                        <div class="form-group mb-3">
                                            <label for="address" class="control-label">Address</label>
                                            <textarea rows="2" class="form-control form-control-sm rounded-0" id="address" name="address[]" required="required"></textarea>
                                        </div>
                                    </div>
                                    <div class="col-md-4">
                                        <div class="form-group mb-3">
                                            <button tabindex="-1" class="btn btn-outline-danger btn-sm rounded-0 mt-4 rem-btn" onclick="rem_item($(this))" type="button"><i class="fa fa-times"></i> Remove</button>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </form>
                        <div class="text-center mt-3">
                            <button class="btn btn-primary rounded-0 bg-gradient" type="button" id="add_item"><i class="fa fa-plus"></i> Add Item</button>
                        </div>
                    </div>
                    <div class="modal-footer">
                        <button type="submit" class="btn btn-primary btn-sm rounded-0" form="new_member">Save Item(s)</button>
                        <button type="button" class="btn btn-secondary btn-sm rounded-0" data-bs-dismiss="modal">Close</button>
                    </div>
                </div>
            </div>
        </div>
    </main>
</body>
</html>

Creating the Custom CSS

The below script is the custom Cascading Style Sheet script that designs or redesign some of the elements in our user interface. Save this file as styles.css and make sure to include this file into your index.html file.

 
html,
body {
    height: 100%;
    width: 100%;
}
 
main {
    padding: 1em 0;
}
 
main * {
    font-family: Comic Sans MS;
}
 
#project-title {
    text-shadow: 3px 3px 7px #000;
    padding: 2.5em 1em !important;
    color: white;
    font-size: 3em;
    padding-bottom: 0.5em !important;
}
 
 
/* Loader */
 
#pre-loader {
    position: absolute;
    width: 100%;
    height: 100%;
    top: 0;
    left: 0;
    backdrop-filter: brightness(.5);
    display: flex;
    align-items: center;
    justify-content: center;
    z-index: 99;
}
 
.lds-hourglass {
    display: inline-block;
    position: relative;
    width: 80px;
    height: 80px;
}
 
.lds-hourglass:after {
    content: " ";
    display: block;
    border-radius: 50%;
    width: 0;
    height: 0;
    margin: 8px;
    box-sizing: border-box;
    border: 32px solid #fff;
    border-color: #fff transparent #fff transparent;
    animation: lds-hourglass 1.2s infinite;
}
 
@keyframes lds-hourglass {
    0% {
        transform: rotate(0);
        animation-timing-function: cubic-bezier(0.55, 0.055, 0.675, 0.19);
    }
    50% {
        transform: rotate(900deg);
        animation-timing-function: cubic-bezier(0.215, 0.61, 0.355, 1);
    }
    100% {
        transform: rotate(1800deg);
    }
}

Creating the Custom JS

The below script is the custom JavaScript of out application which holds the codes or scripts the event listeners and function for our form's functionalities. The scripts will allows the user to add new item in the form and removing item from the form. Save this file as script.js and make sure to include the file into your index.php file also.

// Custom Loader Element Node
var loader = document.createElement('div')
loader.setAttribute('id', 'pre-loader');
loader.innerHTML = "<div class='lds-hourglass'></div>";
 
// Loader Start Function
window.start_loader = function() {
    if (!document.getElementById('pre-loader') || (!!document.getElementById('pre-loader') && document.getElementById('pre-loader').length <= 0))
        document.querySelector('body').appendChild(loader)
}
 
// Loader Stop Function
window.end_loader = function() {
    if (!!document.getElementById('pre-loader')) {
        setTimeout(() => {
            document.getElementById('pre-loader').remove()
        }, 500)
    }
}
 
function rem_item(_this) {
    if ($('form#new_member .item').length > 1) {
        _this.closest('.item').remove()
    } else {
        $('form#new_member')[0].reset()
    }
}
$(function() {
    end_loader()
    $('#add_item').click(function() {
        var item = $('form#new_member .item').first().clone()
        item.find('input, textarea').val('')
        $('form#new_member').append(item)
    })
})

Creating the Main Script

Lastly, we will create a new PHP File naming batch_insert.php and save into your source code directory. This file contains the php script for inserting the array data from our form. The file also contains a simple script on catching the errors if any will occurred.

 
<?php 
session_start();
// Require/Include DB Connection
require_once('./db-connect.php');
 
// Data to Insert
$data = "";
 
// Extract POST Data
extract($_POST);
 
// Loop the Array Data
foreach($first_name as $k => $v){
    if(!empty($data)) $data .= ", ";
    $data .= "('{$v}', '{$middle_name[$k]}', '{$last_name[$k]}', '{$contact[$k]}', '{$address[$k]}')";
}
 
if(empty($data)){
    echo '<script> alert("New Data has been sent."); </script>';
}else{
    $sql = "INSERT INTO `member_list` (`first_name`, `middle_name`, `last_name`, `contact`, `address`) VALUES {$data} ";
    $insert_batch = $conn->query($sql);
    if($insert_batch){
        echo '<script> alert("New Member(s) has been saved successfully."); </script>';
    }else{
        echo '<script> alert("Data has failed to save."); </script>';
        $_SESSION['error'] = "Query Error: ". $conn->error .". <br />".$sql;
    }
}
$conn->close();
header("Location: ./");

That's it! You can now check the application and see if it works as we planned and if it achieves our goal of this tutorial. You can test the application by browsing http://localhost/source_code_directory_name/ in to your preferred browser.

That's the end of this tutorial. If there's an error occurred on your end, please review the changes in your codes and try to differentiate them from the source code I provided above. You can also download the working source code I created for this tutorial. The download button is located below this article.

I hope this tutorial will help you with what you are looking for and you'll find this useful for your future PHP Projects.

Explore more on this website for more Tutorials and Free Source Codes.

Add new comment

1337 views