Page 1 of 2

Temporary to Permanent database

Posted: Sat Nov 22, 2008 12:11 pm
by unknown87
Hi

I have a temporary database and permanent database and I want to move data from the temporary database to the permanent database in ID order.

This is what I have so far:
 

Code: Select all

SELECT id FROM temporary ORDER BY id LIMIT 1; 
INSERT INTO permanent SELECT * FROM temporary WHERE id = $id 
DELETE FROM temporary WHERE id = $id
But I want to move one row at a time every 30 seconds.

How can I achieve this without the use of cron jobs?

Thanks in advance

Re: Temporary to Permanent database

Posted: Sat Nov 22, 2008 12:19 pm
by Mark Baker
unknown87 wrote:But I want to move one row at a time every 30 seconds.
Any particular reason why
unknown87 wrote:How can I achieve this without the use of cron jobs?
Not easily. Your alternative is a CLI script running on the server with set_time_limit(0) so that it won't time out, and with a sleep(30) between each insert

Re: Temporary to Permanent database

Posted: Sat Nov 22, 2008 12:30 pm
by unknown87
Mark Baker wrote:Any particular reason why
I'm creating a online cricket management game where the games are played every Fridays at 10am. I want to provide the user to see live commentry of the games.

Thats why I have created a temporary & parmanent database and everything in the parmanent database is displayed to the user which will be updated every 30 seconds.

I don't know if this is the right way but this is the only solution I can think of.

Re: Temporary to Permanent database

Posted: Sat Nov 22, 2008 12:45 pm
by Eran
If you would explain in more detail the requirements of your game, maybe someone here would have an idea on how to improve the design of your system.

Re: Temporary to Permanent database

Posted: Sat Nov 22, 2008 12:56 pm
by onion2k
unknown87 wrote:Thats why I have created a temporary & parmanent database and everything in the parmanent database is displayed to the user which will be updated every 30 seconds.
Just put all the commentary in one database and only select what you need based on a time value.

Re: Temporary to Permanent database

Posted: Sat Nov 22, 2008 1:30 pm
by unknown87
pytrin wrote:If you would explain in more detail the requirements of your game, maybe someone here would have an idea on how to improve the design of your system.
This is a game where users play matches with other users in a league format and they set their match orders for their upcoming games.

All the scores is calculated based on the players which the user has chosen to play before the games start which is fridays at 10 am.

Now that everything is already calculated and stored in the database - starting from 10am on fridays, i need to start displaying the scores to the user, for example:

Ball 1 : 2 Runs

30 second later user should see:

Ball 1 : 2 Runs
Ball 2 : 0 Runs

Another 30 second later user should see:

ball 1 : 2 Runs
Ball 2 : 0 Runs
Ball 3 : 6 Runs

... and so on

User have to refresh the page to see changes so i'm going to have the match page refresh every 30 automatically.

If the user logs in say 10:03am - they will have missed 6 balls of the games so they will see

Ball 1 : 2 Runs
Ball 2 : 0 Runs
Ball 3 : 6 Runs
ball 4 : 1 Runs
Ball 5 : 0 Runs
Ball 6 : 3 Runs

30 second later user should see:

Ball 1 : 2 Runs
Ball 2 : 0 Runs
Ball 3 : 6 Runs
ball 4 : 1 Runs
Ball 5 : 0 Runs
Ball 6 : 3 Runs
Ball 7 : 1 Runs

Been working on this for about 2 weeks but with no luck.

I hope someone on here can help me with this.

Re: Temporary to Permanent database

Posted: Sat Nov 22, 2008 1:34 pm
by Eran
As onion said, use one database. If you want scores to only update once every 30 seconds, use caching to present the results with a lifetime of 30 seconds. There are many caching solutions written in PHP, I usually use the Zend_Cache component from the Zend Framework (works also as a standalone).

Re: Temporary to Permanent database

Posted: Sat Nov 22, 2008 3:15 pm
by unknown87
thank you very much for the help - i'm not familar with caching so i will do some reading on that. If i get stuck i'll let you guys knows.

Re: Temporary to Permanent database

Posted: Sun Nov 23, 2008 1:23 pm
by unknown87
Could someone please help me get started with some code or a few examples becauce I've got no idea how caching is going to help me on what i'm trying to achieve.

Thanks in advance.

Re: Temporary to Permanent database

Posted: Sun Nov 23, 2008 4:27 pm
by VladSun
Mark Baker wrote:
unknown87 wrote:How can I achieve this without the use of cron jobs?
Not easily. Your alternative is a CLI script running on the server with set_time_limit(0) so that it won't time out, and with a sleep(30) between each insert
MySQL 5.1.6+ does this easily ;)

Re: Temporary to Permanent database

Posted: Sun Nov 23, 2008 5:37 pm
by Eran
Caching allows you to obtain data the can be relatively expansive to create (such as a database query) by saving it for a time in a static format (such as a file).
To be honest, I didn't understand from your description why do players data should only be updated every 30 seconds .. I thought you might be trying this path for performance reasons, but first you have to experience performance problems before you attempt to solve them.
I would suggest giving the players the most current data every time they refresh the page, and only when things slow down attempt to solve it.

If you still want to use caching, then using Zend_Cache as an example:

Code: Select all

 $frontendOptions = array(
    'lifetime' => 30 //Cache will be fresh for 30 seconds
);
$backendOptions = array(
    'cache_dir' => '/tmp' //Cache will be stored here
);
$cache = Zend_Cache::factory('Core','File',$frontendOptions,$backendOptions);
 
//Check if cache is fresh
 
if(! ($data = $cache -> load($userId))) { //We need somekind of special identifier for the cache, for example a user ID
    //Cache miss
    $data = fetchResults($userId); //Some function that gets the results depending on the user ID
    
    $cache -> save($data,$userId); //Cache the results
}
 
//Display results 

Re: Temporary to Permanent database

Posted: Sun Nov 23, 2008 6:06 pm
by unknown87
I'm getting this error in the brower:

Fatal error: Class 'Zend_Cache' not found in C:\wamp\www\project\cric.php on line 7

Re: Temporary to Permanent database

Posted: Sun Nov 23, 2008 6:13 pm
by Eran
You have to include the relevant files from the Zend Framework obviously. Did you read the Zend Framework manual?..

Re: Temporary to Permanent database

Posted: Sun Nov 23, 2008 6:22 pm
by unknown87
no not properly but i'll go through it again

Re: Temporary to Permanent database

Posted: Mon Nov 24, 2008 3:48 am
by onion2k
VladSun wrote:MySQL 5.1.6+ does this easily ;)
I'd never looked at Events before.. I can imagine that being very useful indeed.