Live table update using AJAX

[LinusIT]

Hi

I've got a table that I need to edit sometimes and instead of using phpMyAdmin I wanted to do it live. I found a fantastic script online which helps me do this but it isn't saving the changes to the database, only showing the new information on the screen until the page is refreshed.

Here's what draws the table (works great):

<?php
$color1 = "even";
$color2 = "odd";
$row_count = 0;
while($row=mysql_fetch_array($result1)) {
$row_color = ($row_count % 2) ? $color1 : $color2;
$id = $row['weighbridge_id'];
$ref = $row['weighbridge_ref'];
$in = $row['weighbridge_in'];
$out = $row['weighbridge_out'];
$receipt = $row['weighbridge_receipt'];
?>
<tr id="<?php echo $id; ?>" class="edit_tr <?php echo $row_color; ?>">
<td class="edit_td"><span id="ref_<?php echo $id; ?>" class="text"><?php echo $ref; ?></span>
<input type="text" value="<?php echo $ref; ?>" class="editbox w140" id="ref_input_<?php echo $id; ?>" /></td>
<td class="edit_td"><span id="in_<?php echo $id; ?>" class="text"><?php if ($in == 0) { echo "&nbsp;"; } else { echo "&pound;" . $in; } ?></span>
<input type="text" value="<?php echo $in; ?>" class="editbox w80" id="in_input_<?php echo $id; ?>" /></td>
<td class="edit_td"><span id="out_<?php echo $id; ?>" class="text"><?php if ($out == 0) { echo "&nbsp;"; } else { echo "&pound;" . $out; } ?></span>
<input type="text" value="<?php echo $out; ?>" class="editbox w80" id="out_input_<?php echo $id; ?>" /></td>
<td class="edit_td"><span id="receipt_<?php echo $id; ?>" class="text"><?php echo $receipt; ?></span>
<input type="text" value="<?php echo $receipt; ?>" class="editbox w140" id="receipt_input_<?php echo $id; ?>" /></td>
</tr>
<?php $row_count++; }?>

Here's the Javascript/Ajax code:

<script type="text/javascript">
$(document).ready(function()
{
$(".edit_tr").click(function()
{
var ID=$(this).attr('id');
$("#ref_"+ID).hide();
$("#in_"+ID).hide();
$("#out_"+ID).hide();
$("#receipt_"+ID).hide();
$("#ref_input_"+ID).show();
$("#in_input_"+ID).show();
$("#out_input_"+ID).show();
$("#receipt_input_"+ID).show();
}).change(function()
{
var ID=$(this).attr('id');
var ref=$("#ref_input_"+ID).val();
var inn=$("#in_input_"+ID).val();
var out=$("#out_input_"+ID).val();
var receipt=$("#receipt_input_"+ID).val();
var dataString = 'id='+ ID +'&in='+inn+'&out='+out+'$receipt='+receipt;
//$("#ref_"+ID).html('<img src="load.gif" />'); // Loading image

if(ref.length>0)
{

$.ajax({
type: "POST",
url: "table_edit_ajax.php",
data: dataString,
cache: false,
success: function(html)
{
//window.location.replace("http://www.domain.com"); JUST TESTING
$("#ref_"+ID).html(ref);
$("#in_"+ID).html(inn);
$("#out_"+ID).html(out);
$("#receipt_"+ID).html(receipt);
}
});
}
else
{
alert('Enter something.');
}

});

// Edit input box click action
$(".editbox").mouseup(function()
{
return false
});

// Outside click action
$(document).mouseup(function()
{
$(".editbox").hide();
$(".text").show();
});

});
</script>

So when I click on said row, it hides the text and displays the input box. I can then edit the value in the input box, click outside and the changes are made. However it isn't updating the database.

Here's table_edit_ajax.php:

<?php
include ('../includes/global.php');
if($_POST['id'])
{
$id=mysql_escape_string($_POST['id']);
$ref=mysql_escape_string($_POST['ref']);
$in=mysql_escape_string($_POST['in']);
$out=mysql_escape_string($_POST['out']);
$receipt=mysql_escape_string($_POST['receipt']);
$sql = "UPDATE weighbridge SET weighrbidge_ref='$ref', weighbridge_in='$in', weighbridge_out='$out', weighbridge_receipt=$receipt WHERE weighbridge_id='$id'";
$result = mysql_query($sql) or die(mysql_error());
}
?>

I apologise that this post is rather long but I thought it'd be helpful to understand what's going on and maybe useful to others wanting to achieve the same thing.

Thanks

[Fotiman]

The values are escaped in the table_edit php file.

That won't help in the case that one of the strings contains something that breaks your dataString (in other words, it would arrive at the server already broken). Imagine a case where one of the strings contains an ampersand.

Glad it's working again. :)