database SELECT query problem

I am new to database, i faced a very strange problem recently:

1. I create a database as a leaderboard server for my game where it holds the player names and scores. I created a php scipt to query the database, and the game will open the php url with GET arguments.

2. My submit score function works perfectly. I use phpadmin to check. Everytime I submit it will update the database correctly.

3. The problem is retrieving the leaderboard, my game will retrieve the leaderboard every 10 secs. It succeeds everytime. But the scores it gets are always not the lastest records in database, all are previous records. And after some hours, it can get newer data.

Below are my troubleshoots: 1 I didn't include "commit" in my submit score php script. So I manully do commit, but game still can't get the lastest data.

1. I try to manullay print the data in web browser by entering php address with GET arguments开发者_运维百科. It shows previous data same as in my game. So the problem is not with the game. I also mannualy enter the sql commands in phpadmin, it will return the lastetes data. So i assure the problem 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 need to turn off query caching

set session query_cache_type=0;

See this link for details:

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

---

Your php server is caching the sql query to avoid repetetive querying and reduce load

Turn it off by

set session query_cache_type=0;

in the start of the php script for select.

Also, running a db query every 10 seconds is not a good idea. When you scale up to 1000 users (suppose) you will have at least 100 requests per second which is not very good for your server.

Consider running the php script which querys the db and writes the results to a xml or comma delimited file every 1 minute or so by setting up a cron job and get this file from the server every 10 seconds instead of the php

---

I noticed you did not include the code for closing your connection to the DB. If it is there and you just did not show it, then disregard this. But otherwise, I wonder if you do not explicitly close the connection to the DB if it will create a persistent connection that caches the data until a new connection is established.

If you are closing the connection then you might want to look into whether or not the DB itself is set to cache the data for a period of time.

I really think this sounds like a caching issue.

---

I think you should go through your entire code again and see if you used an instance of the Score. To make it more clear see if you have use the score data from a previous Database retrieval. Make sure you remove such instances and that you are retrieving the required data from the DataBase everytime.