How can I edit the results of a MySQL query inline using AJAX or something similar?

Question

I'm using PHP to inject a table, generated from a MySQL query, to a mixed HTML/PHP file, using a <div>. The <div> is declared in index.php like so: <div id="searchdiv"></div>.

And the content for the <div> is generated like so:

(1) Create the search box:

<form id="searchform">
<td class="master">Codigo o nombre cliente: <input type="text" name="box" onkeypress="return noenter()" /><input id="srchsubmit" type="submit" value="Buscar" onclick="showUser(this.form['box'].value);return false;" /><br /></td></form>

(2) Execute JavaScript:

function showUser(str){
    if (str==""){
        document.getElementById("searchdiv").innerHTML="";
        return;
        }
        if (window.XMLHttpRequest){
            // code for IE7+, Firefox, Chrome, Opera, Safari
            xmlhttp=new XMLHttpRequest();
            }
            else
            {
                // code for IE6, IE5
                xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
                }
                xmlhttp.onreadystatechange=function(){
                    if (xmlhttp.readyState==4 && xmlhttp.status==200){
                        document.getElementById("searchdiv").innerHTML=xmlhttp.responseText;
                        }
                        }
                        xmlhttp.open("GET","search.php?q="+str,true);
                        xmlhttp.send();
                        }

(3) Construct the result to inject in the <div>:

<?php

// Fetch the string from the search box

$q=$_GET["q"];

// Connection settings

$dbhost = "localhost";
$dbuser = "root";
$dbpass = "";
$dbname = "scorecard";

//Connect to MySQL Server

mysql_connect($dbhost, $dbuser, $dbpass);

//Select Database

mysql_select_db($dbname) or die(mysql_error());

// Fetch data

$sql="SELECT * FROM scoreboard WHERE codcliente = '".$q."' OR lower(nombre) LIKE '%".strtolower($q)."%'";
$result = mysql_query($sql);
if(mysql_num_rows($result) == 0){
  echo '<div align="center" style="background-color:#CCCCCC; font-weight:bold; color:#C0504D;">Record no existe.&nbsp;&nbsp;&nbsp;<input id="srchcreate" type="button" value="Crear" /></div>';
  return;
  }
//echo $result;

// Construct the table

echo "<table id='srchtable' class='srchtable'>";

// Construct the array

while($row = mysql_fetch_array($result)){
  echo "<tr>";
  echo "<td align='center'>" . $row['codcliente'] . "</td>";
  echo "<td align='left'>" . $row['nombre'] . "</td>";
//  echo "<td align='center'>" . $row['ejecutivo'] . "</td>";
//  echo "<td align='center'>" . $row['banca_as400'] . "</td>";
//  echo "<td align='center'>" . $row['banca_real'] . "</td>";
//  echo "<td align='right'>" . $row['ingresos'] . "</td>";
  echo "<td align='center'>" . $row['ciiu'] . "</td>";
//  echo "<td align='center'>" . $row['division'] . "</td>";
  echo "<td align='left'>" . $row['actividad'] . "</td>";
  echo "<td align='center' class='{$row['riesgo_industria']}'>" . $row['riesgo_industria'] . "</td>";
  echo "<td align='center' class='{$row['riesgo_cliente']}'>" . $row['riesgo_cliente'] . "</td>";
  echo "<td align='center'>" . $row['fecha'] . "</td>";
  echo "<td align='center'>" . $row['analista'] . "</td>";
  echo "<td align='center'>" . "<input id='edit' type='button' value='Editar' onclick='' />" . "</td>";
  echo "</tr>";
}
echo "</table>";

?>

The result looks something like this: Screenshot. My question is, how can I make the fields editable in-line when I click the "Editar" (edit) button? Maybe place an option input at the front of the <td> to select which row to edit? After I edit, how do I insert the changes back to MySQL? Thanks!

Answer 1

for your first question, you dont need the checkbox. If you dont mind jquery, you can use

$(this).closest('tr')

to get the row the edit button that was clicked is in. You can then replace the contents of each cell you want to edit with an input. Wrap your table in form tags and use ajax to submit.

Something to get you started...

$(document).ready(function(){
        $(".edit").click(function(){
            var tr = $(this).closest("tr");
            var submit = "<input type='submit' name='Submit' value='Submit' />";

            tr.find(".td").each(function(){
                var name = $(this).attr("title");
                var value = $(this).html();
                var input = "<input type='text' name='"+name+"' value='"+value+"' />";
                $(this).html(input);
            });

            tr.find(".button").html(submit);

        });
    });

Assuming your table/form looked something like:

<form id="form" name="form" action="#">
        <table>
            <tr class="row">
                <td class="td" title="first_name">John</td>
                <td class="td" title="last_name">Smith</td>
                <td class="button" title="button"><button class="edit">Edit</button></td>
            </tr>
            <tr class="row">
                <td class="td" title="first_name">Roger</td>
                <td class="td" title="last_name">Jones</td>
                <td class="button" title="button"><button class="edit">Edit</button></td>
            </tr>
        </table>
    </form>

You'd then need to do the ajax submit and remove the inputs/revert back to text.