Using Bootstrap Modal with AJAX to import data from MySQL

Recently, I am working with my college minor project. I am building VDC (Village Development Committee) Online Information System. I am using PHP, Bootstrap and MySQL with some addition of jQuery and AJAX to make this a complete project.  So, I am building a dashboard to show all data into a table by importing data from MySQL database using PHP, where I have 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.

Skills required:

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

Files required to create modal bootstrap with ajax to import data from MySQL databaseFor this tutorial we’ll be creating two files

index.php – This file will show all your data 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 for using Bootstrap Modal to import data from MySQL database, 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 download source code to try on your own.

Also see my previous article on Creating Simple Login Page Template using Bootstrap

Creating simple MySQL database table

Lets begin by creating MySQL table to hold member details, and insert some dummy 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

CREATE TABLE IF NOT EXISTS `details` (
  `sn` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `address` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `phone` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
  `email` varchar(40) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`sn`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=3 ;

Insert some dummy data

INSERT INTO `details` (`sn`, `name`, `address`, `phone`, `email`) VALUES
(1, 'Luzan Baral', 'Pokhara, Nepal', '9802824412', '[email protected]'),
(2, 'Anjana Sharma', 'Kathmandu, Nepal', '9802821134', '[email protected]');

Creating bootstrap table to show data from MySQL database

Let’s create index.php file to show data on tabular format from MySQL database. As we are using bootstrap classes we’ll 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

HTML Table using Twitter Bootstrap

HTML Table using Twitter Bootstrap

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 <?php ?>  section of our code. It is best to start php before every thing on our html document.

    $username = "root";
    $password = "";
    $hostname = "localhost";
    $db_name = "member"; 

    //connection to the database
    $dbhandle = mysql_connect($hostname, $username, $password)
        or die("Unable to connect to MySQL");
    $selected = mysql_select_db($db_name,$dbhandle)
        or die("Could not select examples");

Now that we are connected to our database, we can create and execute some queries to select data from our database tables. For selecting data from table add this code after our connection code.

    $qryMem = "SELECT * FROM `details`";
    $memresult =  mysql_query($qryMem) or die(mysql_error());

For more database related MySQL queries, see my lab work on Data Manipulation Language using MySQL Database.

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 = mysql_fetch_assoc($memresult)):
            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;
    ?>
Showing data from MySQL database onto HTML Bootstrap Table

Showing data from MySQL database onto HTML Bootstrap Table

Creating Twitter 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.

<td>
    <a class="btn btn-small btn-primary" data-toggle="modal" data-target="#exampleModal" data-whatever="<?php echo $mem['sn']; ?>">Edit</a>
</td>

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">
              
                </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.

Using AJAX with in Bootstrap Modal JavaScript

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 and that is done using AJAX with conjunction to the Modal JavaScript, for using twitter bootstrap modal we’ll need to add two scripting files, bootstrap.js and jquery.js which will also help to add AJAX. Add this code before the ending of body section in your index.php file.

    <!-- jQuery Version 1.11.0 -->
    <script src="../js/jquery-latest.min.js"></script>

    <!-- Bootstrap Core JavaScript -->
    <script src="../js/bootstrap.min.js"></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('.ct').html(data);
                },
                error: function(err) {
                    console.log(err);
                }
            });  
    })
    </script>

This is all to be done with index.php file, Now in editdata.php file we’ll fetch data from MySQL database which matches the `sn` send as id from index.php modal triggering button and 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, we’ll match the id to `sn` of the database by making an query:

    $id = $_GET['id'];
    $qryMem = "SELECT * FROM `details` WHERE `sn`='$id'";
    $memresult =  mysql_query($qryMem) or die(mysql_error());
    $mem = mysql_fetch_assoc($memresult) or die(mysql_error());

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

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

    //connection to the database
    $dbhandle = mysql_connect($hostname, $username, $password)
        or die("Unable to connect to MySQL");
    $selected = mysql_select_db($db_name,$dbhandle)
        or die("Could not select examples");
    $id = $_GET['id'];  

    if (isset($_POST['submit'])) {
    	$id = $_POST['id'];
    	$name = $_POST['name'];
    	$phone = $_POST['phone'];
    	$address = $_POST['address'];
    	$email = $_POST['email'];
    	$qryUpt = "UPDATE `details` SET `name` = '$name', `phone` = '$phone', `address`='$address', `email`='$email' WHERE `sn`=$id";
    	mysql_query($qryUpt) or die(mysql_error());
    	header("location:index.php");
    }

    $qryMem = "SELECT * FROM `details` WHERE `sn`='$id'";
    $memresult =  mysql_query($qryMem) or die(mysql_error());
    $mem = mysql_fetch_assoc($memresult) or die(mysql_error());
  
?>
<!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>
<form method="post" action="editdata.php" role="form">
	<div class="modal-body">             
		<div class="form-group">
			<label for="name">ID
				<input type="text" id="id" name="id" value="<?php echo $mem['sn'];?>" readonly="true"/>
			</label>
		</div>	
		<div class="form-group">
			<label for="name">Name
				<input type="text" id="name" name="name" value="<?php echo $mem['name'];?>" />
			</label>
		</div>	
		<div class="form-group">
			<label>Phone
				<input type="text" id="job" name="phone" value="<?php echo $mem['phone'];?>" />
			</label>
		</div>	
		<div class="form-group">
			<label>Address
				<input type="text" id="service" name="address" value="<?php echo $mem['address'];?>" />
			</label>
		</div>	
		<div class="form-group">
			<label>Email
				<input type="text" id="education" name="email" value="<?php echo $mem['email'];?>" />
			</label>
		</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.

Twitter Bootstrap Modal box rendering HTML form to show data from mysql using ajax

Twitter Bootstrap Modal box

So in this tutorial Using Bootstrap Modal with AJAX to import data from MySQL we learnt how to use Bootstrap modal with AJAX and PHP to load data from MySQL Database and update it on the same page.

[button link="https://www.dropbox.com/s/04p36uiajgifk2a/modal.zip?dl=0" type="big"] Download Source Code[/button]

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>