CRUD with PHP MVC, PDO, JSON and Twitter Bootstrap

CRUD with PHP MVC, PDO, JSON and Twitter Bootstrap

We will create a CRUD application by using MVC design pattern,  PDO extension, Jquery and Twitter Bootstrap.
We will use JSON as data-interchange format for HTTP request and response.

To learn about::
MVC Design Pattern -> link1, link2
PDO -> link1, link2
JSON -> link
Twitter Bootstrap -> link

Before start coding you can take a look on the demo::

First of all we create a table and populate it, use below script::

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) CHARACTER SET latin1 NOT NULL,
  `email` varchar(150) CHARACTER SET latin1 NOT NULL,
  `mobile` varchar(20) CHARACTER SET latin1 NOT NULL,
  `address` varchar(255) CHARACTER SET latin1 NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=56 ;

INSERT INTO `users` (`id`, `name`, `email`, `mobile`, `address`) VALUES
(1, 'Al-Amin Khan', [email protected]', '6546464', 'Dhaka,Bangladesh'),
(2, 'Sahed Bhuiyan', [email protected]', '987979', 'Khulna, Bangladesh'),
(3, 'Mamun', [email protected]', '1234', 'Dhaka, Bangladesh'),
(9, 'foysal', [email protected]', '1234556', 'Dhaka, Bangladesh.');

Directory Structure::
We have only two parent directory “models” and “ui”. So create these two directory in your app location.
Download Twitter Bootstrap from “Bootstrap“. and put it just under “ui” directory.

Now we will create all our views related files. So create “index.php” in app directory and put below code::

<?php include("ui/header.htm"); ?>
<?php include("ui/nav.htm"); ?>
<div id="content"></div>
<?php include("ui/deleteconfirmmodal.htm"); ?>
<?php include("ui/footer.htm"); ?>

In this “index.php” file we included all our static content as files. We will load our dynamic content in “content” div.
Next in “ui” directory create four new files “header.htm”, “footer.htm”, “nav.htm” and “deleteconfirmmodal.htm” which we already included in “index.php”. Now put content of each file from below::

header.htm

<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
    <head>
	<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
	<title>JSON CRUD</title>
	<meta name="viewport" content="width=device-width, initial-scale=1.0">
	<!-- Bootstrap -->
	<link href="ui/css/bootstrap.min.css" rel="stylesheet" media="screen">
	<link href="ui/css/crud.css" rel="stylesheet" media="screen">
    </head>

    <body>
	<div class="container">

    	    <div class="jumbotron">
		<h1>JSON CRUD</h1>
	    </div>

footer.htm

        </div>

        <script src="http://code.jquery.com/jquery.js"></script>
        <script src="ui/js/bootstrap.min.js"></script>
        <script src="ui/js/custom.js"></script>
    </body>
</html>

You can see in footer we are including “jquery” from online, if you dont have internet at your working machine then collect the latest “jquery” and load it from your local in this footer section.

nav.htm

<div class="navbar">
    <div class="navbar-inner">
	<ul class="nav">
	    <li><a href="javascript:void(0);" id="user_list"><i class="icon-th icon-black"></i> Read</a></li>
	    <li><a href="javascript:void(0);" id="create_user_form"><i class="icon-plus-sign icon-black"></i> Create</a></li>
	</ul>
    </div>
</div>

<div id="indicator" style="display: none; text-align: center;" class="loading_img">
    <img src="ui/img/indicator.gif"/>
</div>

deleteconfirmmodal.htm

<div id="delete_confirm_modal" class="modal hide fade" tabindex="-1" role="dialog" aria-labelledby="myModalLabel" aria-hidden="true">
    <div class="modal-header">
	<h3 id="myModalLabel">Delete User</h3>
    </div>

    <div class="modal-body">
	<p>Are you sure to delete this user?</p>
    </div>
    <div class="modal-footer">
	<input type="hidden" id="user_id" value="" />
	<button class="btn" data-dismiss="modal" aria-hidden="true">No</button>
	<button class="btn btn-primary delete">Yes</button>
    </div>
</div>

You will need one css file and one image, you will get it “ui -> css -> crud.css” and “ui -> img -> indicator.gif” from downloaded package of this example, and put this files in proper directory.

Create a controller “Controller.php” in app location. and put below code in it::

<?php
function __autoload($className){
    include_once("models/$className.php");
}

$users=new User("your_host","your_user","your_password","your_database");

if(!isset($_POST['action'])) {
    print json_encode(0);
    return;
}

switch($_POST['action']) {
}

exit();

Create “User.php” in “models” directory, this is our user model class. put below code in it::

<?php

class User {

    private $dbh;

    public function __construct($host,$user,$pass,$db)	{
	$this->dbh = new PDO("mysql:host=".$host.";dbname=".$db,$user,$pass);
    }
}

For our custom javascript create a “custom.js” file in “ui -> js” directory. and put below code::

$(function() {
});

At this point if you access your this application you will get a page like below::
MVC, PDO, JSON, Bootstrap

Now we will functionality one by one.
First of all we will add “Read” functionality. On “Read” button click we will display all user list from database in our “content” div by Ajax call.

So add a line in “custom.js” file’s “$(function()” block like below::

$(function() {
    $(document).on("click", "a#user_list", function(){ getUserList(this); });
});

Here we are adding a click listener on “user_list” link click.
After that block add below functions “getUserList” and “renderUserList”::

function getUserList(element) {

    $('#indicator').show();

    $.post('controller.php',
        {
            action: 'get_users'
        },
        function(data, textStatus) {
	    renderUserList(data);
	    $('#indicator').hide();
        },
        "json"
    );
}

function renderUserList(jsonData) {

    var table = '<table width="600" cellpadding="5" class="table table-hover table-bordered"><thead><tr><th scope="col">Name</th><th scope="col">Email</th><th scope="col">Mobile</th><th scope="col">Address</th><th scope="col"></th></tr></thead><tbody>';

    $.each( jsonData, function( index, user){
	table += '<tr>';
	table += '<td class="edit" field="name" user_id="'+user.id+'">'+user.name+'</td>';
	table += '<td class="edit" field="email" user_id="'+user.id+'">'+user.email+'</td>';
	table += '<td class="edit" field="mobile" user_id="'+user.id+'">'+user.mobile+'</td>';
	table += '<td class="edit" field="address" user_id="'+user.id+'">'+user.address+'</td>';
	table += '<td><a href="javascript:void(0);" user_id="'+user.id+'" class="delete_confirm btn btn-danger"><i class="icon-remove icon-white"></i></a></td>';
	table += '</tr>';
    });

    table += '</tbody></table>';

    $('div#content').html(table);
}

By above function “getUserList” we are sending a “get_users” POST request to collect all users as JSON data and with these JSON data function “renderUserList” is rendering user list in a table.

Open “Controller.php” and add below “case” block in “switch” ::

case 'get_users':
    print $users->getUsers();
break;

Now we have to add “getUsers” mathod to “User” model class. so open “User.php” file and add this below method::

public function getUsers(){
    $sth = $this->dbh->prepare("SELECT * FROM users");
    $sth->execute();
    return json_encode($sth->fetchAll());
}

Now if you click on “Read” button you can see a nice table with all users like below without whole page load::
MVC, PDO, JSON, Bootstrap

To create functionality, open “custom.js” and add two new listeners to get create user FORM and save this new user. so modify your “$(function()” block like below::

$(function() {
    $(document).on("click", "a#user_list", function(){ getUserList(this); });
    $(document).on("click", "a#create_user_form", function(){ getCreateForm(this); });
    $(document).on("click", "button#add_user", function(){ addUser(this); });
});

After this “$(function()” block add these two functions from below::

function addUser(element) {

    $('#indicator').show();

    var User = new Object();
    User.name = $('input#name').val();
    User.email = $('input#email').val();
    User.mobile = $('input#mobile').val();
    User.address = $('textarea#address').val();

    var userJson = JSON.stringify(User);

    $.post('controller.php',
        {
	    action: 'add_user',
	    user: userJson
	},
	function(data, textStatus) {
	    getUserList(element);
	    $('#indicator').hide();
	},
	"json"
    );
}

function getCreateForm(element) {
    var form = '<div class="input-prepend">';
	form +=	'<span class="add-on"><i class="icon-user icon-black"></i> Name</span>';
	form +=	'<input type="text" id="name" name="name" value="" class="input-xlarge" />';
	form +=	'</div><br/><br/>';

        form +=	'<div class="input-prepend">';
	form +=	'<span class="add-on"><i class="icon-envelope icon-black"></i> Email</span>';
	form +=	'<input type="text" id="email" name="email" value="" class="input-xlarge" />';
	form +=	'</div><br/><br/>';

	form +=	'<div class="input-prepend">';
	form +=	'<span class="add-on"><i class="icon-headphones icon-black"></i> Mobile</span>';
	form +=	'<input type="text" id="mobile" name="mobile" value="" class="input-xlarge" />';
	form +=	'</div><br/><br/>';

	form +=	'<div class="input-prepend">';
	form +=	'<span class="add-on add-on-area "><i class="icon-home icon-black"></i> Address</span>';
	form +=	'<textarea row="5" id="address" name="address" class="input-xlarge"></textarea>';
	form +=	'</div><br/><br/>';

	form +=	'<div class="control-group">';
	form +=	'<div class="">';
	form +=	'<button type="button" id="add_user" class="btn btn-primary"><i class="icon-ok icon-white"></i> Add User</button>';
	form +=	'</div>';
	form +=	'</div>';

	$('div#content').html(form);
}

By function “getCreateForm” we are rendering a FORM for create new user. and function “addUser” send a “add_user” POST request with all given data in create FORM as JSON.

Open “Controller.php” and add below “case” block in “switch” ::

case 'add_user':
    $user = new stdClass;
    $user = json_decode($_POST['user']);
    print $users->add($user);
break;

Here we are decoding “user” POST variable and passing this to “add” method of “User” class.

Now we have to add “add” mathod to “User” model class. so open “User.php” file and add this below method::

public function add($user){
    $sth = $this->dbh->prepare("INSERT INTO users(name, email, mobile, address) VALUES (?, ?, ?, ?)");
    $sth->execute(array($user->name, $user->email, $user->mobile, $user->address));
    return json_encode($this->dbh->lastInsertId());
}

Clicking on “Create” button you will get a nice looking FORM like below::
MVC, PDO, JSON, Bootstrap

Fill up this FORM and click “Add User” button, all data will save and without page load you can see the updated user list.

Hope you already see a “red” button on user list table, it is for delete user which is in current row. So now we want to delete recently added user. Let implement the “Delete” functionality.

Open “custom.js” file and add another new two listener to it, so your “$(function()” block will look like below::

$(function() {
    $(document).on("click", "a#user_list", function(){ getUserList(this); });
    $(document).on("click", "a#create_user_form", function(){ getCreateForm(this); });
    $(document).on("click", "button#add_user", function(){ addUser(this); });
    $(document).on("click", "a.delete_confirm", function(){ deleteConfirmation(this); });
    $(document).on("click", "button.delete", function(){ deleteUser(this); });
});

After this “$(function()” block add these two functions from below::

function deleteConfirmation(element) {
    $("#delete_confirm_modal").modal("show");
    $("#delete_confirm_modal input#user_id").val($(element).attr('user_id'));
}

function deleteUser(element) {

    var User = new Object();
    User.id = $("#delete_confirm_modal input#user_id").val();

    var userJson = JSON.stringify(User);

    $.post('controller.php',
    {
	action: 'delete_user',
	user: userJson
    },
    function(data, textStatus) {
	getUserList(element);
	$("#delete_confirm_modal").modal("hide");
    },
    "json"
    );
}

Function “deleteConfirmation” will display a confirmation modal which will ask about delete user. and function “deleteUser” will delete user with given user id.

Open “Controller.php” and add below “case” block in “switch” ::

case 'delete_user':
    $user = new stdClass;
    $user = json_decode($_POST['user']);
    print $users->delete($user);
break;

Here we are decoding “user” POST variable and passing this to “delete” method of “User” class.

Now we have to add “delete” mathod to “User” model class. so open “User.php” file and add this below method::

public function delete($user){
    $sth = $this->dbh->prepare("DELETE FROM users WHERE id=?");
    $sth->execute(array($user->id));
    return json_encode(1);
}

Clicking on any “Red Cross” button it will display a nice modal to get confirmation from you like below::
MVC, PDO, JSON, BootStrap

If you click on “No” button then modal will disappear and no server action but if you click on “Yes” button then the user will delete and you can see your updated user list.

And at last the special thing “Edition”, why special? because we will implement a inline edition now. we only use jquery but no any additional js plugin for this easy and simple and few code. So open your “custom.js” and update like below::

$(function() {
    $(document).on("click", "a#user_list", function(){ getUserList(this); });
    $(document).on("click", "a#create_user_form", function(){ getCreateForm(this); });
    $(document).on("click", "button#add_user", function(){ addUser(this); });
    $(document).on("click", "a.delete_confirm", function(){ deleteConfirmation(this); });
    $(document).on("click", "button.delete", function(){ deleteUser(this); });
    $(document).on("dblclick", "td.edit", function(){ makeEditable(this); });
    $(document).on("blur", "input#editbox", function(){ removeEditable(this) });
});

You can see we added new two listener, one on double click and another one on blur. so when we double click on any data, a editable input box will appear and after necessary change when we move the focus from this input field then it will disappear and return to as before.

So after this block now add two new functions from below::

function removeEditable(element) {

    $('#indicator').show();

    var User = new Object();
    User.id = $('.current').attr('user_id');
    User.field = $('.current').attr('field');
    User.newvalue = $(element).val();

    var userJson = JSON.stringify(User);

    $.post('controller.php',
    {
	action: 'update_field_data',
	user: userJson
    },
    function(data, textStatus) {
	$('td.current').html($(element).val());
	$('.current').removeClass('current');
	$('#indicator').hide();
    },
    "json"
    );
}

function makeEditable(element) {
    $(element).html('<input id="editbox" size="'+  $(element).text().length +'" type="text" value="'+ $(element).text() +'">');
    $('#editbox').focus();
    $(element).addClass('current');
}

Open “Controller.php” and add below “case” block in “switch” ::

case 'update_field_data':
    $user = new stdClass;
    $user = json_decode($_POST['user']);
    print $users->updateValue($user);
break;

Now we have to add “updateValue” mathod to “User” model class. so open “User.php” file and add this below method::

public function updateValue($user){
    $sth = $this->dbh->prepare("UPDATE users SET ". $user->field ."=? WHERE id=?");
    $sth->execute(array($user->newvalue, $user->id));
    return json_encode(1);
}

Here “updateValue” method updating data of given field.

Now if you double click on any data you will get a input field to edit this data like below::
MVC, PDO, JSON, Bootstrap

After change when this field get lost the focus then the new data will save and it will become like before as non editable.

So those are all for this article. Thanks.

I am a student of Web Application Development courses at Google University.
24 comments on “CRUD with PHP MVC, PDO, JSON and Twitter Bootstrap
  1. first thanks very for this…it is helping.

    so i download the code and run it on a local xampp server and everything seems to be working (i dump debug statements to make sure database is sending data and all that) but when it tries to display the users all i get is the spinning wheel and nothing.

    its very frustrating…ive been trying over and hour and i know that i set up the db and table correctly..i can see the query work and checked the data…everything is ok except the renderUserList() in custom.js is never getting called (i use Chrome with the Console and breakpoints)…it seems to get hung up in some jquery area.

    please…i would like to see this work locally…any ideas?

    • fyi…when i run it on a hosted account it runs fine…it must have something to do with the MIME-type setting of the png files…at least thats the errors in the Console…

      “Resource interpreted as Image but transferred with MIME type image/x-png: “http://localhost:8080/ui/img/glyphicons-halflings-white.png””

  2. Thank you very much for this and i love your tutorial so much, im beginner and trying to apply this form into a bootstrap template. but some part kinda messed up (ie: the modal border lost), which file i need to customize if by any chance i want to add more form to input into database (maybe like image or another form into more table). Thank you in advance 🙂

  3. Would like to see it work. Demo does not seem work, get spinning wheel then blank page

    local machine
    read just gives me spinning wheel
    create shows the input put will not update DB
    MySQL workbench will show a client connection from the example.

    MySQL 5.6.13
    Apache/2.4.7 (Win64) PHP/5.5.7

    Any help out there?

  4. Hi,
    Excellent All coding, is the first example simple and functionality that I have Found.

    I have a questions.

    how to add new object? I have created other object, I have created CRUD using the same structure that ‘User.php’, but in custom.js how manage 2 different object with the same modal questions?
    this line I don´t know how to modify.

    $(document).on(“click”, “button.delete”, function(){ deleteUser(this); });.

    if you decide I will collaborate with you to expand this tutorial to manage 2 or more objects, this reference is very necessary for much people.

    Cesar from Venezuela, my poor English go way to better.

  5. Hello, I tried to change your example in order to make it work with Firebird. The integration works well, but when i go to see the data in the columns I only see data like: undefined.

    Why? I await your news

  6. Hey, this is great tutorial!
    Could you show how to make this CRUD with simple auth and then read only (the users list) mode for public?

  7. I want to use it with diferent table names and columns. Ho can i implement another table? I try changing in custom.js file but no success. Please give an example? Thank you!

  8. I did not mean you personally, i meant you as in one. It’s quite spiutd to insult a singer/songwriters looks i think, since looks don’t matter when it’s the music that you listen to, that’s why i was joking. I think that both Marilyn and Mick was much more attractive in their youth, but not so much anymore.

Leave a Reply

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