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(' ');
$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);
}
i couldnt edit the css ! i wanted to increase the widthe of the last field.
Easy solution would be to add last table td class=”last” and create CSS rule .last { width: 200px; }