Is this possible??

[Aesop]

Hi there,

Ok picture this;  in one mysql table we have 4 names aka our brokers. 

*table "brokers"
-------------------------------------------
bid | bname |
-------------------------------------------
1  | joe
2  | john
3  | mary
4  | joann
-------------------------------------------

On the website we have a contact form in which we treat anyone who contacts us as a lead.  I have a seperate table for storing those leads and the date they contacted us.  Now what I would like to do is setup some kind of query and php conditional statement that auto-assigns each new lead to the broker who is next in line based off of which broker received the last lead.  So if mary got the last one, then joann would be next.  This is the basic overview of the table structure I have;

table "leads"
-------------------------------------------
lid | lname |  bid (the join) | ldate
-------------------------------------------
1  | elvis presley | 3 | 2006-12-01 08:15:00
2  | paris hilton | 4 | 2006-12-01 09:24:45
3  | barney rubble | 1 | 2006-12-04 21:14:45
4  | carmen electra | 2 | 2006-12-05 16:20:00
-------------------------------------------

Is this doable or am I going to be manually assigning them?

[roopurt18]

When you assign a lead, just record it in a table that has:
broker_id, lead_id, date_assigned

When it's time to assign your next lead you can choose the person who has last received a lead with:
[code]$sql = "SELECT broker_id FROM lead_assigns WHERE 1 ORDER BY date_assigned LIMIT 1";[/code]

You will have to make sure that when adding rows to this table to insert if the broker doesn't already exist and to update if they do.  Otherwise it gets a little trickier.

[The Little Guy]

or add a column to brokers, with a number of how many lead's that they have done. When you check who is next, you will need to order by bid, and the first bid that is the lowest would be the next person in line.

[roopurt18]

You would have to order by both number of leads and broker_id, in that order.  Otherwise you run the risk of one broker being chosen twice in a row, which may or may not be desirable.