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

Question

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);
}
?>

CodeCaster · Accepted Answer · 2011-10-16 12:24:16Z

1

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. ;)

edited Oct 16, 2011 at 12:24

answered Oct 16, 2011 at 12:17

CodeCaster

153k24 gold badges237 silver badges287 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

bammab Over a year ago

Do I have to change anything to the jquery?

CodeCaster Over a year ago

Yes, otherwise the input elements aren't posted to the script.

bammab Over a year ago

I added my updated code with your changes but it doesnt save the data?

CodeCaster Over a year ago

Well, good luck debugging then. :) I see a question mark in your comment, but no question.

CodeCaster Over a year ago

That's rubbish, the amount of columns has nothing to do with this. Are there any SQL errors when you call the script?

|

Amr · Accepted Answer · 2017-04-07 12:26:44Z

0

A better way is to add the html attribute "contenteditable" to the table cells to allow inline editing on clicking cell view and then attach a function the onblur event to update the database using ajax.

answered Apr 7, 2017 at 12:26

Amr

6011 gold badge13 silver badges16 bronze badges

Collectives™ on Stack Overflow

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

edited code

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

edited code

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related