Link to home
Start Free TrialLog in
Avatar of goodie069
goodie069Flag for United States of America

asked on

Checking for a duplicate value in database prior to submitting Ajax form

I am using jQuery Form to submit an Ajax form to a MySQL database. One of the form fields is a dropdown menu which contains available dates that the user must select from. This data corresponds to a specific column ("metrics_date") in the table. The user also selects their appropriate site location ("location"), which is also stored in the table.

I would like to do a bit of pre-processing and query the database to see if a row already exists in the table with that same date for that site location. If it does exist, the user should be notified of the duplicate entry and form processing should stop. If it does not exist, form processing should continue.

For example -- A user selects 2011-02-16 as their metrics_date and "Site 1" as their location. The user does not realize that their team member already updated their data for this date, so a record for Site 1 with a metrics_date of 2011-02-16 already exists. They should be alerted to this, and the form should not be submitted.

I used this tutorial to create my jquery / ajax form --
http://www.myphpetc.com/2010/01/save-to-database-via-ajax-using-jquery.html

Given that I'm going from PHP to an Ajax / Javascript function, I'm a bit lost. I can get around PHP pretty well, but I'm pretty dumb when it comes to Ajax and Javascript. I know that I can't necessarily pass a php variable array containing the results of a mysql query (dates for a particular site) to javascript and perform the validation there, but I'm not sure how else to validate using this type of form scripting.

Any advice or suggestions are welcomed. Thanks in advance.
Avatar of JayDiablo
JayDiablo
Flag of United States of America image

I don't see why you can't do the validation on the form post.  That's probably where it should be done anyhow, especially to avoid any sort of race conditions (as you describe, perhaps another team member already entered data that would cause the duplicate error).

In the example you posted, if the PHP script returns "save_failed" on the ajax post, there is some code written to deal with that (looks like it just alerts the user that the save failed).

You can leverage that same idea, and do your date validation check, if the check fails, return "save_failed" as the output (and stop execution of that script with an exit; or however else you output errors).  The javascript Ajax handler will see that "save_failed" message and alert the user.

It would probably be nice to make it a bit more user friendly, but try to get this error message returning when there's a duplicate first.
Avatar of goodie069

ASKER

Hi JayDiablo,

Thanks for your response. I've actually got it functioning that way now, but I need the alert message to be more descriptive than the standard "Changes not saved" alert that's prompted with save_failed.

That's where I'm stuck, though. I can't figure out how to get a secondary "save_failed_duplicate" alert message to function - the error alert only works with "save_failed" - if I change it to anything else, i get a Success (even though the form is not submitted). I know the query works, and I've specified in my conditional (process.php) to echo "save_failed_duplicate" if a duplicate exists. I've also included the conditional, with appropriate alert message, in save.js.

Here's my code:

process.php
// Check if data is already present for that Date and Location
$check_sql = "SELECT * FROM test WHERE location = '$location' AND metrics_date = '$metrics_date';";
$check_sql_query=mysql_query($check_sql, $link);
if (mysql_num_rows($check_sql_query) > 0) {
        echo "save_failed_duplicate";
        @mysql_close($link);
        return;
} else if (mysql_num_rows($check_sql_query) == 0) {
// PERFORM INSERT HERE
if (@mysql_query($sql, $link)) {
        echo "success";
        @mysql_close($link);
        return;
} else {
        echo "save_failed";
        @mysql_close($link);
        return;

Open in new window


save.js
$("document").ready(function() {
        $('#Metrics').submit(function() {
                processMetricsDetails();
                return false;
        });
});

function processMetricsDetails() {
        var errors = '';

        // Validate Metrics Date
        var metrics_date = $("#Metrics [name='metrics_date']").val();
        if (metrics_date == "null") {
                errors += ' - Please select a Metrics Date\n';
        }
// MORE FORM VALIDATIONS
        if (errors) {
                errors = 'The following errors occurred:\n' + errors;
                alert(errors);
                return false;
        } else {
                // Submit our form via Ajax and then reset the form
                $("#Metrics").ajaxSubmit({success:showResult}).resetForm();
                return false;
        }

}

function showResult(data) {
        if (data == 'save_failed') {
                alert('ERROR. Your metrics were not saved. Please contact the admin for assistance.');
                return false;
        } else if (data == 'save_failed_duplicate') {
                alert('ERROR. Metrics data already exists for the selected date.');
                return false;
        } else {
                alert('SUCCESS. Your metrics data has been saved.');
                return false;
        }
}

Open in new window


So as you can see, I'm halfway there. I just can't figure out how to get Ajax to recognize the echoed "save_failed_duplicate" as an error situation and run the alert for it. I have a feeling it's in the forms.js file, but again, I'm pretty clueless with it comes to javascript / ajax. I'm including that file as an attachment here, just in case it might help. forms.js

Thanks again, Jay. Please let me know if you have any other thoughts.
Chris
Avatar of Ovid Burke
I strongly recommend that all the data are validated and check on submit as well, but I do see the benefit of check before to assist the user with their choice of date and location. This way, hopefully, the user avoids combination that are not available altogether.

In this regard, I would try this:

 
<?php # Mad_About_ASP : EE Help : bookingpage.php ?>
<!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>
<title>Dupe Check</title>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<script type="text/javascript" src="path/to/jquery-1.5.js"></script>
<script type="text/javascript">
$(document).ready(function() {
	$('#metrics_date').change(function() {
		$.post('dupecheck.php', { metrics_date: $(this).val(), location: $('#location').val() }, function(data) {
			$('#dupecheck').html(data);
		});
	});
	$('#location').change(function() {
		$.post('dupecheck.php', { metrics_date: $('#metrics_date').val(), location: $(this).val() }, function(data) {
			$('#dupecheck').html(data);
		});
	});
});
</script>
</head>

<body>
<div>
<select id="metrics_date" name="metrics_date">
  <option value="2011-02-16">2011-02-16</option>
  <option value="2011-02-17">2011-02-17</option>
  <option value="2011-02-18">2011-02-18</option>
  <option value="2011-02-19">2011-02-19</option>
</select>
<select id="location" name="location">
  <option value="Site 1">Site 1</option>
  <option value="Site 2">Site 2</option>
  <option value="Site 3">Site 3</option>
  <option value="Site 4">Site 4</option>
</select>
</div>
<div id="dupecheck"></div>
</body>
</html>

Open in new window


... and this:

 
<?php # Mad_About_ASP : EE Help : dupecheck.php

require_once('path/to/your/connection.php');

$metrics_date = trim($_POST['metrics_date']);
$location = trim($_POST['location']);

$is_dupe = mysql_result(mysql_query("SELECT COUNT(*) AS is_dupe WHERE metrics_date = '$metrics_date' AND location = $location", $conn_link), 0, 0);

if($is_dupe > 0) {
	echo sprintf("The date <strong>%s</strong>, is not avaliable for the location <strong>%s</strong>.", $metrics_date, $location);
} /*else {
	echo "Good choice!";
}*/

?>

Open in new window

Thanks for your response, madaboutasp --

That looks great, but I would really like to perform the validation on submit. I've posted a response with a bit more clarification just prior to receiving your message, so you may not have seen it. If you have any thoughts on accomplishing this on form submit, please let me know.

Thanks again for your help,
Chris
ASKER CERTIFIED SOLUTION
Avatar of JayDiablo
JayDiablo
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
@JayDiablo --

Thanks. I agree that having the success be the else statement doesn't make much sense. I've changed it to the code you recommended.

I've installed Firebug and, while watching the console while submitting the form, I see the response as "save_failed_duplicate", which is what is expected. No extra characters or information is included in the response - just "save_failed_dupe". I am still receiving the "Success" alert, though. Very strange.

The only other information I see through the console is 200 OK 309ms   jquery-1.4.2-min.js line 130

Line 30 of jquery-1.4.2-min.js
g("abort")}}catch(l){}e.async&&e.timeout>0&&setTimeout(function(){x        &&!E&&g("timeout")},e.timeout);try{x.send(n==="POST"||n==="PUT"||n=        =="DELETE"?e.data:null)}catch(m){c.handleError(e,x,null,m);d()}e.as        ync||g();return x}},handleError:function(a,b,d,f){if(a.error)a.erro        r.call(a.context||a,b,d,f);if(a.global)(a.context?c(a.context):c.ev        ent).trigger("ajaxError",[b,a,f])},active:0,httpSuccess:function(a)        {try{return!a.status&&location.protocol==="file:"||a.status>=200&&a        .status<300||a.status===304||a.status===

Open in new window


Actually, line 129 looks more interesting to me:
 d();E=true;if(x)x.onreadystatechange=c.noop}else if(!E&&x&&(x.ready        State===4||q==="timeout")){E=true;x.onreadystatechange=c.noop;i=q==        ="timeout"?"timeout":!c.httpSuccess(x)?"error":e.ifModified&&c.http        NotModified(x,e.url)?"notmodified":"success";var p;if(i==="success"        )try{o=c.httpData(x,e.dataType,e)}catch(v){i="parsererror";p=v}if(i        ==="success"||i==="notmodified")j||b();else c.handleError(e,x,i,p);        d();q==="timeout"&&x.abort();if(e.async)x=null}};try{var h=x.abort;        x.abort=function(){x&&h.call(x);

Open in new window


I'm wondering if that's the cause of the erroneous "Success" message, even though no data is modified. There's no mention of a "save_failed" scenario in that file, though, so I'm still not understanding why that works but nothing else does.

@madaboutasp --

Thanks. The adjustments to my javascript aren't necessary since the metrics_date form element is a drop-down and the "Choose a Date" default has a value of "null", so that's really all I need.

I made the adjustment to my php script (moving the second conditional set inside the bracket after else) but am still experiencing the same issue.

Thanks again to both of you for the suggestions. Please let me know if you have any other ideas!
Chris
In the "showResult" function above, could you add this to the top of it:

console.log(data);

Open in new window


And then make a request that should return the duplicate error.  In your Firebug console you should get some output, could you post what that is?
Here's the showResult section with your change (just to be sure I put it in the right spot):
function showResult(data) {
console.log(data);
        if (data === 'success') {
                alert('SUCCESS. Your metrics data has been saved.');
                return false;
        } else if (data === 'save_failed_dupe') {
                alert('ERROR. Metrics data already exists for the selected date.');
                return false;
        } else {
                alert('ERROR. Your metrics were not saved. Please contact the admin for assistance.');
                return false;
        }
}

Open in new window


I did not get anything extra in the console -- same as before, except there's nothing in the HTML tab now (before, "save_failed_dupe" was listed. Under the response tab, I simply get "save_failed_dupe".

Wondering if this might be caused by an outdated version of jquery (or the minified version), I installed the most recent version (1.5). I'm still getting 200 OK 315ms jquery-1.5.js (line 7180).

jquery-1.5.js --
7177                                         // Do send the request
7178                                         // This may raise an except        ion which is actually
7179                                         // handled in jQuery.ajax (        so no try/catch here)
7180                                         xhr.send( ( s.hasContent &&         s.data ) || null );
7181
7182                                         // Listener
7183                                         callback = function( _, isA        bort ) {
7184

Open in new window


Let me know if you have any other thoughts. Thanks,
Chris
Could you slightly modify the console.log to something like this:

console.log('in showResult', data);

Open in new window


At minimum you should see "in showResult" appear in the console when the ajax call finishes.  If not, then I don't think this showResult function is actually getting called.
When you say you see the "success" message, is it exactly what is in this showResult function?

Do you get an alert that says: "SUCCESS. Your metrics data has been saved."?
I've modified the console.log as suggested and still receive the same result in the console (just "save_failed_dupe"). No extra information, not even "in showResult".

Yes, the success message is exactly what's outlined in the showResult function. However, I just modified the success message text and submitted the form again and found that the success message had not changed. So clearly this is coming from somewhere else.

Let me do a little digging and I'll report back.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Glad you were able to figure it out. :)
Found my own dumb mistake :-)