PHP & MySQL Problem, query execution order ....

[CheesierAngel]

Hi, i really don't know which forum to post this in but i got a mysql php problem.

I would like to calculate the number of assigned customers to an operator and link the new customers to the operator who has the fewest customers assigned too. I already wrote some code but the problem is that before the insert is executed the new count calculation is already performed... So if there are 3 new customers, they will all three be assigned to one operator?

I already tried to use LOW_PRIORITY and HIGH_PRIORITY inside my queries but the MySQL server doesn't support this yet. (can't upgrade because i don't have the access to do so) I rather do not wanna use a sleep function because if the server is busy there might be a chance the problem occurs again.

Any suggestions ?
[code]
<?php
/* ... */
foreach($newCustomers as $newCustomer) {
print $newCustomer . '\n';
/* Gather Online Operators With The Fewest Assigned Customers */
$sql = <<<SQL
SELECT u.id as `operatorId`, COUNT(a.customer_id) as `assignments`
FROM users u
LEFT JOIN assignments a ON a.user_id = u.id
WHERE u.id <> 0
AND u.role_id = 3
AND u.active = 1
AND u.online = 1
GROUP BY operatorId
ORDER BY assignments ASC
SQL;
$results = $cCustomer->query($sql);
/* Assign New Customer To Operator */
if(!empty($results)) {
$operatorId = $results[0]['u']['operatorId'];
$sql = <<<SQL
INSERT INTO assignments
VALUES($newCustomer, $operatorId)
SQL;
$cCustomer->query($sql);
$sql = <<<SQL
UPDATE users
SET tsAssigned = NOW()
WHERE id = $operatorId
SQL;
$cCustomer->query($sql);
}
}
/* ... */
?>
[/code]

The query executions may look strange but this code is written inside the CMS cakePHP. (There are no syntax errors!)
If you liked some explanation on some line, just ask ...

Thanks a lot

[CheesierAngel]

Found some temporary solution, still all suggestions welcome !!

[code]
<?php
/* ... */
                $lastAssigned = "";
foreach($newCustomers as $newCustomer) {
print $newCustomer . '\n';
/* Gather Online Operators With The Fewest Assigned Customers */
$sql = <<<SQL
SELECT u.id as `operatorId`, COUNT(a.customer_id) as `assignments`
FROM users u
LEFT JOIN assignments a ON a.user_id = u.id
WHERE u.id <> 0
AND u.role_id = 3
AND u.active = 1
AND u.online = 1
$lastAssigned                                                //-> Extra check
GROUP BY operatorId
ORDER BY assignments ASC
SQL;
$results = $cCustomer->query($sql);
/* Assign New Customer To Operator */
if(!empty($results)) {
$operatorId = $results[0]['u']['operatorId'];
if(isset($results[1])) {                                            //-> last operator may not be used (if there are more then 1)
$lastAssigned = "AND u.id <> $operatorId";
}
$sql = <<<SQL
INSERT INTO assignments
VALUES($newCustomer, $operatorId)
SQL;
$cCustomer->query($sql);
$sql = <<<SQL
UPDATE users
SET tsAssigned = NOW()
WHERE id = $operatorId
SQL;
$cCustomer->query($sql);
}
}
/* ... */
[/code]