How to update a Database with AJAX using a Table

First rule: There is always more than one way to do something.
The way you choose is as good a choice as any, if it works correctly. The best thing you can do is to get it working first.

BUT you can always ask yourself, and you should all the time... "Can I make this simpler?" and "Is this the best way to do this?", but don't let that get in the way of getting something working first!

So having said that... Let's continue on.

The Displaying of entries from a Database is a wonderful thing. But how do you easily edit them?

So lets say you had something like this...

This is a simple table where each table cell is a form text input. Why were form text inputs chosen? Well only because it gives us a simple way to edit an entry. Also, the person asking for help on this had already decided on this method, so I thought, let's stick with it and see how it plays out!

As you do this a few times, you will see, that there is a nice simple "recipe" that you will find, that allows you to work through these issues.

Starting from the Top!

So to perform this investigation, we need to come up with a script that will allow us to emulate the problem at hand and allow us to test things out. In other words, a playpen!

So we'll need an index.php file and why not make this a proper HTML page where we can display our table.

Here is the index.php file!

This is a direct copy of the boiler plate code (Basic Template) from Bootstrap

<?php
/**
 * This is the Main View for the demonstration code
 */
include( "db_connect.php" );
include( "functions.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">
	<!-- The above 3 meta tags *must* come first in the head; any other head content must come *after* these tags -->
	<title>Bootstrap 101 Template</title>

	<!-- Bootstrap -->
	<link href="assets/css/bootstrap.min.css" rel="stylesheet">

	<!-- HTML5 shim and Respond.js for IE8 support of HTML5 elements and media queries -->
	<!-- WARNING: Respond.js doesn't work if you view the page via file:// -->
	<!--[if lt IE 9]>
	<script src="https://oss.maxcdn.com/html5shiv/3.7.3/html5shiv.min.js"></script>
	<script src="https://oss.maxcdn.com/respond/1.4.2/respond.min.js"></script>
	<![endif]-->
</head>
<body>
<h1>Welcome to LookingOverMyShoulder - PHP Tuts</h1>
<?php
/* Populate the table and display it*/
include( 'form_table.php' ); // This gets the table...
?>
<!-- jQuery (necessary for Bootstrap JavaScript plugins) -->
<script src="assets/js/jquery.min.js"></script>
<!-- Include all compiled plugins (below), or include individual files as needed -->
<script src="assets/js/bootstrap.min.js"></script>
<script src="assets/js/jquery-ui.min.js"></script>
<script src="assets/js/table_edit.js"></script>
</body>
</html>

So there is nothing too wonderful there, except for some little additions and changes. So let's quickly summarize those.

  • We have made this an index.php file, where we have embedded some php into the boilerplate HTML
  • You might notice that I have removed all the CDN links to the css and js files and made them all local.
  • At the top of this file, we have included two php files.
    • db_connect.php
    • functions.php
  • And you may notice the little extra table_edit.js file added at the bottom.
The reason for the includes is, to prevent adding in lots of code in here. But the main point here is that I am defining what needs to be worked on.

So I know I need to create a connection to the Database, so create a file for that.

I know I will need some functions to talk to the database, so create a file for that.

I know I will be using Jquery and AJAX, so create a file for that.

Now let's go through those one at a time.

Now the whole idea here is to be modular. So where ever you are going to repeat code, turn it into an include file so you can include it inline.

And this is exactly what the first two include statements do!

include( "db_connect.php" );
include( "functions.php" );

db_connect.php

<?php
/**
 * Establish the Database Connection
 *
 */
/*
 * For a Production Environment we want to disable the standard PHP Errors
 *   to help minimize telling someone too much information
 */
// For a Development site we want this to be set to 1
// For a Production sie we want this to be set to 0
ini_set('display_errors',1); // Show all errors when in Development mode.
/* The Database Connection */
// This is the Database Global Handle.
global $db;
// Attempt to connect to the Database creating a mysqli class
$db = new mysqli('localhost','root','','phptut');
// If we have an error, report it and stop execution
if($db->connect_errno > 0){
	exit('Database Connection Failed!');
}

So as we need to connect to the database on each page load, why not make it a file and include it.

Things to note! We are using the mysqli class which we are creating an instance of in $db.
So we can use this as a handle to our database object, we need to make it global to make it simple to pass into our functions when required.

While it is considered bad practice to use globals, and I totally agree... in this instance it is ok! Otherwise we would have to pass it in as a parameter to every function that requires it.

There are other ways to get around this, but that is a topic for another day.

functions.php

<?php
/**
 * Common Database Functions
 *
 */

/**
 * Give me all the Member rows, please!
 *
 * @return mixed
 */
function get_members() {
	global $db;
	$sql    = "SELECT * FROM member";
	$result = $db->query($sql);
	// By default fetch_all gives both indexed(numbers) and associative(named) indexes in the result
	// so using MYSQLI_ASSOC tells fetch_all to just give us the associative(named) indexes.
	return $result->fetch_all(MYSQLI_ASSOC);
}

/**
 * Given the correct data, update the member entry
 *
 * @param array $data
 *
 * @return bool
 */
function update_member($data) {
	global $db;
	// Check that we have valid field names
	// We are using keys (index names) to make this simpler
	//   as the result set from the database contains them.
	$expected_array = [ 'id' => '', 'name' => '', 'phone' => '', 'email' => '', 'score' => '' ];
	if( ! validate_database_field_names($data, $expected_array)) {
		return false;
	}
	/* If we get here- the data has the correct field names required,
	     BUT does not validate the values YET! */

	/* $db is a class, so to access it's member, function prepare, so you need to use $db->prepare()
	   It is the same as saying... Give me the prepare method that belongs to the $db class,
	   where $db is an instance of the mysqli class.
	*/

	/* The Question marks '?' indicate the place holders for the values*/
	$stmt = $db->prepare("UPDATE member 
	SET name = ?, 
        phone = ?, 
        email = ?,  
        score = ?  
        WHERE id = ?");

	/* We need to define the values that will be used in the above place holders.
	    We also need to indicate the types of each column,
	    sssii means String, String, String, Integer, Integer
	*/
	$stmt->bind_param('sssii',
		$data['name'],
		$data['phone'],
		$data['email'],
		$data['score'],
		$data['id']);
	$stmt->execute(); // This is the GO BUTTON - GO and Execute the Query
	$stmt->close();   // This is for nicness... The connection will close once the script ends.

	/* Check if we have any errors and tell the caller what happened, True = Dobro, False = Shit!*/
	if($db->errno > 0) {
		return false; // Nope it buggered up which is unlikely as we are using superior coding skills so
		              // only a monumental "stuff up" on the server will cause this.
	}

	return true; // As we have done some checking we should see this happen 99.99% of the time.
}

/**
 * Check that the Keys in the array being passed in matches the expected
 * keys required to perform the DB operation.
 *
 * @param array $data ['key'=>'value']
 * @param array $expected_array ['key'=>''] , the values can be empty as
 *                                              only the keys are being checked
 *
 * @return bool
 */
function validate_database_field_names($data, $expected_array) {
	$count_required = count($expected_array);
	$result_array   = array_intersect_key($expected_array, $data);
	if(count($result_array) != $count_required) {
		return false; // Whoops! The Data we are submitting is missing a key!
	}

	return true; // All is good! We have all the required field names.
}


So we are going to need a way to read the existing entries from the Database, and a way to update the changes back into the database.

As we are using the mysqli class, we can use much better sql calls than the old mysql_ days. Here we are using prepared statements. Although they look like more work, in reality, the benefits they give you over security etc is much much better. So start using them. Or you can use PDO which is a little harder to learn, but gives you a general way to talk to many different databases.

NOTE: If this all looks weird and you do not understand it, then GOOGLE IT and do some reading! We've all had to read up on this to learn how to use it. Then we've tried writing code to use it...It's not going to make instant sense just by looking at some example code!

I had to go and look this up again just to do this example!

functions.php - get_member

/**
* Give me all the Member rows, please!
*
* @return mixed
*/
function get_members() {
global $db;
$sql    = "SELECT * FROM member";
$result = $db->query($sql);
// By default fetch_all gives both indexed(numbers) and associative(named) indexes in the result
// so using MYSQLI_ASSOC tells fetch_all to just give us the associative(named) indexes.
return $result->fetch_all(MYSQLI_ASSOC);
}

The Table Generation Code

<?php
/**
 * Partial View - Table for performing editing of entries.
 *
 * This is the test table for demonstrating the code.
 */

// Get the array of member entries
// There is No Pagination as yet.
$member_rows = get_members(); // This gets the data from the Database
?>
<table class="table">
	<tr>
		<th>Id</th>
		<th>Name</th>
		<th>Phone</th>
		<th>Email</th>
		<th>Score</th>
		<th>Change</th>
	</tr>
	<?php
	// This creates the rows using the data from the database.
	foreach ( $member_rows as $row ) {
		echo '<tr class="nr">';
		echo '<td>';
		echo '<input type="text" name="id" value="'.$row['id'].'" disabled >';
		echo '</td>';
		echo '<td>';
		echo '<input type="text" name="name" value="'.$row['name'].'" >';
		echo '</td>';
		echo '<td>';
		echo '<input type="text" name="phone" value="'.$row['phone'].'" >';
		echo '</td>';
		echo '<td>';
		echo '<input type="text" name="email" value="'.$row['email'].'" >';
		echo '</td>';
		echo '<td>';
		echo '<input type="text" name="score" value="'.$row['score'].'" >';
		echo '</td>';
		echo '<td>';
		echo '<input class="btn btn-xs btn-primary" id ="'.$row['id'].'" type = "submit" name = "Update" value = "Update" >';
		echo '</td>';
		echo '</tr >';
	}
	?>
</table>

We have all the table generation code in form_table.php. It contains both HTML and PHP.

As you can see, we call the database function get_members() which we save into $member_rows.

Then we generate the table, using a foreach statement to loop through each array, retrieving the values for each column in the table.

And this is where a lot of new programmers can get very Stuck when writing code like this.

So when we run the script, we will see the table as shown at the beginning of this page.

We have just run through the code to create the table with the values from the database.

But what about updating things? This is where the real fun begins.

What we need to do to Update the Database.

So we now have the table with populated rows, and each column or field, is a text box where we can type in and alter their content. We also have a BUTTON on the end of each row, which if we click on it, it should update the database, right?

Well it won't do anything right now as it's just a table of text inputs. There is No FORM, there is no submit button. So where do we go from here?

In Steps AJAX!

NOTE: This is a NOT a complete discussion on AJAX. It is an example and you will need to research, read up and use this yourself to gain a proper understanding. So what is stated here is a quick walk through with some pointers to make it a bit clearer but do not expect this to make you an expert in this.

There is a thought process you need to understand to achieve something like this. First of all you need to understand that a browser, has built into it, the ability to know what and when you do anything on the web page. We as programmers can tap into this using javascript.

But javascript is one of those languages that you really really need to spend a lot of time learning and to those of you who are fluent in javascript, I hold you in high regard! It is very powerful and is way beyond the abilities of most of us! So In Steps jQuery! jQuery is a Javascript library that is more suited to us mere mortals. It's been designed to be, well, more usable and takes care of any cross browser issues and a whole bunch of other potential headaches no one wants!

Next we have a thing called AJAX, which stands or Asynchronous Javascript And XML. AJAX is a recipe, or a bunch of technologies. In our case, it's mixing Javascript, via jQuery and PHP together.

So we don't need to go deep into this, what we do need to do is understand the process. So in short what we want to do, using our big words... is...

When I click on an update button, I want the values in the table associated with the entry in the database to be updated!

So now we start asking questions... This is the fun part and there a many ways to solve this!

Ok, so we need to look at the button. When I click on it, what information do I need to provide, so "something" can tell which button I clicked on?

Excellent question!

I would add... how do we know what row in the database is associated with that button? Keep in mind that we need to tie the data shown in the table to where it came from in the database. And there are a few ways... But I'll stick with using the id field as we know that the id is unique for each entry in the database. So let's pick on the id. The other benefit is we can use this number to identify the button! How wonderful!

So how can we tag each button with the id from the table where the information came from? Well I have done it by assigning an id to each button.

If you look at the table code, you will see this...

form_table button code

echo '<input class="btn btn-xs btn-primary" id ="'.$row['id'].'" type = "submit" name = "Update" value = "Update" >';

Where we have assigned an id to each button using id ="'.$row['id'].'"

So each button is uniquely identified with the row id of the entry in the database table. Nice huh!

Now we have to get jQuery to recognise it when we click the button and to get the id associated with it.

So clicking on something is an event! In this case, it's a button click event. So we need to understand how jQuery detects this. It is here that we can employ some magic debugging skills.

So let's try this...

table-edit.js stage 1

$(document).ready(function () {
    /* Event when we click on the Update Button*/
    $('.btn').click(function () {
        // Do something
    })
});

On the first line, the outer document ready statement simply says, don't execute this code until the page has completed loading. In many cases this is exactly what we need.

So we need to identify we've clicked on a button. Now our buttons have the class ="btn". So this statement is saying, "When I see a Page Element with a class of btn and it is Clicked, then do something.

One of the first mistakes you'll ever make is getting this correct. To this day I ensure that I have got this right before going any further. One way you can do that is to to add in the commend console.log('I clicked on a button');

Note: This discussion could get very long and will be best explained in a video (Coming soon)

So let's just take a look at the whole jQuery Event, AJAX Code.

table-edit.js

$(document).ready(function () {

    /* Event when we click on the Update Button*/
    $('.btn').click(function () {
        /* We need to get the data from the row*/
        var $row = $(this).closest('tr');
        var $columns = $row.find('td');

        var values = {};

        $(this).closest('tr').find("input").each(function () {
            values[this.name] = this.value
        });
        console.log(values);

        /* Now we need to send this to our php via a ajax call */
        /* This takes the row information and sends it to the PHP File called ajax.php*/
        $.ajax({
            type: "POST",
            url: 'http://phptuts.com/ajax.php',
            data: {data: values},
            dataType: 'json',
            success: function (data) {
                // $columns.addClass('success');
                if (data.result) {
                    $columns.effect("highlight", {color: "green"}, 3000);
                } else {
                    $columns.effect("highlight", {color: "red"}, 3000);
                }
                $columns.addClass('active');
            },
            error: function () {
                $columns.effect("highlight", {color: "red"}, 3000);
                console.log("Whoops!");
            }
        });
    })
});

So there are basically two main sections to this. There is the detection of the button click and inside that is the AJAX Call. There are also the added effects for turning the whole row Green after a successful update. If there is an error, then the row will and does, turn Red. Then that fades out and the row gets a grey background as a way to indicate a change had occurred.

Of course there are many other things we could do here. We could change the color of the text input border when something has changed as an good visual indicator showing we should click on the update.

We could also make it so whenever a change is made and we click off somewhere else or press enter that field is immediately updated. All things are possible, it's just a matter of deciding what would work best for your use.

The AJAX part is worth a mention here. The Important parts are defining the type, url, data and dataType.

You will notice that the url points to a different file. The reason behind that is any AJAX Calls are performed asynchronously and it's a very good idea to have a dedicated ajax handler to deal with these requests.

The PHP file we use to handle that is...

ajax.php

<?php
/**
 * AJAX Handler
 *  - At the moment this is only processing One AJAX Post
 *  1. Table Update
 *
 * The $_POST is getting an array called data
 * So we need to reference the associative array items from it
 *   $_POST['data']['item']
 *
 *
 * This file is called by the AJAX Call.
 *
 */
/* This expects to see a POST, so the AJAX is behaving like a Form POST*/
if(isset( $_POST )) {
	// This is a call from a table using a button called Update
	// @todo this needs an action to help identify the caller.
	if(isset( $_POST['data']['Update'] )) {
		// Extract the array using the ole fashioned way.
		$values = [];
		foreach ( $_POST['data'] as $key => $value ) {
			$values[ $key ] = $value;
		}
		/* Include the required files to perform the Database Update */
		include( "db_connect.php" ); // AHH the beauty of Separate files and functions :)
		include( "functions.php" );
       // Update the Database member table
		$result = update_member($values);
       // Determine the appropriate response
		if($result === true) {
			// Everything worked wonderfully.
			echo json_encode([ 'result' => true, 'data' => 'It works - Data Saved' ]);
		} else {
			// A Database Error occurred.
			echo json_encode([ 'result' => false, 'data' => 'It works - It did NOT Save' ]);
		}
		exit(); // We always need to exit after echoing a json_encode in response to an AJAX Call
	} else {
		// This is not the correct caller based upon the Update value being posted
		echo json_encode([ 'result' => false, 'data' => 'Nope we are missing something' ]);
		exit();
	}
}

This the PHP File that the AJAX call sends the "Posted" data to.

So this file checks that it was posted to by the correct AJAX Call and determines what code to run.

So this is where the call to the Database Update is performed.

It then gets the response back from the database update and sends the appropriate response back to the AJAX call, which it deals with and performs the required actions back on the webpage.


Now it would be a good idea to show the SQL to create the table and Data.

members.sql

You can simply copy and paste this in via either phpmyadmin or whatever mysql utility you like to use.

-- phpMyAdmin SQL Dump
-- version 4.6.4
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Oct 30, 2016 at 10:22 AM
-- Server version: 5.7.11
-- PHP Version: 7.0.4

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `phptut`
--

-- --------------------------------------------------------

--
-- Table structure for table `member`
--

CREATE TABLE IF NOT EXISTS `member` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) NOT NULL,
  `phone` varchar(64) NOT NULL,
  `email` varchar(64) NOT NULL,
  `score` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `member`
--

INSERT INTO `member` (`id`, `name`, `phone`, `email`, `score`) VALUES
(1, 'Fred Smith', '789-654-321', 'fred@fredsmith.com', 56),
(2, 'sam Jones', '123-456-789', 'sam@samjones.com', 72);

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;