how to edit table column data with jquery/ajax and save to mysql


Problem :

I am using the following code to edit a table of data on the fly for an admin area. It works well with only two columns, but when I add more I am not able to edit the data and have it save to mysql. Can someone show me how to add 5 more columns that will work. Here is a demo and where the code originates DEMO

<?php include('db.php'); ?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Live Table Edit</title>
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.5/jquery.min.js"></script>
<script type="text/javascript">
$(document).ready(function() {
    $(".edit_tr").click(function() {
        var ID = $(this).attr('id');
        $("#first_" + ID).hide();
        $("#last_" + ID).hide();
        $("#first_input_" + ID).show();
        $("#last_input_" + ID).show();
    }).change(function() {
        var ID = $(this).attr('id');
        var first = $("#first_input_" + ID).val();
        var last = $("#last_input_" + ID).val();
        var dataString = 'id=' + ID + '&firstname=' + first + '&lastname=' + last;
        $("#first_" + ID).html('<img src="load.gif" />');

        if (first.length && last.length > 0) {
            $.ajax({
                type: "POST",
                url: "table_edit_ajax.php",
                data: dataString,
                cache: false,
                success: function(html) {

                    $("#first_" + ID).html(first);
                    $("#last_" + ID).html(last);
                }
            });
        }
        else {
            alert('Enter something.');
        }

    });

    $(".editbox").mouseup(function() {
        return false
    });

    $(document).mouseup(function() {
        $(".editbox").hide();
        $(".text").show();
    });

});
</script>
<style>
body
{
    font-family:Arial, Helvetica, sans-serif;
    font-size:14px;
}
.editbox
{
    display:none
}
td
{
    padding:7px;
}
.editbox
{
    font-size:14px;
    width:270px;
    background-color:#ffffcc;

    border:solid 1px #000;
    padding:4px;
}
.edit_tr:hover
{
    background:url(edit.png) right no-repeat #80C8E5;
    cursor:pointer;
}


th
{
    font-weight:bold;
    text-align:left;
    padding:4px;
}
.head
{
    background-color:#333;
    color:#FFFFFF

}
</style>
</head>

<body>
<table width="100%">
<tr class="head">
<th>First Name</th><th>Last Name</th>
</tr>
<?php
$sql=mysql_query("select * from fullnames");
$i=1;
while($row=mysql_fetch_array($sql))
{
$id=$row['id'];
$firstname=$row['firstname'];
$lastname=$row['lastname'];

if($i%2)
{
?>
<tr id="<?php echo $id; ?>" class="edit_tr">
<?php } else { ?>
<tr id="<?php echo $id; ?>" bgcolor="#f2f2f2" class="edit_tr">
<?php } ?>
<td width="50%" class="edit_td">
<span id="first_<?php echo $id; ?>" class="text"><?php echo $firstname; ?></span>
<input type="text" value="<?php echo $firstname; ?>" class="editbox" id="first_input_<?php echo $id; ?>" />
</td>
<td width="50%" class="edit_td">
<span id="last_<?php echo $id; ?>" class="text"><?php echo $lastname; ?></span> 
<input type="text" value="<?php echo $lastname; ?>"  class="editbox" id="last_input_<?php echo $id; ?>"/>
</td>
</tr>

<?php
$i++;
}
?>
</table>
</body>
</html>

Here is the code for the ajax file table_edit_ajax.php

<?php
include("db.php");
if($_POST['id'])
{
$id=mysql_escape_String($_POST['id']);
$firstname=mysql_escape_String($_POST['firstname']);
$lastname=mysql_escape_String($_POST['lastname']);
$sql = "update fullnames set firstname='$firstname',lastname='$lastname' where id='$id'";
mysql_query($sql);
}
?>

edited code

<?php include('db.php'); ?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Live Table Edit</title>
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.5/jquery.min.js"></script>
<script type="text/javascript">
$(document).ready(function() {
    $(".edit_tr").click(function() {
        var ID = $(this).attr('id');
        $("#first_" + ID).hide();
        $("#last_" + ID).hide();
        $("#othercolumn3_" + ID).hide();
        $("#othercolumn4_" + ID).hide();
        $("#othercolumn5_" + ID).hide();
        $("#first_input_" + ID).show();
        $("#last_input_" + ID).show();
        $("#othercolumn3_input_" + ID).show();
        $("#othercolumn4_input_" + ID).show();
        $("#othercolumn5_input_" + ID).show();
    }).change(function() {
        var ID = $(this).attr('id');
        var first = $("#first_input_" + ID).val();
        var last = $("#last_input_" + ID).val();
        var dataString = 'id=' + ID + '&firstname=' + first + '&lastname=' + last + '&othercolumn3=' + othercolumn3 + '&othercolumn4=' + othercolumn4 + '&othercolumn5=' + othercolumn5;
        $("#first_" + ID).html('<img src="load.gif" />');

        if (first.length && last.length && othercolumn3.length && othercolumn4.length && othercolumn5.length > 0) {
            $.ajax({
                type: "POST",
                url: "table_edit_ajax.php",
                data: dataString,
                cache: false,
                success: function(html) {

                    $("#first_" + ID).html(first);
                    $("#last_" + ID).html(last);
                    $("#othercolumn3_" + ID).html(othercolumn3);
                    $("#othercolumn4_" + ID).html(othercolumn4);
                    $("#othercolumn5_" + ID).html(othercolumn5);
                }
            });
        }
        else {
            alert('Enter something.');
        }

    });

    $(".editbox").mouseup(function() {
        return false
    });

    $(document).mouseup(function() {
        $(".editbox").hide();
        $(".text").show();
    });

});
</script>
<style>
body
{
    font-family:Arial, Helvetica, sans-serif;
    font-size:14px;
}
.editbox
{
    display:none
}
td
{
    padding:7px;
}
.editbox
{
    font-size:14px;
    width:270px;
    background-color:#ffffcc;

    border:solid 1px #000;
    padding:4px;
}
.edit_tr:hover
{
    background:url(edit.png) right no-repeat #80C8E5;
    cursor:pointer;
}


th
{
    font-weight:bold;
    text-align:left;
    padding:4px;
}
.head
{
    background-color:#333;
    color:#FFFFFF

}
</style>
</head>

<body>
<table width="100%">
<tr class="head">
<th>1</th><th>2</th><th>3</th><th>4</th><th>5</th>
</tr>
<?php
$sql=mysql_query("select * from offers");
$i=1;
while($row=mysql_fetch_array($sql))
{
$id=$row['id'];
$firstname = $row['one'];
$lastname = $row['two'];
$othercolumn3 = $row['three'];
$othercolumn4 = $row['four'];
$othercolumn5 = $row['five'];
if($i%2)
{
?>
<tr id="<?php echo $id; ?>" class="edit_tr">
<?php } else { ?>
<tr id="<?php echo $id; ?>" bgcolor="#f2f2f2" class="edit_tr">
<?php } ?>
<td width="50%" class="edit_td">
<span id="first_<?php echo $id; ?>" class="text"><?php echo $firstname; ?></span>
<input type="text" value="<?php echo $firstname; ?>" class="editbox" id="first_input_<?php echo $id; ?>" />
</td>
<td width="50%" class="edit_td">
<span id="last_<?php echo $id; ?>" class="text"><?php echo $lastname; ?></span> 
<input type="text" value="<?php echo $lastname; ?>"  class="editbox" id="last_input_<?php echo $id; ?>"/>
</td>
<td width="50%" class="edit_td">
<span id="othercolumn3_<?php echo $id; ?>" class="text"><?php echo $othercolumn3; ?></span> 
<input type="text" value="<?php echo $othercolumn3; ?>"  class="editbox" id="othercolumn3_input_<?php echo $id; ?>"/>
</td>
<td width="50%" class="edit_td">
<span id="othercolumn4_<?php echo $id; ?>" class="text"><?php echo $othercolumn4; ?></span> 
<input type="text" value="<?php echo $othercolumn4; ?>"  class="editbox" id="othercolumn4_input_<?php echo $id; ?>"/>
</td>
<td width="50%" class="edit_td">
<span id="othercolumn5_<?php echo $id; ?>" class="text"><?php echo $othercolumn5; ?></span> 
<input type="text" value="<?php echo $othercolumn5; ?>"  class="editbox" id="othercolumn5_input_<?php echo $id; ?>"/>
</td>
</tr>
<?php
$i++;
}
?>
</table>
</body>
</html>

ajax

<?php
include("db.php");
if($_POST['id'])
{
$id=mysql_escape_String($_POST['id']);
$firstname=mysql_escape_String($_POST['firstname']);
$lastname=mysql_escape_String($_POST['lastname']);
$othercolumn3 = mysql_escape_String($_POST['othercolumn3']);
$othercolumn4 = mysql_escape_String($_POST['othercolumn4']);
$othercolumn5 = mysql_escape_String($_POST['othercolumn5']);
$sql = "update offers set firstname='$firstname',lastname='$lastname', othercolumn3='$othercolumn3', othercolumn4='$othercolumn4, othercolumn5='$othercolumn5' where id='$id'";
mysql_query($sql);
}
?>


Solution :

You mean this?

$firstname=mysql_escape_String($_POST['firstname']);
$lastname=mysql_escape_String($_POST['lastname']);
$othercolumn1 = mysql_escape_String($_POST['othercolumn1']);
...
$othercolumn5 = mysql_escape_String($_POST['othercolumn5']);
$sql = "update fullnames set firstname='$firstname',lastname='$lastname', othercolumn1='$othercolumn1', ..., othercolumn5='$othercolumn5' where id='$id'";

You'd have to create the appropriate input elements and add the required code to the jquery that's calling the script:

var first = $("#first_input_" + ID).val();
var last = $("#last_input_" + ID).val();
var othercolumn1 = $("#othercoumn1_input_" + ID).val();
...
var othercolumn5 = $("#othercoumn5_input_" + ID).val();
var dataString = 'id=' + ID + '&firstname=' + first + '&lastname=' + last + '&othercolumn1=' + othercolumn1 + ... + '&othercolumn5=' + othercolumn5;
$("#first_" + ID).html('<img src="load.gif" />');

if (first.length && last.length > 0) {
    $.ajax({
        type: "POST",
        url: "table_edit_ajax.php",
        data: dataString,
        cache: false,
        success: function(html) {
            $("#first_" + ID).html(first);
            $("#last_" + ID).html(last);
            $("#othercolumn1_" + ID).html(othercolumn1);
            ...
            $("#othercolumn5_" + ID).html(othercolumn5);
        }

But you should be able to sort things like this out for yourself. ;)


Mysql Tutorials

Mysql Howto..

How to select only that what matches all parameters in many-to-many

MySql: How to turn on the general query log via my.cnf?

How to change time zone of error log file of MySQL?

How to sort mysql result but show specific result first

how to insert HTML code into a table

How to wait for MySQL To Update in VB.NET?

SELECT UNION and ORDER BY in mysql.. how to?

How to modifie the standard path of MySQL Workbench which points to where Backups shall be stored?

mysql query to show what doesnt match

MySQL - How to use ROLLUP and ORDER BY in same query

How to scroll through mysql database in java

How can I get MySQL records from past 7 days, but only days that have a record, and return each day as a record?

how to import large datasets to mysql - the right way?

how to automatically update a column in another table when an insertion is made in a different column in a different table in MySql using java

How to skip a row from sql?

How do I express a “has many through” relationship in Entity Framework 5?

php mysql How to Run a Subquery in the right way for this table

How consider NULL as the MAX date instead of ignoring it in MySQL?

Dynamic JavaScript slideshow

MySQL Row Ordering: How to set up and use a `position` column for row ordering?

How to pass the value of javascript into a PHP variable and stored into mysql database? [duplicate]

how to use smsserver.java from smslib? I have read the docs

Basic mysql question - how to search by time?

how to create custom function in excel to grab mysql data?

how to pass parameter to trigger in mysql

Neo4j - How to emulate MySQL multiple schema deployment in Neo4j

How to use jooq to express mysql REGEXP?

How to store american formated date into MySQL

how to create a trigger for specific database in MySql?

How to select every row N times in MySql table?