Solved

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

Posted on 2011-02-16
15
2,169 Views
Last Modified: 2012-05-11
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.
0
Comment
Question by:goodie069
  • 7
  • 6
  • 2
15 Comments
 
LVL 4

Expert Comment

by:JayDiablo
ID: 34911207
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.
0
 

Author Comment

by:goodie069
ID: 34911339
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
0
 
LVL 11

Expert Comment

by:Ovid Burke
ID: 34911347
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

0
 

Author Comment

by:goodie069
ID: 34911379
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
0
 
LVL 4

Accepted Solution

by:
JayDiablo earned 375 total points
ID: 34911440
@madaboutasp:  I thought of that as well, but it's still required to do it before the record is inserted, just in case another user on another computer has submitted a value with a matching date in-between the time that your check is done, and when the actual submit is done (could be a second, could be a few minutes, just depends on the user flow).

@goodie069: I personally like displaying the returned error message to the user, as it allows you to change the message given to the user on the back-end, without having to always update the JS on the front-end each time you add a new error message.  Your needs may vary (internationalization comes to mind).  That said, it's probably still good practice to only give the user a success message if the result was a success (don't put the success message in the else condition):

function showResult(data) {
        if (data === 'success') {
                alert('SUCCESS. Your metrics data has been saved.');
                return false;
        } else if (data === 'save_failed_duplicate') {
                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


That way it falls back to an error message if there wasn't an expected response.

However, the code you have in place should work, so the question is, why doesn't it?  My initial guess is that your server is returning more than just "save_failed_duplicate", perhaps there is other output sneaking in there?  To confirm this you can make use of Firefox's Firebug extension, and watch the Console tab as you make a request (it'll show you the data sent, and the exact response).  Or Chrome's built-in Inspector (which has a Network tab that will show you the same sort of data).  Or you can just alert the "data" variable inside the showResult function and check that it's EXACTLY what you're testing against in that if statement.
0
 
LVL 11

Assisted Solution

by:Ovid Burke
Ovid Burke earned 125 total points
ID: 34912829
A few adjustments:

First to your JavaScript..

function processMetricsDetails() {
        var errors = '';

        // Validate Metrics Date
		var metrics_date = $("#Metrics [name='metrics_date']").val();
        if (metrics_date == "null" || metrics_date == "") { // check for empty value
                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;
        }
}

Open in new window


and then to your PHP ...

// define variable for your queries
$location 		= mysql_real_escape_string($_POST['location']);
$metrics_date 	= mysql_real_escape_string($_POST['metrics_date']);

$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) {
		$sql = "INSERT INTO test (location, metrics_date) VALUES ('$location', '$metrics_date')";
		if (@mysql_query($sql, $link)) {
				echo "success";
				@mysql_close($link);
				return;
		} else {
				echo "save_failed";
				@mysql_close($link);
				return;
		}
	}
}

Open in new window

0
 

Author Comment

by:goodie069
ID: 34919210
@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
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 4

Expert Comment

by:JayDiablo
ID: 34919281
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?
0
 

Author Comment

by:goodie069
ID: 34919465
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
0
 
LVL 4

Expert Comment

by:JayDiablo
ID: 34919573
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.
0
 
LVL 4

Expert Comment

by:JayDiablo
ID: 34919582
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."?
0
 

Author Comment

by:goodie069
ID: 34919740
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.
0
 

Assisted Solution

by:goodie069
goodie069 earned 0 total points
ID: 34919912
Ugh, I found it.

The page that has this update form on it also has another update form that allows the user to update a different set of information. Both use the same ajax/jquery form input, but have different javascript and php files, different ID's and, I thought, different names for their functions.

It turns out that the other form also used "showResult(data)", and of course that js was being loaded before the other one, and as such, it was using that showResult function.

I gave them both discrete names (showResultMetrics) and that has solved the problem - the correct errors are being thrown now and successful updates are taking effect.

I hate it when it's something simple like this - makes me feel like an idiot! But I'm glad it's working now, and thank both of you for your help. I'll divide up the points between the two of you 75%/25% since jay was a bit more instrumental in guiding me in the right direction.

Thanks again guys, I appreciate your help.
Chris
0
 
LVL 4

Expert Comment

by:JayDiablo
ID: 34920177
Glad you were able to figure it out. :)
0
 

Author Closing Comment

by:goodie069
ID: 34949786
Found my own dumb mistake :-)
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Summernote and form validation 10 44
Ajax control editor 4 36
mysql left join sentence 7 22
Web Reply Form - PHP with Upload 4 21
Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
I found this questions asking how to do this in many different forums, so I will describe here how to implement a solution using PHP and AJAX. The logical flow for the problem should be: Write an event handler for the first drop down box to get …
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now