How to load MySQL data in Bootstrap Modal Body using AJAX

This article was written when I was doing my minor project back in 2015, but wait I have updated this article with the support of new PHP 7 and MySQLi syntax. You’ll find me using PHP, Bootstrap (not the latest but process is same on new Bootstrap 4 as well), and MySQLi with some flavor of AJAX in this article.  On my project I was building a dashboard to show all data in a table by importing data from MySQL database using PHP, where I also added an Edit button at the end of each row that opens up a Modal box, displaying and allowing the admin to alter information and update the database.

So, in this tutorial I will be showing you the best way of Using Bootstrap Modal with AJAX to import data from MySQL database. Go through this tutorial to learn How to load MySQL data in Bootstrap Modal Body using AJAX.

Skills required:

  1. HTML
  2. PHP Basics
  3. Bootstrap
  4. Ajax

For this tutorial we’ll be creating three php files

conn.php We’ll be using this file to store PHP script to connect to Database.

index.php This file will show all data imported from MySQL database on the HTML table with a button edit to give an option to edit the particular row of data.

editdata.php This file will have the form that is to be shown on the modal while user clicks on edit button on the table.

This will be a step by step guide on How to load MySQL data in Bootstrap Modal Body using AJAX, and also include other extra edits you need to make. If you think you are just looking for code then you can skip tutorial and fork this project on Github to try on your own. Remember, more stars on this GitHub Project motivates me to write more.

Creating simple MySQL database table

Lets begin by creating MySQL table to store member details, and insert some example data onto that table.

Goto your mysql from terminal using mysql -u root -p   and enter password if any.

Create a database member

create database member;

Create table details

Creating bootstrap table to show data from MySQL database

Lets create index.php file to show data on tabular format from MySQL database. As we are using bootstrap classes well also be linking bootstrap.min.css file on our head section.

On your index.php file add this code to show HTML table using bootstrap.

<!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>Using Bootstrap modal</title>
    <!-- Bootstrap Core CSS -->
    <link href="../css/bootstrap.min.css" rel="stylesheet">
</head>
<body>
<div class="container">
    <div class="row">
        <div class="col-lg-12">
            <h1 class="page-header">Members Details</h1>
        </div>
        <!-- /.col-lg-12 -->
    </div>
    <div class="row">
        <div id="member" class="col-lg-12">                            
            <table class="table table-striped table-bordered">
                <thead>
                <tr>
                    <th>Name</th>
                    <th>Job Title</th>
                    <th>Service Year</th>
                    <th>Education</th>
                </tr>
                </thead>
                <tbody>
                    <tr>
                        <td>Data 1</td>
                        <td>Data 1</td>
                        <td>Data 1</td>
                        <td>Data 1</td>
                    </tr>
                </tbody>
            </table>
        </div>
    </div>
</div>
</body>
</html>

 

This will create a table just like this:

Bootstrap Table
Bootstrap Table

Now lets show data from MySQL Database table onto the HTML table we just created, for that we’ll need to add some php to our code.

First we’ll have to connect to our local server and select the database, for that we’ll have to add the following script to the conn.php file. Now when including it to other HTML documents, it is always a best practice to require conn.php at the top of html document.

<?php
    $username = "root";
    $password = "";
    $hostname = "localhost";
    $db_name = "member";

    //connection to the database
    $mysqli = new mysqli($hostname, $username, $password, $db_name);
    /* check connection */
    if ($mysqli->connect_errno) {
        printf("Connect failed: %s\n", $mysqli->connect_error);
        exit();
    }
?>

Now that we are connected to our database, back in our index.php we can include conn.php and execute some queries to select data from our database tables. For selecting data from table add this code after our connection code.

<?php
    require('conn.php');

    $result = $mysqli->query("SELECT * FROM `details`");
?>

So, we are left to show data on to the table, for that we’ll be using while loop to fetch data from table and echo data to the <td> section of the table.

<?php
     while ($mem = mysqli_fetch_assoc($result)):
        echo '<tr>';
           echo '<td>'.$mem['name'].'</td>';
           echo '<td>'.$mem['phone'].'</td>';
           echo '<td>'.$mem['address'].'</td>';
           echo '<td>'.$mem['email'].'</td>';
        echo '</tr>';
     endwhile;
     /* free result set */
     $result->close();
?>

We see our table has been updated with the new data from the database.

Bootstrap Table with data from MySQL
Bootstrap Table with data from MySQL

Creating Bootstrap Modal Box

Let’s add a button on each row, which will trigger modal with the `sn` from database table to the modal data. New code inside the Loop will now be like this.

<?php
     while ($mem = mysqli_fetch_assoc($result)):
        echo '<tr>';
           echo '<td>'.$mem['name'].'</td>';
           echo '<td>'.$mem['phone'].'</td>';
           echo '<td>'.$mem['address'].'</td>';
           echo '<td>'.$mem['email'].'</td>';
           echo '<td>
                    <a class="btn btn-small btn-primary"
                       data-toggle="modal"
                       data-target="#exampleModal"
                       data-whatever="'.$mem['sn'].' ">Edit</a>
                 </td>';
        echo '</tr>';
     endwhile;
     /* free result set */
     $result->close();
?>

Remember to add a new blank <th></th> . We can place modal div on any place of the HTML Body section, it is a best practice to put it just after the body tag, paste this code just after the body tag.

<!-- Modal -->
<div class="modal fade" id="exampleModal" tabindex="-1" role="dialog" aria-labelledby="memberModalLabel" aria-hidden="true">
    <div class="modal-dialog">
        <div class="modal-content">
            <div class="modal-header">
                <button type="button" class="close" data-dismiss="modal"><span aria-hidden="true">&times;</span><span class="sr-only">Close</span></button>
                <h4 class="modal-title" id="memberModalLabel">Edit Member Detail</h4>
            </div>
            <div class="dash">
             <!-- Content goes in here -->
            </div>
        </div>
    </div>
</div>

On the above modal code I have created a div with class dash this will work as a container later on the tutorial, all other class is taken from bootstrap itself.

Load MySQL data in Bootstrap Modal Body using AJAX

Now to send that value of `sn` to modal form on editdata.php, which will be different for each row of the table and will help to extract the data from database. We’ll be using AJAX with to the Bootstrap Modal JavaScript. For this I am adding javascript references to online jQuery and Bootstrap CDN. Add this code before the ending of body section in your index.php file.

<!-- jQuery (necessary for Bootstrap's JavaScript plugins) -->
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.min.js"></script>
<!-- Latest compiled and minified JavaScript -->
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js" integrity="sha384-Tc5IQib027qvyjSMfHjOMaLkfuWVxZxUPnCJA7l2mCWNIpG9mGCD8wGNIcPD7Txa" crossorigin="anonymous"></script>

<script>
    $('#exampleModal').on('show.bs.modal', function (event) {
          var button = $(event.relatedTarget) // Button that triggered the modal
          var recipient = button.data('whatever') // Extract info from data-* attributes
          var modal = $(this);
          var dataString = 'id=' + recipient;

            $.ajax({
                type: "GET",
                url: "editdata.php",
                data: dataString,
                cache: false,
                success: function (data) {
                    console.log(data);
                    modal.find('.dash').html(data);
                },
                error: function(err) {
                    console.log(err);
                }
            });  
    })
 </script>

This is all to be done in index.php file. Now in editdata.php file we’ll fetch data from MySQL database which matches the `sn` that is sent as id from index.php modal triggering button. In this way, we’ll show data on the form fields so that it can be modified.

Creating HTML form to Import and Update data on MySQL Database

To get field data for form and load MySQL data in Bootstrap Modal Body using AJAX, we’ll match the id to `sn` of the database by making an query:

$members = $mysqli->query("SELECT * FROM `details` WHERE `sn`='$id'");
$mem = mysqli_fetch_assoc($members);

Now, we’ll put values to the form fields, here’s code for editdata.php file.

<?php
    require('conn.php');
    $id = $_GET['id'];

    if (isset($_POST['submit'])) {
    	$id = $_POST['id'];
    	$name = $_POST['name'];
    	$phone = $_POST['phone'];
    	$address = $_POST['address'];
    	$email = $_POST['email'];
    	$mysqli->query("UPDATE `details` SET `name` = '$name', `phone` = '$phone', `address`='$address', `email`='$email' WHERE `sn`=$id");
    	header("location:index.php");
    }

    $members = $mysqli->query("SELECT * FROM `details` WHERE `sn`='$id'");
    $mem = mysqli_fetch_assoc($members);

?>
<!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>Using Bootstrap modal</title>

    <!-- Bootstrap Core CSS -->
    <link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" rel="stylesheet">
</head>
<body>
<form method="post" action="editdata.php" role="form">
	<div class="modal-body">
		<div class="form-group">
		    <label for="id">ID</label>
		    <input type="text" class="form-control" id="id" name="id" value="<?php echo $mem['sn'];?>" readonly="true"/>
		</div>
		<div class="form-group">
		    <label for="name">Name</label>
	            <input type="text" class="form-control" id="name" name="name" value="<?php echo $mem['name'];?>" />
		</div>
		<div class="form-group">
		    <label for="phone">Phone</label>
	            <input type="text" class="form-control" id="phone" name="phone" value="<?php echo $mem['phone'];?>" />
		</div>
		<div class="form-group">
		     <label for="address">Address</label>
		     <input type="text" class="form-control" id="address" name="address" value="<?php echo $mem['address'];?>" />
		</div>
		<div class="form-group">
                     <label for="email">Email</label>
		     <input type="text" class="form-control" id="email" name="email" value="<?php echo $mem['email'];?>" />
		</div>
		</div>
		<div class="modal-footer">
		     <input type="submit" class="btn btn-primary" name="submit" value="Update" />&nbsp;
		     <button type="button" class="btn btn-default" data-dismiss="modal">Close</button>
		</div>
	</form>
</body>
</html>

Note: Here you can see two classes of bootstrap modal, modal-body and modal-footer this divs are kept here so that it will be rendered nicely while loading modal on webpage.

How to load MySQL data in Bootstrap Modal Body using AJAX

So in this tutorial Using Bootstrap Modal with AJAX to import data from MySQL we learnt How to load MySQL data in Bootstrap Modal Body using AJAX and Update it on the same page.

Fork and Star the Project on Github

 

12 thoughts on “How to load MySQL data in Bootstrap Modal Body using AJAX”

    • On your demo, I see you haven’t kept anything under div “dash”. Place your content inside that div.

      <div class=”dash”>
      <!– Content goes in here –>
      </div>

      Reply
  1. PLEASE HELP
    I need to add attributes to an existing database from a webpage ..for example
    say in the database we have 10 names
    I need to manually categorize the data as men and women
    but I don’t want to type
    i need two buttons (men& women)when I select the button the data should be updated in the database and the next name should come simultaneously.
    please please help..

    Reply
  2. just change the data-whatever to what id you need to pass on another page for update.
    example data data-whatever =
    then in your javascript
    var dataString = ‘id=’ + recipient;

    lastly, GET the values of id from your update page. it works fine.

    Reply
  3. How do you add a file upload into the Create form and Edit form? This is for work and if you need to contact me to discuss, that would be amazing. Thanks!

    Reply

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: