Temporary to Permanent database

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

unknown87
Forum Newbie
Posts: 7
Joined: Sat Nov 22, 2008 12:00 pm

Temporary to Permanent database

Post 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
Mark Baker
Forum Regular
Posts: 710
Joined: Thu Oct 30, 2008 6:24 pm

Re: Temporary to Permanent database

Post 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
unknown87
Forum Newbie
Posts: 7
Joined: Sat Nov 22, 2008 12:00 pm

Re: Temporary to Permanent database

Post 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.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Temporary to Permanent database

Post 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.
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: Temporary to Permanent database

Post 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.
unknown87
Forum Newbie
Posts: 7
Joined: Sat Nov 22, 2008 12:00 pm

Re: Temporary to Permanent database

Post 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.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Temporary to Permanent database

Post 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).
unknown87
Forum Newbie
Posts: 7
Joined: Sat Nov 22, 2008 12:00 pm

Re: Temporary to Permanent database

Post 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.
unknown87
Forum Newbie
Posts: 7
Joined: Sat Nov 22, 2008 12:00 pm

Re: Temporary to Permanent database

Post 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.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Temporary to Permanent database

Post 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 ;)
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Temporary to Permanent database

Post 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 
unknown87
Forum Newbie
Posts: 7
Joined: Sat Nov 22, 2008 12:00 pm

Re: Temporary to Permanent database

Post 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
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Temporary to Permanent database

Post by Eran »

You have to include the relevant files from the Zend Framework obviously. Did you read the Zend Framework manual?..
unknown87
Forum Newbie
Posts: 7
Joined: Sat Nov 22, 2008 12:00 pm

Re: Temporary to Permanent database

Post by unknown87 »

no not properly but i'll go through it again
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: Temporary to Permanent database

Post 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.
Post Reply