Link with jQuery to Update MySQL

rsmith

I've got the following script that updates a set of columns in my database.

acknowledge.php

<?php
	if(isset($_POST['action'])){
        $field = $_POST['db_field'];
        $value = $_POST['db_value'];
        $ticket = $_POST['ticket'];
        $ack_date = date("Y_m_d");

    $link = mysql_connect("localhost", "dbadmin", "dbpw");
	mysql_select_db("dbname", $link);
	mysql_query("UPDATE table SET $field = '$value', ack_date = '$ack_date' WHERE id = '$ticket', $link);
	mysql_close($link);	
}
?>

What I'm trying to do is in my HTML table I have a link for each column that says whether or not an item has been acknowledged. If it has, show the date and user who did otherwise display a text link to allow for someone to acknowledge it.

<?php
      	  // Display acknowledgement data; if not acknowledged, display link to accept it
      		  if(empty($details['ack_by']) AND empty($details['ack_date'])){
	      		  echo "<center><a href='#' class='ack-link' db_field='ack_by' db_value='".$username."' ticket='".$id."' />Take Ownership</a></center>";
      		  }else{
	      		  echo $details['ack_by']." on ".$details['ack_date'];
      		  }
        ?>

What I want to happen is, if it's not acknowledged, clicking the link will do this without navigating away from the page. I figured ajax and jQuery would be the best for this so I wrote this:

<script type="text/javascript">
      function performAjaxSubmission() {
        $.ajax({
          url: 'acknowledge.php',
          method: 'POST',
          data: {
            action: 'save',
            field: $(this).attr("db_field"), 
            val: $(this).attr("db_value"),
            ticket: $(this).attr("ticket_id")
          },
          success: function() {
            alert("success!");
          }
        });
        return false; // <--- important, prevents the link's href (hash in this example) from executing.
      }

  jQuery(document).ready(function() {
    $(".ack-link").click(performAjaxSubmission);
  });
  </script>

What happens is clicking the link will generate the "Success!" prompt but the db isn't actually updated.

Derokorian

        mysql_query("UPDATE table SET $field = '$value', ack_date = '$ack_date' WHERE id = '$ticket', $link); 
        mysql_close($link);     

    } 
?>

Missing a closing double quote to end the query string. Since this creates a parse error that doesn't change headers, you are still getting a Status of 200 so the success function is being triggered. When you have problems like this its best to alert the data returned on success like so:

success: function(data) {
            alert(data);
          }

rsmith

Derokorian;10998659 wrote:
Missing a closing double quote to end the query string.

Good find, I totally missed that.

Derokorian;10998659 wrote:
Since this creates a parse error that doesn't change headers, you are still getting a Status of 200 so the success function is being triggered. When you have problems like this its best to alert the data returned on success like so:
success: function(data) {
            alert(data);
          }

I added that to the script and now I get a blank alert window and db still isn't updated.

I know I'm fighting an uphill battle being new to the jQuery and Ajax game but I work best by the whole "trial by fire" method. 😃

bradgrafelman

One problem I see is this:

        $field = $_POST['db_field']; 
        $value = $_POST['db_value'];

Your AJAX function doesn't POST any items named 'db_field' or 'db_value'.

EDIT: This should be generating an E_NOTICE level error message, by the way. Do you have error_reporting set to E_ALL (or better) and display_errors (or log_errors, which would probably be more helpful in this situation) set to On?

rsmith

data: {
     action: 'save',
     field: $(this).attr("db_field"), 
     val: $(this).attr("db_value"),
     ticket: $(this).attr("ticket_id")
          },

Doesn't this part of the ajax post the db_field and db_value and ticket_id?

Derokorian

Also note, you say that you get nothing back and that the DB still isn't updated. You should be checking if the query was successful, and returning that info:

$res = mysql_query($query);
if( $res === FALSE ) {
   echo "Update failed.\n".mysql_error();
} else {
   echo "Update successful.";
}

Or instead of echoing (which would then show up in the alert window) you could turn on error logging, and make your own error:

if( $res === FALSE ) {
   user_error('Update failed in '.__FILE__.' mysql said: '.mysql_error(), E_USER_ERROR);
}

ALSO, you are using the deprecated mysql_ library, you should instead use mysqli (the i stands for improved) or PDO. See this page for more info: http://us.php.net/manual/en/mysqlinfo.api.choosing.php

Derokorian

     action: 'save',
     field: $(this).attr("db_field"), 
     val: $(this).attr("db_value"),
     ticket: $(this).attr("ticket_id")

Is looking for an attribute db_field on the element referenced by this. An attribute is something like src of the img element, or href of the a element (etc). Since you haven't shown your form I can't speculate on what this should be, however note that you can simply serialize the form data (like seen in the ajax of my example in your other thread) like this:

$.ajax({
          url: 'acknowledge.php',
          method: 'POST',
          data: $('#formID').serialize(),
          success: function() {
            alert("success!");
          }
        });

and match it up with a form tage like:

<form id="formID" action="http://example.com/script.php" method="post">

bradgrafelman

rsmith;10998662 wrote:
data: {
     action: 'save',
     field: $(this).attr("db_field"), 
     val: $(this).attr("db_value"),
     ticket: $(this).attr("ticket_id")
          },
Doesn't this part of the ajax post the db_field and db_value and ticket_id?

Note the errors in the use of attr() Derokorian mentioned, but to answer your question, no, there are no such elements being POST'ed. There is, however, one element named just "field" as well as one named "val".