Using only one function to update different columns of a table in database

Question

id  car         make          sales
 1  panamera    porsche       100 
 2  italia      ferrari       200
 3  volante     astonmartin   300
 4  avantador   lamborghini   400
 5  slk         mercedes      500

So guys, i have this simple table in my database. And i'm gonna echo this table in a while loop.

<ul>
<?php  
$query = "SELECT * FROM inplace LIMIT 0, 6";    
$result = mysql_query($query) or die ('Query couldn\'t be executed');  
while ($row = mysql_fetch_assoc($result)) {
echo '<li class="editable" id="'.$row['id'].'">'.$row['car'].'</li>';
echo '<li class="editable2" id="'.$row['id'].'">'.$row['make'].'</li>'; 
}
?>
</ul>

The idea is to update this table using jQuery in-place editor. So here is the code-

$(document).ready(function() 
{
$(".editable").bind("dblclick", replaceHTML);
$(".editable2").bind("dblclick", replaceHTML2);
$(".btnSave, .btnDiscard").live("click", handler);

function handler()
    {

        if ($(this).hasClass("btnSave"))
            {

                var str = $(this).siblings("form").serialize();
                $.ajax({
                        type: "POST",
                        async: false,
                        url: "handler.php",
                        data: str,
                }); 

            }

    } 

function replaceHTML()
    {
        var buff = $(this).html()
        .replace(/"/g, """);
        $(this).addClass("noPad")
                .html("<form><input type=\"text\" name=\"car\" value=\"" + buff + "\" /> <input type=\"text\" name=\"buffer\" value=\"" + buff + "\" /><input type=\"text\" name=\"id\" value=\"" + $(this).attr("id") + "\" /></form><a href=\"#\" class=\"btnSave\">Save changes</a> <a href=\"#\" class=\"btnDiscard\">Discard changes</a>")
                .unbind('dblclick', replaceHTML);   


    }

    function replaceHTML2()
    {
        var buff = $(this).html()
        .replace(/"/g, "&quot;");
        $(this).addClass("noPad")
                .html("<form><input type=\"text\" name=\"make\" value=\"" + buff + "\" /> <input type=\"text\" name=\"buffer\" value=\"" + buff + "\" /><input type=\"text\" name=\"id\" value=\"" + $(this).attr("id") + "\" /></form><a href=\"#\" class=\"btnSave\">Save changes</a> <a href=\"#\" class=\"btnDiscard\">Discard changes</a>")
                .unbind('dblclick', replaceHTML);   


    }

}
); 

This is an in-place edit code i got it from the internet and i just tore it down to basic level just to understand the codes. Dont worry bout the update query, its is in "handler.php". The problem here is, i have to write separate function for each column. In this case, i have to write a separate function to update 'car' column, separate function to update 'make' column and goes on. I dont think this is the correct method to do. Because, here i just have 3 columns. What if i had 10 to 15 columns? I dont think writing 15 functions is the correct method. And "$(this).html()" takes only one form's value. Please help.

Answer 1

Modify your PHP script to generate HTML similar to this:

<table>
<tbody>
    <tr data-id="1">
        <td data-col="car">panamera</td>
        <td data-col="make">porsche</td>
        <td data-col="sales">100</td>
    </tr>
</tbody>
</tbody>

The id of the database row corresponding to each HTML table row is specified with data-id in each tr. And each td specifies to which DB column it corresponds using data-col.

Using these information you can pass enough information back to the PHP script that updates the database. So essentially when a cell is clicked, you can get its column name using:

$(this).data('col')

And you can get the ID for its row using:

$(this).parent('tr').data('id')

Then you can pass these to the PHP page that updates the DB.

EDIT 1:

You can use ul/li instead of table/tr/td. You can also use class=car, class=make, etc. instead of data-col='car', data-col='make', etc. if you are using an older version of jQuery that does not support HTML5-style data- attributes.

EDIT 2: Complete solution

Change your while loop to this:

while ($row = mysql_fetch_assoc($result)) {
    echo '<li class="editable" data-id="'.$row['id'].'" data-col="car">'.$row['car'].'</li>';
    echo '<li class="editable" data-id="'.$row['id'].'" data-col="make">'.$row['make'].'</li>'; 
}

As you can see we store the database row ID in data-id and the database column name in data-col.

Now with this setup you would only need one handler:

function replaceHTML()
{
    var rowId   = $(this).data('id');
    var colName = $(this).data('col');
    var buff = $(this).html().replace(/"/g, """); // Are you sure you need this?
    $(this).addClass("noPad")
            .html("<form><input type=\"text\" name=\"" + colName + "\" value=\"" + buff + "\" /> <input type=\"text\" name=\"buffer\" value=\"" + buff + "\" /><input type=\"text\" name=\"id\" value=\"" + rowId + "\" /></form><a href=\"#\" class=\"btnSave\">Save changes</a> <a href=\"#\" class=\"btnDiscard\">Discard changes</a>")
            .unbind('dblclick', replaceHTML);   
}

$(".editable").bind("dblclick", replaceHTML);

Finally, always try to write readable code! Please! :)

EDIT 3: JSFiddle

Please see this live solution. It shows how you can get the column name and row ID. You just have to adopt it to work with your PHP script.