I'm a new comer to database, i faced a really strange problem lately:

  1. I produce a database like a leaderboard server for my game where it supports the player names and scores. I produced a php scipt to question the database, and the overall game will open the php url with GET arguments.

  2. My submit score function works perfectly. I personally use phpadmin to check on. Each time I submit it'll update the database properly.

  3. The issue is locating the leaderboard, my game will retrieve the leaderboard every 10 secs. It works each time. However the scores it will get will always be not the lastest records in database, each one is previous records. And after a little hrs, it may get more recent data.

Here are my troubleshoots: 1 I did not include "commit" during my submit score php script. And So I manully do commit, but game still can't obtain the lastest data.

  1. I attempt to manullay print the information in internet browser simply by entering php address with GET arguments. It shows previous data same as with my game. So the issue is avoid the overall game. I additionally mannualy go into the sql instructions in phpadmin, it'll return the lastetes data. And so i assure the issue is with my php?

php script:

$query = "SELECT * FROM `space_scores` ORDER by `score` DESC";
    $result = mysql_query($query) or die('Query failed: ' . mysql_error());

    $num_results = mysql_num_rows($result); 


    for($i = 0; ($i < $lines)&&($i<$num_results); $i++)
    {
         $row = mysql_fetch_array($result);
         echo $i+1 . "," . $row['name'] . "," . $row['score'] . "|";
    }

You have to switch off query caching

set session query_cache_type=0;

Check this out link for particulars:

http://dev.mysql.com/doc/refman/5.5/en/query-cache.html

Your php server is caching the sql query to prevent repetetive querying and lower load

Power it down by

set session query_cache_type=0;

in the beginning of the php script for choose.

Also, managing a db query every ten seconds is not recommended. Whenever you scale as much as 1000 customers (suppose) you'll have a minimum of 100 demands per second that is not so great for your server.

Consider running the php script which querys the db and creates the outcomes to some xml or comma delimited file every one minute approximately by establishing a cron job and obtain this file in the server every ten seconds rather than the php

I observed you didn't range from the code for closing your link with the DB. If it's there and you simply didn't show it, then disregard this. But otherwise, I question if you don't clearly close the bond towards the DB whether it can create a persistent connection that caches the information until a brand new connection is made.

If you're closing the bond then you might like to consider set up DB is set to cache the information for time.

I truly think this seems like a caching problem.

I believe you need to undergo your whole code again and find out should you used a clear case of the Score. To really make it more obvious see for those who have make use of the score data from the previous Database retrieval. Make certain you remove circumstances like this which you're locating the needed data in the DataBase each time.