Antonio D Antonio D - 5 months ago 25
jQuery Question

JQuery in-line edit of HTML select element populated by MySQL table. Contenteditable

I have some JQuery code that allows in-line editing of a MySQL table on the fly without a submit by the user. It uses JQuery and AJAX calls to update the database. It works great for data within the element, however when I want to update something within the element I can not seem to get selected="selected" to be invoked when I chose that option. When reviewing the SQL that AJXA is creating, all of the the options that are available are in the SQL statement submitted.

What am I doing wrong or do I need to take another route? Here is a reduced version of my code. I hope it is enough to understand what I'm trying to accomplish. I appreciate any help or direction that you can provide.

I have a web page (members.php) that makes a call to a MySQL table (members_v30) to retrieve member data and another call to get options from a member_credentials table to populate field to show and edit if needed a member's credentials.

<?php
//Create DB Object
$db = new Database;
//Query
$query = "SELECT * from members_v30 ORDER BY last_name ASC";
//Run Query
$members = $db->select($query);
?>

<?php
//Query
$query = "SELECT * from member_credentials ORDER BY credential ASC";
//Run Query
$credentials = $db->select($query);
?>


The population of the table and the member's credentials works great. I can edit anything within the elements. I also have code that will chose the proper option based on an equal condition that matches the DB table. (Note: I may be making the code in this question to detailed buy showing the credential loop as I don't think it has anything to do with the problem. I thinks it associated with the contenteditable and select.)

<?php while($row = $members->fetch_assoc()) : ?>

<tr class="table-row">
<!-- edit saveToDatabase(this,'xxxxxxxx') -->
<td contenteditable="true" onBlur="saveToDatabase(this,'last_name','<?php echo $row['id']; ?>')" onClick="showEdit(this);"><?php echo $row['last_name']; ?></td>
<td contenteditable="true" onBlur="saveToDatabase(this,'first_name','<?php echo $row['id']; ?>')" onClick="showEdit(this);"><?php echo $row['first_name']; ?></td>
<td contenteditable="true" onBlur="saveToDatabase(this,'initial','<?php echo $row["id"]; ?>')" onClick="showEdit(this);"><?php echo $row["initial"]; ?></td>
<td contenteditable="true" onBlur="saveToDatabase(this,'suffix','<?php echo $row["id"]; ?>')" onClick="showEdit(this);"><?php echo $row["suffix"]; ?></td>

<td>
<select name="code1" contenteditable="true" onChange="saveToDatabase(this,'code1','<?php echo $row["id"]; ?>')" onClick="showEdit(this);">
<?php mysqli_data_seek($credentials,0); ?>
<?php while($row2 = $credentials->fetch_assoc()) : ?>
<option value="<?php echo $row2['credential']; ?>" <?php if ($row2['credential'] == $row['code1']) echo 'selected="selected"'; ?> ><?php echo $row2['credential']; ?></option>
<?php endwhile; ?>
</select>
<?php //echo $row["code1"]; ?>
</td>
</tr>
<?php endwhile; ?>


My problem is when I select another option I don't seem to get the selected="selected" attribute and then my JQuery/AJAX does not pass off the correct query to the DB.

Once agiain, this only happens in . works great.

Here's my JavaScript (edit.js)...

function showEdit(editableObj) {
$(editableObj).css("background","#FFF");
}



function saveToDatabase(editableObj,column,id) {
$(editableObj).css("background","#FFF url(loaderIcon.gif) no-repeat right");
$.ajax({
url: "saveedit.php",
type: "POST",
data:'column='+column+'&editval='+editableObj.innerHTML+'&id='+id,
success: function(data){
$(editableObj).css("background","#FDFDFD");
}
});
}


And my savedit.php

<?php include 'libraries/Database.php'; ?>

<?php
//Create DB Object
$db = new Database;
//Query
$query = "UPDATE members_v30 set " . $_POST["column"] . " = '".$_POST["editval"]."' WHERE id=".$_POST["id"];
//Run Query
//echo $query ; // Will be shown in Chrome Developer Tools
$update_members = $db->update_members($query);
?>


Using Chrome's Dev. tools' Network - Preview here is what I get

UPDATE members_v30 set code1 = '
ABAAR
ABHM
APRN
' WHERE id=1

So you can see every option is being passed.

Any input, criticism or ?? will be appreciated. Thank you in advance.

Answer

I got it working. I'm not sure if this is the best way to do it but it is working.

I'll post the code and explain what I have done....

Main page with the select pull down menu.

         <td>
                <select id=code1_<?php echo $row['id']; ?> onchange="getcode1(this,'<?php echo $row['id']; ?>')">
                    <option value=""></option>
                    <?php mysqli_data_seek($credentials,0); ?>
                    <?php while($row2 = $credentials->fetch_assoc()) : ?>
                        <option value="<?php echo $row2['credential']; ?>" <?php if ($row2['credential'] == $row['code1']) echo 'selected="selected"'; ?> ><?php echo $row2['credential']; ?></option>
                    <?php endwhile; ?>
                </select>
            </td>

onchange, I am passing the code1 selected and the members ID.

I have done away with contenteditable and now using the select element's id to represent the values passed to the JavaScript getcode1 function.

Please note that this page has many members listed and this is why i'm adding a member's ID to the select id since I need each select id to be different. If I didn't do this, the select id would keep the first value when I would pass it to the JavaScript.

I'm also looping through a credential table and if the option value matches a members code, I set selected="selected".

And here's my Javascript

    function getcode1(value,member_id) {

    var value = $('#code1_'+member_id).val();

            $.ajax({
                type: "POST",
                url: "save_code1.php",
                data:'member_code1='+value+'&member_id='+member_id,
                success: function(data){
                    $('form#submit').hide(function(){$('div.success').fadeIn();});

                }
            });
     return true;
     };

And here's the save_code1.php

  <?php include 'libraries/Database.php'; ?>

  <?php 
  //Create DB Object
  $db = new Database;
  ?>

  <?php
  $id = $_POST['member_id'];
  $code1 = $_POST['member_code1'];
  $query = "UPDATE members_v30 SET code1 = '$code1' where id =".$id;
  $update_members = $db->update_members($query);
  ?>

As I mentioned before, it's working, but it's probably not the best way to do it. I may revisit it, but for now I'm moving onto my next problem.