Create editable data grid in PHP and JQuery

Here is small example how to create the data grid which data comes from database and it is displayed as table. Also the table must be easily editable and would not need unnecessary buttons like ‘save’ or ‘edit’. The current implementation is not very secure so you need to add your security layer in, validations, filtering and error messages.

The idea how it works is simple, the whole table is filled with input fields. When you want to edit some field hover over the field and the edit can begin, take the cursor out of the input box and the POST request is made to server for saving data(only one field at time is updated).

Features which need to be added:

  • Add new row(simply add end of table new tr and content building a bit DOM)
  • Delete row

I added the code also in https://github.com/riston. Small snippet of the index.php

<?php
$db = new mysqli('localhost', 'root', '', 'grid');
$db->set_charset('utf8');
if ($db->connect_errno) {
	die('Check the database connection again!');
}

$userQuery = 'SELECT user_id, name, age, location FROM user';
$stmt = $db->query($userQuery);
?>
<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
		<link rel="stylesheet" type="text/css" href="css/style.css" />
		<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.7.1/jquery.min.js"></script>
		<script type="text/javascript">
			$(document).ready(function() {
				var textBefore = '';
				$('#grid').find('td input').hover(function() {
					textBefore = $(this).val();
					$(this).focus();
				}, function() {
					var $field = $(this),
						text = $field.val();
					$(this).blur();
					// Set back previous value if empty
					if (text.length <= 0) {
						$field.html(textBefore);
					} else if (textBefore !== text) {
						// Text has been changed make query
						var value = {
							'row': parseInt(getRowData($field)),
							'column': parseInt($field.closest('tr').children().find(':input').index(this)),
							'text': text
						};
						$.post('user.php', value)
						.error(function() {
							$('#message')
								.html('Make sure you inserted correct data')
								.fadeOut(3000)
								.html('&nbsp');
							$field.val(textBefore);
						})
						.success(function() {
							$field.val(text);
						});
					} else {
						$field.val(text);
					}

				});

				// Get the id number from row
				function getRowData($td) {
					return $td.closest('tr').prop('class').match(/\d+/)[0];
				}
			});
		</script>
		<title></title>
    </head>
    <body>
	<?php if ($stmt): ?>
	<div id="grid">
	<p id="message">Click on the field to edit data</p>
	<table>
		<thead>
			<tr>
				<th>Name</th>
				<th>Age</th>
				<th>From</th>
			</tr>
		</thead>
		<tbody>
		<?php while ($row = $stmt->fetch_assoc()): ?>
			<tr class="<?php echo $row['user_id']; ?>">
				<td><input type="text" value="<?php echo $row['name']; ?>" /></td>
				<td><input type="text" value="<?php echo $row['age']; ?>" /></td>
				<td><input type="text" value="<?php echo $row['location']; ?>" /></td>
			</tr>
		<?php endwhile; ?>
		</tbody>
	</table>
	</div>
	<?php else: ?>
		<p>No users added yet</p>
	<?php endif; ?>
    </body>
</html>

And the server XHR request handlre named user.php

<?php
// Detect if there was XHR request
if (!empty($_SERVER['HTTP_X_REQUESTED_WITH']) &&
	strtolower($_SERVER['HTTP_X_REQUESTED_WITH']) == 'xmlhttprequest') {
	$fields = array('row', 'column', 'text');
	$sqlFields = array('name', 'age', 'location');

	foreach ($fields as $field) {
		if (!isset($_POST[$field]) || strlen($_POST[$field]) <= 0) {
			sendError('No correct data');
			exit();
		}
	}

	$db = new mysqli('localhost', 'root', '', 'grid');
	$db->set_charset('utf8');
	if ($db->connect_errno) {
		sendError('Connect error');
		exit();
	}

	$userQuery = sprintf("UPDATE user SET %s='%s' WHERE user_id=%d",
			$sqlFields[intval($_POST['column'])],
			$db->real_escape_string($_POST['text']),
			$db->real_escape_string(intval($_POST['row'])));
	$stmt = $db->query($userQuery);
	if (!$stmt) {
		sendError('Update failed');
		exit();
	}

}
header('Location: index.php');
function sendError($message) {
	header($_SERVER['SERVER_PROTOCOL'] .' 320 '. $message);
}
About these ads

8 comments

    • Easy solution would be to add last table td class=”last” and create CSS rule .last { width: 200px; }

  1. using xampp here, the page has no problem fetching data, but any changes made were not being saved.
    did the code missed something?

    • I have not run this script for a long time, but seems there is no saving of new rows in database only updating. This is missing functionality and can be easily added.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s