Updating a MySql database using PHP via an onClick javascript function

Question

I am creating a web game for learning new words aimed at children.

I have a set of four links each displaying a specific word retrieved from my database and a clue, I need to check that the word which has been selected matches the correct word for that clue.

I know that I need to use javascript because of the onClick function and I can successfully check whether the word selected matches the correct word. However, I then need to update a score held in the database if the word is matched correctly, therefore I would need to use php.

From what I can gather this means I must use AJAX but I can't find a good example of anyone using AJAX onClick of a link to then update a database.

I have attempted to do this...but its probably completely wrong as I couldn't get it to work properly:

//This is my link that I need to use in my game.php file where $newarray[0] is that answer I want to check against $newarray[$rand_keys]

<a onClick=originalUpdateScore('$newarray[0]','$newarray[$rand_keys]')>$newarray[0]</a>

//my attempt at ajax in a score.js file

var xmlHttp;

function originalUpdateScore(obj,corr){
    xmlHttp=GetXmlHttpObject();
if (xmlHttp==null)
{
 alert ("Browser does not support HTTP Request");
 return;
}

if(corr == obj){

var url="getscore.php";
//url=url+"?q="+str;
//url=url+"&sid="+Math.random();
xmlHttp.onreadystatechange=stateChanged;
//xmlHttp.open("GET",url,true);
xmlHttp.open(url,true);
xmlHttp.send(null);

 alert('Correct');

}
else
{
 alert('AHHHHH!!!');
}

window.location.reload(true);

}

function stateChanged() 
    { 
 if (xmlHttp.readyState==4 || xmlHttp.readyState=="complete")
 { 
  document.getElementById("txtHint").innerHTML=xmlHttp.responseText;
 } 
}
function GetXmlHttpObject()
{
 var xmlHttp=null;
 try
 {
  // Firefox, Opera 8.0+, Safari
  xmlHttp=new XMLHttpRequest();
 }
 catch (e)
 {
  //Internet Explorer
  try
  {
   xmlHttp=new ActiveXObject("Msxml2.XMLHTTP");
  }
  catch (e)
  {
   xmlHttp=new ActiveXObject("Microsoft.XMLHTTP");
  }
 }
return xmlHttp;

}

//attempting to update the database in a getscore.php file

<?php
//$q=$_GET["q"];
include("dbstuff.inc");
$con = mysqli_connect($host, $user, $passwd, $dbname)
or die ("Query died: connection");

$sql= "UPDATE `temp` SET `tempScore`= `tempScore`+1 WHERE (temp.Username='$_SESSION[logname]')";

$showsql = "SELECT `tempScore` FROM `temp` WHERE (temp.Username='$_SESSION[logname]')";
$result = mysqli_query($con, $showsql);

echo "$result";

mysqli_close($con);
?>

Answer 1

I would highly recommend learning AJAX properly - it won't take you ages but will help you understand what you can and can't do with it.

Updating a DB from a web page via AJAX is very common. I would suggest simplifying your JavaScript development using jQuery (a JavaScript library). There is a good introduction to jQuery and AJAX here.

Basically what jQuery will do is write a lot of the boilerplate code for you. What you will end up writing is something like this:

function updateScore(answer, correct) {
  if (answer == correct) {
    $.post('updatescore.php');
  }
}

...

<a onclick="updateScore(this, correct)" ...> </a>

What you're doing here is sending a POST request to updatescore.php when the answer is correct.

Then, in your updatescore.php, you just need to have PHP code like you already do which will update the score in the database.

You can obviously do many more complicate things than this, but hopefully that will be enough to get you started.

Answer 2

I noticed you have "window.location.reload(true);" in your code. Why? That seems like it would make things not work.

You should try to analyze your program to find out where the problem is happening. Then you will be able to ask us a very specific question like "why does Firefox not fire the onClick handler when I click on this link" instead of just posting three pages of code. When you paste so much code, it's pretty hard for us to find your bug.

So here are the questions you should ask:

1. Is my HTML being parsed correctly? To me, it looks like it might not be parsed correctly because you did not put quotes around the value of onClick. You should use quotes, like: onClick="..." To find out if your HTML is being parsed nicely, you can use Firefox's View->Source feature and look at the colors it prints.

2. Is my onClick handler getting called? It looks like you are using alert()'s effectively so that's good.

3. Does the request actually get sent to my server? To determine this, you should use Firefox, and install the Firebug extension. In the "Net" panel, it will show you all the AJAX requests that are being made by your page, and it will show you the results that were returned from the server.

4. Is the script on my server doing the right thing? So on the server side, you can now add lines like "echo 'hello world';" and you will see that output in the Firebug Net panel, which will help you debug the behavior of your server-side script.

5. Is my stateChanged function getting called? Once again, use alert() statements, or write to Firebug's debug console.

Once you've narrowed your problem down, try to reduce your code to the simplest possible code that still fails. Then show us the code and tell us exactly what the symptoms of the error are.

On another note, I recommend getting this book: Javascript: The Deinitive Guide, 5th Edition by O'Reilly. It covers lots of cool stuff like AJAX and closures. It costs $50 but it's definitely a good investment because it explains things in a much more coherent way then you'll ever get from free websites.