Sometimes two submits (AJAX calls) are required to save data

Question

I am currently building an multiple choice quiz module for my website. There is a list with 20 questions, which can only be answered by customers that are logged in.

I have three tables:

table questions, with fields questions.id, questions.description

table options, with fields options.id, options.questionid, options.trueorfalse

table answers, with fields answers.id, answers.customerid, answers.questionid, answers.chosenoption

The questions come at random, rand(), by an ajax-call, so only the questions that are not answered by a customer are being showed. Once all questions are answered, the final score is echoed.

Can someone help me with the correct sql statement the php script should run to show up the questions that have NOT been answered by a customer?

Currently it seems to work but I sometimes have to submit twice the same answer. Actually the first time it is aleady being sent, but the ajax call shows up the same question.

This is the sql statement I use...

SELECT * from questions
where  not exists (
select answers.questionid from answers
where answers.questionid = questions.id 
and answers.customerid = '".$customerid."' )
order by rand()
limit 1"

The data is being sent by a JQuery post (works well). Inside the page quiz_ajax.php

<script type="text/javascript">
$(document).ready(function(){
$("#ajax-post-answer").submit(function(){
var str = $(this).serialize();
$.ajax({
type: "POST",
url: "answerpost.php",
data: str
 });

callquiz();

return false;
});
});
</script>

Inside the page quiz.php (main page)

<script type="text/javascript"> 
function callquiz()
{
var newDate = new Date;
$.ajax({
method: 'POST',
url : 'quiz_ajax.php?'  + newDate.getTime(),
dataType : 'text',
success: function (text) { 
$('#updateMequiz').html(text); }
});
}

callquiz();
</script> 
<div id="updateMequiz"></div>

So the function callquiz, should refresh this div with a new question. I am investigating this problem for 24 hours now, so I am very curious to hear what I am doing wrong.

Thanks in advance!!

Development betterment: Consider [using placeholders](http://stackoverflow.com/questions/60174/how-to-prevent-sql-injection-in-php). — , Jan 29 '13 at 20:52
This is only an example, I am aware of the risks of SQL injection, no problem. The variable customerid goes through mysql_real_escape_string(), but thanks anyway for your help... — luus, Jan 29 '13 at 20:57
@luus See: [SQL injection that gets around mysql_real_escape_string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) — Michael Fredrickson, Jan 29 '13 at 21:00
@luus I don't care. I never care in this situation. Good practices are Good Practices and it could just have easily been shown using a placeholder like `?` or `:customerId` - in any, it's just a comment. However, there is no "sometimes" when it comes to [proper ACID] databases: can the problem be isolated more? Was there an exception (remember to check/propagate the return value)? A rollback? Is auto-commit used? Did the code just not execute? — , Jan 29 '13 at 21:08
@Michael Fredrickson, thanks, but there is no real danger here, the customerid is also checked before the sql statement by php to be ctype_digit — luus, Jan 29 '13 at 21:09
@pst thanks to solve this, at least I was close to the solution :) — luus, Jan 29 '13 at 22:02

score 0 · Accepted Answer · 2013-01-29T21:49:40.983

0

The issue is likely related to the asynchronous nature of HTTP/AJAX: just because the request has been "put on the wire" does not mean it has actually been processed by the server - much less saved to the database!

As a quick check, move callquiz() from the $.submit callback to the success callback of the "answerpost.php" $.ajaxcallback. (That is, only request new information after the completion of the request that submits question answers.)

This will ensure that the "update" (answerpost.php) operation has occurred (and completed) before the next "get quiz" (quiz_ajax.php) request. If this is indeed the case, consider having the "update" also return the "get quiz" information to make a single unified call.

edited Jan 29 '13 at 21:49

answered Jan 29 '13 at 21:44

1

You were so right! I added a succes {} option to the $.ajax-post and within that option, I added the callquiz() function. That way it's only called on succes. Works like a charm now. Thanks man, you don't know half how happy you made me ;) – luus Jan 29 '13 at 21:56
by the wya my ctype_digit check of the customerid is enough to protect that variable vs. injection, isn't it? As php checks if it is a numeric value, there can no tags, special chars, etc. be injected. Or do i see that wrong? – luus Jan 29 '13 at 21:59
1

@luus The beauty of placeholders, is one *doesn't need to worry* about any of that - perhaps the check was forgotten in just one place: oops! There are more important/interesting things to focus on, like Business Rules. (Also, as a bonus, I find placeholders make queries look more tidy.) – Jan 29 '13 at 22:23

Sometimes two submits (AJAX calls) are required to save data

1 Answers1