JQuery .ajax() post to php database query

Question

I see a lot of questions like this, however, I am having trouble even getting into the .ajax() function. I had a previous issue resolved here: Accessing clicked cell instead of all cells using JQuery and Ajax My follow-up was never responded to, though. Instead of posting code again I'll leave it there (unless told otherwise). Can anyway help me figure out 1: get the var field and var text to reflect my changes. and 2: how to access the id of the tr since it's a variable. How do I set that inside .parent()? Thanks.

EDIT:

Here is my script:

<script type="text/javascript">
    $(document).ready(function()
    {
        $(".edit_td").click(function()
        {
            $(this).children(".text").hide();
            $(this).children(".editbox").show();

        }).change(function()
            {
                alert("working");
                var id=$(this).parent();
                var field=$("#input_"+ id).val();
                var text=$(this).children(".editbox").val();

                var dataString = 'id='+ id +'&field='+ field +'&text='+ text;
                //$("#first_"+ID).html('<img src="load.gif" />'); // Loading image

                if(input != text)
                {
                    $.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.');
                }
            });

        // Edit input box click action
        $(".editbox").mouseup(function() 
        {
            return false
        });

        // Outside click action
        $(document).mouseup(function()
        {
            $(".editbox").hide();
            $(".text").show();
        });

    });
    </script>

Here is my php to display the table:

public function displayTable($table)
{
  //connect to DB
  $con = mysqli_connect(DB_HOST, DB_USER, DB_PASS, DB_NAME);

echo "<table id='table' border='1'>";   //start an HTML table

$dbtable = $table;
$fields =array();
$result = mysqli_query($con, "SHOW COLUMNS FROM ".$dbtable);

//fill fields array with fields from table in database
while ($x = mysqli_fetch_assoc($result))
{
    $fields[] = $x['Field'];
}

$fieldsnum = count($fields);    //number of fields in array

//create table header from dbtable fields
foreach ($fields as $f)
{
    echo "<th>".$f."</th>";
}

//create table rows from dbtable rows
$result = mysqli_query($con, "SELECT * FROM ".$dbtable);

while ($row = mysqli_fetch_array($result))
{
    $rowid = $row[$fields[0]];
    echo "<tr class='edit_tr' id='".$rowid."'>";
    foreach ($fields as $f) 
    { 
        echo "<td class='edit_td'><span id='span_".$rowid."' class='text'>".$row[$f]."</span>
        <input type='text' value='".$row[$f]."' class='editbox' id='input_".$rowid."'/> </td>"; 
    }
    $rowid++;
    echo "</tr>";
}

echo "</table>";    //close the HTML table

//close connection
mysqli_close($con);

}

I am not even sure I'm getting into my .change() function, but once I do I'd like to set var id to the row id which corresponds to my database id (which will allow me to run my update query by my primary key). The next thing id like to do is set var field to the column header of the td selected so I know what field from my database to select. The next thing i'd like is my var text to reflect the changed text from the textbox to send to the update query.

Now, I'd like to be able to send that to table_edit_ajax.php as a POST and run my update query. I am just really confused as to how to do all of that. I am very new to ajax and javascript, obviously.

EDIT: file: table_edit_ajax.php

<?php
//connect to DB
$con = mysqli_connect(DB_HOST, DB_USER, DB_PASS, DB_NAME);

echo 'in table_edit';

$id = mysqli_escape_String($con, $_POST['id']);
$table = "owners";
$field = $_POST['field'];
$text = mysqli_escape_String($con, $_POST['text']);
$query = "UPDATE owners SET ".$field."='".$text."' WHERE id = '".$id."'";
mysqli_query($con, $query);

//close connection
mysqli_close($con);

?>

Answer 1

First of all, you're going to want to bind your change event to your input box, not its parent td, since I assume you want your function to run only when the input value changes. You'll also want to change the td binding from click to mouseup and add a stopPropagation call to avoid triggering the mouseup event on your document.

$(".edit_td").mouseup(function(e)
        {
            $(this).children(".text").hide();
            $(this).children(".editbox").show();
            e.stopPropagation();

        }).children('.editbox').change(function() {

To get the id of the tr:

var id = $(this).closest('tr').attr('id');

To get the text:

var text = $(this).val();

And the best way to go about getting the field is probably to assign a data attribute to your input box that represents the column header. In your PHP, as you're generating your tds:

foreach ($fields as $f) 
{ 
    echo "<td class='edit_td'><span id='span_".$rowid."' class='text'>".$row[$f]."</span>
    <input type='text' value='".$row[$f]."' class='editbox' id='input_".$rowid."' data-field='".$f."'/> </td>"; 
}

Then in your Javascript:

var field = $(this).data('field');

Furthermore, your query string may not be encoded correctly. It's best to provide the data as an object: eg. {id:id, field:field, text:text}, and let jQuery handle the url encoding for you.

And just a heads up, I noticed you're setting the id of your spans to $rowid. In doing this, your span id's will not be unique and may present a problem down the line. If you need to give them an id, try something like id='span_".$rowid."."-".$f."'.