chekcing a database automatically

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

Post Reply
bogha
Forum Newbie
Posts: 8
Joined: Tue Aug 05, 2008 2:06 am

chekcing a database automatically

Post by bogha »

hi

i'm trying to implement a phone number reservation form....

the user will enter a number and i will check if it's available or not

if the number is available the user will enter registration information to reserve this number

but the problem is this

i have a temp table which i used to store this number temporarly before completing the registeration
i put this table becouse in case another user want to register the same number he will now that this number is tmp registered and he/she can try after 15 min to check if it's fully registered or not... assume this scenario

1- the first user enters the number and he found it not registered
2- he will proceed to the registeration process
2- user two now trys to register the number
4- he will found it temporarly registered so he will wait for 15 min and check again.
5- now the first user closed the browser without completing the registeration and the number stays in he temp table
6- now any user trys to get this number will wait for infinity to get this number becouse its still in the temp table

my question is : are there anyway that can be used to trigger the database todo like minute by minute checking of the databse to do a clean up
this thing can be normally done if it's not a web application as my knowledge tell me.


do you have any suggestion?

i can do work around this by checking if the date now is larger than 15 min of the date that this number was registered in the database
but i was thinking about the capabilities of php to do such a background service

best regards
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: chekcing a database automatically

Post by requinix »

bogha wrote:i can do work around this by checking if the date now is larger than 15 min of the date that this number was registered in the database
but i was thinking about the capabilities of php to do such a background service
A daemon/background service to do this is silly. It'll just waste resources.

Use one table. No temporary tables. The difference between "actual" and "theoretical" reservations is a little flag you add on to the data - another field in the table.
When you check the database for existing reservations (taking into account whether it's been 15 minutes since a temporary reservation was placed), generate a random number and execute a DELETE if it's within a certain range. For example, generate a number from 1-100 and do the query if it is <=1. That translates to a 1% chance each time that expired reservations get cleaned up.

That's basically what PHP does when it handles session files, but the percentage is even lower. 1% may sound like not often enough but that means there's an average of about 12 temporary reservations floating around at a time. Pretty sure I got the statistics right on that.
bogha
Forum Newbie
Posts: 8
Joined: Tue Aug 05, 2008 2:06 am

Re: chekcing a database automatically

Post by bogha »

thanks man for the reply
but i really didn't get your point
is ti possible to give me a small example to demonstrate your point?

best regards
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: chekcing a database automatically

Post by requinix »

Code: Select all

// ...
// here you do your queries and such to get the data you need
// ...
 
$number = rand(1, 100);
if ($number < 5) {
    // query to delete all temporary reservations older than 15 minutes
}
 
// ...
Is that what you were asking about?
bogha
Forum Newbie
Posts: 8
Joined: Tue Aug 05, 2008 2:06 am

Re: chekcing a database automatically

Post by bogha »

i know i disturbed you enough today

but i really didn't get the reason of adding a random number

my idea is as this

Code: Select all

 
// here i check if the number is fully or temporary reserved 
// if temporarly reserved then i will check the date if it's more than 15 min
// if so then i will delete the record and make another record with the status  0 and the current datetime
// if he completes the registration then i will update the flag to be 1
 
 
and thanks again and sorry for disturbing
User avatar
susrisha
Forum Contributor
Posts: 439
Joined: Thu Aug 07, 2008 11:43 pm
Location: Hyderabad India

Re: chekcing a database automatically

Post by susrisha »

but again you will be going to the same problem again with this..

Code: Select all

 
//user a added number 123 and closed the browser at registration page
// now after 15 minutes, the row will be deleted and again added to the table with the new time stamp
//user b who has been trying to get 123 number will have to wait for infinity
 
 
bogha
Forum Newbie
Posts: 8
Joined: Tue Aug 05, 2008 2:06 am

Re: chekcing a database automatically

Post by bogha »

my idea is

Code: Select all

 
// when user b checks the number and find it reserved , then he is the one that will check if the time is >15m and then he will //delete the old one and insert a new temp one with new time stamp 
 
 
User avatar
php_east
Forum Contributor
Posts: 453
Joined: Sun Feb 22, 2009 1:31 pm
Location: Far Far East.

Re: chekcing a database automatically

Post by php_east »

bogha wrote:do you have any suggestion?
ajax. double booking probability must be zero. and no hanging tables at all.
User avatar
McInfo
DevNet Resident
Posts: 1532
Joined: Wed Apr 01, 2009 1:31 pm

Re: chekcing a database automatically

Post by McInfo »

Allow anonymous users to check if a number has been reserved or registered, but allow only logged-in users to reserve or register a number. Have a way to associate the reservation with a specific user. That way, the user who initiates the reservation is the only one who can cancel the reservation or continue on to register the number.

Don't DELETE the record if it is older than 15 minutes, use UPDATE to change the user associated with that number and to update the timestamp. You can leave unregistered numbers in the database. You don't need to run any cleanup routines.

Edit: This post was recovered from search engine cache.
Post Reply