Using php to assign responsibility

sgtpepper

Here is my dilema.

I have a client who has 3 sales people. When a new client signs up on their site...he would like a sales person to automatically be assigned to the client.

For example:

Bubba signs up as a client. He gets added to the database. The database assigns the sales_person_id as 1

Joe than signs up as a client. He gets added to the database. The database assigns the sales_person_id as 2

Bill Clinton than signs up as a client. He gets added to the database. The database assigns the sales_person_id as 3

Monica Lewisnky than signs up as a client. He gets added to the database. The database assigns the sales_person_id as 1

How can this be accomplished?

Matt_Kindig

This isn't exact code, but just a sketch of one way to do this. I cannot guarentee this is the most efficient way.

First of all, you should include a field that can identify the order in which the clients were added. Either an auto increment client_id field, or maybe the timestamp at creation.

Suppose you use client_id. To get the previously inserted sales_person_id, do something like this:

$result=mysql_query("SELECT sales_person_id FROM tablename ORDER BY timestamp DESC LIMIT 1");
$lastid=mysql_fetch_array($result[0]);

Then increment that id by this:

if($lastid==3) { $nextid=1; }
else { $nextid=$lastid+1; }

then use $nextid as the sales_person_id for the new client, and use

mysql_query("INSERT INTO tablename VALUES ( ..., $nextid, ...)");

This should get you started at least.

wilku

If you only want to have equal number of clients for each sales person, you can use RAND():

SELECT sales_person_id FROM sales_people ORDER BY RAND();

Assuming that random number generator in MySQL is well written, you should have what you want.
Other than that, you can select for the next client, a sales person, who has the least clients (this will only work, if you already have all sales persons ids in clients table):

SELECT COUNT(*) FROM clients GROUP BY sales_person_id;

sgtpepper

my query is now this

SELECT COUNT(*),loid FROM loan_officers_assignment GROUP BY loid;

My results are:

count(*) loid
42 1
40 2
44 3
46 4

So this looks like it is giving me.....the cumber of clients associated with an loid. So now, how can I make the script smart enough to know that loid 2 needs to be the next to have a client assigned to it???

wilku

Make an array of your results (loid=>count), sort it using [man]asort[/man] and get the first [man]key[/man].

sgtpepper

I am still a php novice. Can you please give me an example of code? I promise I will read more details in the manual. I am still learning arrays. I have done simple arrays using [].