Posted by Jerry Stuckle on 05/29/06 17:02

Dynamo wrote:
> Thanks for that Jerry and others that have posted replies. I guess that because
> of the type of work that I am doing that you all assume I am reasonably
> proficient in php. Well I can assure you that I am not :(
>
> I am still relatively green and date/time manipiulation in databases has never
> been one of my fortes. I get the gist of what you are telling me to do and I
> have created a pending transaction table. So now my confirmpurchase.php looks
> something like this and I would be grateful if you could cast your eye over and
> tell me if this is what you mean plus a little help with $firstquery :
>
> <?php
> $id = $_POST['item_number'];
> $firstquery = "DELETE FROM Pendingtransactions WHERE time=(any records that are
> older than 20mins)";
> $result = mysql_query($firstquery) or die ("Failed query of" . $firstquery);
> mysql_free_result($result);
> $secondquery = "SELECT FROM Pendingtransactions WHERE catid = $id";
> $result = mysql_query($secondquery) or die ("Failed query of" . $secondquery);
> if (mysql_num_rows($result) > 0) {
> echo "Sorry but this item is currently being purchased by somebody else.<br>
> If they choose to cancel their sale then this product will become available
> again in 20 mins";
> }
> else {
> $thirdquery = "INSERT INTO Pendingtransactions (catid) VALUES ('$id')";
> ?>
> <p align="center"><font face="Arial" size="2"><b>
> You have chosen to add the following item to your shopping basket.<br>
> Please click Confirm to continue or Cancel to stop your transaction.<br>
> Please note that this page will time out in 20 minutes. If you do not<br>
> make your purchase within this timescale then the product will become<br>
> available again for others to buy.
> </b></font></p>
> <?php
> //Then display item they are purchasing
> //and add form to send payment to paypal
> //plus form to cancel purchase if they so desire//
> //I'm ok with this part
> }
> ?>
>
> Many thanks in advance.
> Dynamo
>
> In article <e7idnWgWP4Itw-fZ4p2dnA@comcast.com>, Jerry Stuckle says...
>
>>This is a typical problem, especially when working on the web. However, it has
>>existed ever since databases started tracking data :-).
>>
>>One of the worst things you can do is keep this in a session. Session data may
>>not be written to the database immediately, causing you to sell the last item
>>twice. Additionally, session data is not necessarily cleared from the database
>>when the session expires. So you may not be able to sell an item which is
>>available.
>>
>>A database is a good place to keep session data for session data's sake. But
>>don't try to use it to access data from another session.
>>
>>Rather, post the potential sale to a pending transaction table, along with the
>>current timestamp. Then have a proc run which goes through the transaction
>>table and looks for timestamps older than X minutes (i.e 15 min.- whatever your
>>session timeout is). If any are found, add the item back into the inventory and
>>remove the entry from the list.
>>
>>OTOH, if the person completes the sale, remove the item from the pending
>>transaction list and place it on a completed order list.
>>
>>The program which cleans up the pending transaction table can be kicked off via
>>a cron job (my favorite way). Another way would be to execute it when someone
>>tries to purchase an item. In the latter case I would recommend some type of
>>locking action (i.e.an "I am running" entry in a table) to keep more than one
>>process from running at the same time.
>>
>
>

Dynamo,

This really isn't a PHP question - it's database related. I suggest you follow
up in comp.databases.mysql.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

[Back to original message]