CheesierAngel Posted December 7, 2006 Share Posted December 7, 2006 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 ASCSQL; $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 = $operatorIdSQL; $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 Quote Link to comment Share on other sites More sharing options...
CheesierAngel Posted December 7, 2006 Author Share Posted December 7, 2006 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 ASCSQL; $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 = $operatorIdSQL; $cCustomer->query($sql); } } /* ... */[/code] Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.