The basic C(R)UD
I recently made a very simple but functional administrational interface using jQuery and Ajax to avoid having to refresh the page all the time thus simplifying development.
The only convention/requirement here is that any database table that uses the interface has a unique id column aptly named id.
Let’s start with adding stuff:
Add:
<form action="<?php echo 'http://'.$_SERVER['SERVER_NAME'].$_SERVER['REQUEST_URI'] ?>" method="POST">
<table>
<tr>
<?php foreach($struct as $row): ?>
<?php if($row->Field != "id"): ?>
<th><?php echo ucfirst($row->Field) ?></th>
<?php endif ?>
<?php endforeach ?>
</tr>
<tr>
<?php foreach($struct as $row): ?>
<?php if($row->Field != "id"): ?>
<td><input class="add_input" type="text" name="<?php echo $row->Field ?>"/></td>
<?php endif ?>
<?php endforeach ?>
<td> <input type="submit" id="add_submit" value="Save"> </td>
</tr>
</table>
<input type="hidden" name="post_action" value="insert"/>
</form>The insert/add form is using automatically generated meta information in the $struct array of objects. This information is used to generate column headlines but more importantly to automatically assign a name to each input field.
Let take a look at the PHP/MySQL behind this:
if(!empty($_POST['post_action'])){
$func = $_POST['post_action'];
unset($_POST['post_action']);
$this->$func();
}
function insert(){
echo "insert";
$this->sql->insertArray($this->table(), $_POST);
}
$struct = $this->sql->loadObjects("SHOW COLUMNS IN ".$this->table());We have a check for the presence of post_action in the POST array if we have it we use its value to call that function in the current class ($this->$func();), one of a select few nifty features of PHP.
If the post_action is insert we simply call insert() above which in turn makes use of a class called SQL which is outside the scope of this tutorial. Suffice to say that insertArray will build a MySQL insert query from the contents of the array and execute it.
A simple SHOW COLUMNS query, very handy in these cases when you need to create interfaces automatically.
Editing is the hardest part because we do it on a per cell basis, we don’t submit the whole row. Therefore we need to keep track of both row id and name, ie. the $key-$id combo below where $key is the field name and the $id the unique id of the row.
Edit:
<table id="jform_table">
<?php foreach($rows as $id => $row): ?>
<tr id="row-<?php echo $id ?>" >
<?php foreach($row as $key => $val): ?>
<?php if($key != 'id'): ?>
<td> <input id="<?php echo "$key-$id" ?>" type="text" value="<?php echo $val ?>" /> </td>
<?php endif ?>
<?php endforeach ?>
<td>
<form action="<?php echo 'http://'.$_SERVER['SERVER_NAME'].$_SERVER['REQUEST_URI'] ?>" method="POST">
<input type="submit" id="delete_submit" value="Delete">
<input type="hidden" name="post_action" value="delete"/>
<input type="hidden" name="row_id" value="<?php echo $id ?>"/>
</form>
</td>
</tr>
<?php endforeach; ?>
</table>The javascript making all of this happen:
$(document).ready(function(){
$("input").keydown(function(event){
if(event.keyCode == 13){
var field_id = $(this).attr("id").split("-");
$.post(window.location.href, {field: field_id[0], id: field_id[1], val: $(this).val()});
}
});
});So as soon as we are editing a field we look for return key press, if we get it we post the value to the same url that generated the form. We submit the field name (field_id[0]) and the row id (field_id[1]) which we obtain by splitting the key-id combo.
So before we render the HTML we have the following check somewhere in the PHP code:
if(!empty($_POST['id'])){
$this->sql->query("UPDATE ".$this->table()." SET {$_POST['field']} = '{$_POST['val']}' WHERE id = {$_POST['id']}");
echo "ok";
exit;
}If the POST global contains the id key we know we have an update request on our hands so we use the POST information to update the row.
As you could see above we also have a delete form attached to each row which upon submission will delete said row. This is not by way of Ajax and there is no JavaScript involved, hence each submit will refresh the page. Note that we submit to the same PHP script that generated the form yet again, all this stuff is self-contained in the same document.
Related Posts
Tags: ajax, Javascript, jquery, PHP