database SELECT query problem

Question

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

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.
My submit score function works perfectly. I use phpadmin to check. Everytime I submit it will update the database correctly.
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.

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'] . "|";
    }

That would be how you query a database with PHP. If the results aren't what you expect your problem lies elsewhere and/or the data in the database isn't correct. — Brian Roach
– Brian Roach, Commented Sep 11, 2011 at 4:21

Sparky · Accepted Answer · 2011-09-11 04:28:38Z

3

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

edited Sep 11, 2011 at 4:28

answered Sep 11, 2011 at 4:22

Sparky

15.1k2 gold badges34 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

pho · Accepted Answer · 2011-09-12 07:38:34Z

2

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

answered Sep 12, 2011 at 7:38

pho

25.7k8 gold badges48 silver badges75 bronze badges

1 Comment

巫妖王 Over a year ago

Yes. You are correct, its web or php caching it. I find your suggestion on database query is very useful. Thank you

Jason Dean · Accepted Answer · 2011-09-11 04:21:57Z

0

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.

answered Sep 11, 2011 at 4:21

Jason Dean

9,62529 silver badges39 bronze badges

Comments

Shalom Sam · Accepted Answer · 2011-09-12 05:17:51Z

0

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.

answered Sep 12, 2011 at 5:17

Shalom Sam

1,5591 gold badge17 silver badges28 bronze badges

Collectives™ on Stack Overflow

database SELECT query problem

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related