Enable user to double-click on table texts to type changes; DB is updated from these changes

Question

The table's data in CDs.php is pulled from a DB. Clicking on the table headers sorts the columns. Now, I want this page (CDs.php) to be editable when the user double-clicks on something in the table rows to type their changes. For example, the user can double-click on "1999 Grammy Nominees" and change it to "2014 Grammy Winners". This change on the site then updates the title inside the DB. I'm not sure how to do this...any ideas/suggestions on how I should go about this? Thanks in advance.

Note: I want the user to be able to type their changes...no drop-down select options or anything like that.

Note: I only want Title, Artist, and Price to be editable.

Note: I got the table columns to be sortable from --> http://www.dougv.com/2009/06/13/sorting-your-mysql-results-set-in-php-using-jquery-and-a-more-traditional-approach/

Code:

<!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=utf-8" />
        <title>Sortable Table Columns</title> 
        <link href="../demo.css" rel="stylesheet" type="text/css" />
        <script type="text/javascript" src="jquery-1.3.1.min.js"></script>
        <script type="text/javascript" src="jquery.tablesorter.min.js"></script>
        <script type="text/javascript">
            $(document).ready(function() {
                $("#sortedtable").tablesorter({ sortlist: [0,0] });
            });
        </script>
        <style type="text/css">
            #sortedtable thead th {
                color: #00f;
                font-weight: bold;
                text-decoration: underline;
            }
        </style>
    </head>
    <body>
            <?php
            include("include.php"); 
            switch($_GET['c']) {
                case "1":
                    $col = "Title"; 
                    break;
                case "2":
                    $col = "Artist"; 
                    break;
                case "3":
                    $col = "Country"; 
                    break;
                case "4":
                    $col = "Company"; 
                    break;
                case "5":
                    $col = "Price"; 
                    break;                  
                default:
                    $col = "Year"; 
            }

            if($_GET['d'] == "1") {
                $dir = "DESC";
            }
            else {
                $dir = "ASC";
            }

            $dbc = mysql_connect($host, $user, $pass);
            if (!$dbc) {
                echo "Cannot connect to db server";
            }
            elseif(!mysql_select_db("database")) {
                echo "Cannot select database";
            }
            else { 
                if(!$rs = mysql_query("SELECT * FROM CD ORDER BY Title")) { 
                    echo "Cannot parse query";
                }
                elseif(mysql_num_rows($rs) == 0) {
                    echo "No records found";
                }
                else {                             
                    echo "<table id=\"sortedtable\" border='3' class=\"bordered\" cellspacing=\"0\">\n";
                    echo "<thead>\n<tr>";
                    echo "<th bgcolor='#FFFF00'>Title</th>"; 
                    echo "<th bgcolor='#FFFF00'>Artist</th>"; 
                    echo "<th bgcolor='#FFFF00'>Country</th>"; 
                    echo "<th bgcolor='#FFFF00'>Company</th>"; 
                    echo "<th bgcolor='#FFFF00'>Price</th>"; 
                    echo "<th bgcolor='#FFFF00'>Year</th>"; 
                    echo "</tr>\n</thead>\n";
                    while($row = mysql_fetch_array($rs)) {                  
                        echo 
                        "<tr>
                            <td>$row[Title]</td>
                            <td>$row[Artist]</td>
                            <td>$row[Country]</td>
                            <td>$row[Company]</td>
                            <td>$row[Price]</td>
                            <td>$row[Year]</td>
                        </tr>\n";
                    }
                    echo "</table><br />\n";
                }
            } 
        ?>     
    </body>
</html>

CDs.php

enter image description here

Database:

enter image description here

Are these gigantic screenshots really necessary? Please, post the **minimal** amount needed to convey your problem. — tadman, Jul 09 '14 at 18:12

score 0 · Answer 1 · edited May 23 '17 at 10:32

You might want to take a look at: Change Label to Textbox on edit hyperlink click

I know it's for a label but it can easily be converted to your case. I'd personally use the "update 1" from the second answer.

To detect the change you'll probably want to detect when the user hits enter, therefore I suggest you take a look at this: Detect enter in input elements of a certain class When you've done that you can convert the input box back to a table cell and make an ajax call to a PHP page which then adds the value to the database.

Visit this jquery API page to learn more about the ajax call: http://api.jquery.com/jquery.get/

Lawrence Cherone · Answer 2 · 2014-07-09T18:41:29.927

You could use the contenteditable="true" HTML attribute, then on blur (focusing out) you send the value to PHP using AJAX. Ive put together a simple example. Its not complete but answers the question, it leaves you to implement it.

<?php 
if($_SERVER['REQUEST_METHOD'] == 'POST'){
    header('Content-Type: application/json');
    exit(json_encode(array(
        'result'=>'Received: '.$_POST['value'].' from AJAX, for row '.$_POST['row_id'].' column '.$_POST['column'],
    )));
}
?>
<!DOCTYPE HTML>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title></title>
<script src="http://code.jquery.com/jquery-1.11.0.min.js"></script>
<script>
jQuery(document).ready(function(){

    $.fn.editable = function(column){
        $(this).attr('contenteditable', true);

        $(this).blur(function () {
            var ajax = $.ajax({
                url: './',
                type: "POST",
                data: { row_id: $(this).parent().attr('id'), column:column, value: $(this).html()},
                dataType: "json"
            });
            ajax.done(function(data){
                $('#result').html(data.result);
            });

            $(this).attr('contenteditable', false);
        });
        return this;
    };
});
</script>
</head>

<body>

<div id="result"></div>

<table border="1" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#111111" width="100%" id="AutoNumber1">
  <tr id="row_id_1">
    <td width="33%" ondblclick="$(this).editable('col_a')">Value A</td>
    <td width="33%" ondblclick="$(this).editable('col_b')">Value B</td>
    <td width="34%" ondblclick="$(this).editable('col_c')">Value C</td>
  </tr>
</table>

</body>

</html>

Enable user to double-click on table texts to type changes; DB is updated from these changes

Code:

CDs.php

Database:

2 Answers2