Cannot dynamically access updated versions of my mySQL database from within my flash game

Question

I've created a game using Actionscript 3. In this game I save scores to an online mySQL database. I access this database from within my flash game by calling php scripts that I have written using the URLLoader class.

I load all the players scores so that they can be viewed at the begining of the game and then save as levels are completed.

My issue is that once I save a score and then reload all the scores at the main menu, I retrieve the scores that were loaded at the beginning of the game and none of the updated ones. I am only able to view the updated scores if I restart my game.

Here is the actionscript code for loading my score. This is called once when the main menu initialises.

getScoresRequest = new URLRequest("http://localhost/GetScores.php");
getScoresRequest.method = URLRequestMethod.GET;
getScoresLoader = new URLLoader();
getScoresVariables = new URLVariables();

getScoresVariables.email = Globals.email;
getScoresRequest.data = getScoresVariables;

getScoresLoader.dataFormat = URLLoaderDataFormat.TEXT;
getScoresLoader.addEventListener(Event.COMPLETE, completeHandler);
getScoresLoader.load(getScoresRequest);

private function completeHandler(e:Event):void
{
    var xmlData:XML = new XML(e.target.data);
}

I then use this xmlData to display my scores.

Here is my php for loading my scores:

<?php

$email = $_REQUEST["email"];

$dbh=mysql_connect ("**********", "*****", "*******")
or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("*******",$dbh);

$query = "SELECT * FROM Scores WHERE email = '$email'";

$result = mysql_query($query, $dbh);

$xml_output = "<Scores>\n";

for($x = 0; $x < mysql_num_rows($result); $x++)
{
    $row = mysql_fetch_assoc($result);
    $xml_output .= "\t<Entry>\n";
    $xml_output .= "\t\t<Level>" . $row['level'] . "</Level>\n";
    $xml_output .= "\t\t<Score>" . $row['score'] . "</Score>\n";
    $xml_output .= "\t</Entry>\n";
}
$xml_output .= "</Scores>";

echo $xml_output;


mysql_close($dbh);

?>

My actionscript for saving scores is very similar to loaded with the exeption of the loader.dataFormat is set to Variables instead of Text. If anyone would like me to post this I can do.

Here is the php for saving:

<?php
$email = $_REQUEST["email"];
$level = $_REQUEST["level"];
$score = $_REQUEST["score"];

$dbh=mysql_connect ("******", "********", "********")
or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("********",$dbh);

$query = "SELECT * FROM Scores WHERE email = '$email' AND level = '$level'";

$result = mysql_query($query, $dbh);
$row = mysql_num_rows($result);

$returnVars = array();

if($row > 0)
{
for($x = 0; $x < 1; $x++)
{
    $currentRow = mysql_fetch_assoc($result);

    if((int)$score > (int)$currentRow['score'])
    {
        $returnVars['prevHighScore'] = $currentRow['score'];
        $updateQuery = "UPDATE Scores SET score = '$score' WHERE email = '$email' AND level = '$level'";
        $result = mysql_query($updateQuery, $dbh);
        $returnVars['newHighScore'] = $score;
        $returnVars['type'] = "Score Updated";
    }
    else
    {
        $returnVars['prevHighScore'] = $currentRow['score'];
        $returnVars['newHighScore'] = '0';
        $returnVars['type'] = "Score Not Updated";
    }
}
}
else
{
    $insertQuery = "INSERT INTO Scores(email, level, score) VALUES('$email', '$level', $score)";
    $result = mysql_query($insertQuery, $dbh);
    $returnVars['newHighScore'] = $score;
    $returnVars['prevHighScore'] = "0";
}
$returnVars['rows'] = $row;
$returnString = http_build_query($returnVars);
echo $returnString ;

mysql_close($dbh);

?>

My database is definitely updating as I'm able to view the contents.

If anyone has any ideas it would be greatly appreciated.

Try adding a random value to your url. Like: http://localhost/GetScores.php?_rand=0.12324343213 and regenerate this value everytime you access your source. — Yoshi
Is there something else I must do to get this to work as when I add that to my url I get an error about an unexpected variable. I tried adding the variable to the php file but still didnt work. — John Lilley
My guess was that somewhere in your communication, http://localhost/GetScores.php get's cached. So adding something to make the request unique (the random value) is often the best approach. I don't know much about flash, but maybe URLRequest::cacheResponse will help you? — Yoshi
Unfortunatly the cacheResponce is only available in the Air runtime. However, I got your random number idea to work and it's fixed my problem. Thankyou :) — John Lilley

Yoshi Yoshi · Accepted Answer · 2011-07-26T15:31:47

Shamelessly adding my own comment as answer

Try adding a random value to your url. Like:

http://localhost/GetScores.php?_rand=0.12324343213

and regenerate this value everytime you access your source.

Cannot dynamically access updated versions of my mySQL database from within my flash game

1 Answers