Mysql: auction problems

[erikperik]

Hi!

I have some problems with a website. I posted a similar question in the php forum but I hope that it would be possible to solve this with some mysql code. The server uses the MyISAM engine.

 

The idea is that users bid on items that they want. If no bids have been received for 2 minutes, the auction is closed. So far, so good.

I want to build a bid agent so that the users can say "i want to bid at most 25 bids and i want to go as high as X dollars". That means that if noone bids for 2 mins, check bid agents and enter bids from different users with a time difference of 2 minutes until (bidtime+2 mins > servertime, or until all bidagents have placed all their maximum bids) Also to never put 2 following bids from the same user. What is possible though is: "user1 bids 1, user2 bids 2, user1 bids 3, user2 bids 4" and so on.

 

The information that I  need is the following:

For every user:

login

pass

email

adress

postal code

etc, etc

 

For every auction:

auction id

status (3 = open, 2 = pending result (verification), 1 = closed, 0 = closed and hidden from display on the front page)

 

for every bidagent:

auction id

max bid

max number of bids

login (for the user who owns the bidagent)

 

for every bid:

auction id

time of bid

bidder

bid amount

 

My way to solve this is to require_once('bidagents.php') on every page.

Bidagents.php checks if time for last bid was more than 2 mins ago. If so, select a bidagent (table bidagent includes "username, maxbid, maxnumberofbids, auctionid").

 

Then iterate and while timeoflastbid was more than 120 seconds ago, bid from a bidagent, update bidagent (decrease maxnumberofbids). This seems very ineffective since I need to iterate through the bidagents and i also get some problems with database not updating until i do a mysql_close(). Also, with many users, it seems unnecessary to check bidagents for every single one, maybe it would be wise to set a updateinterval of 5 seconds or something? (but how...?)

 

So my questions are 

1) How would you build this database? (relations, triggers etc)

2) Any ideas of how a better idea to solve this? Maybe write the whole thing in SQL with triggers, loops etc?

 

[fenway]

Why is this running on the client side?