Re: A tricky and interesting mysql-php problem................

Well, I don't know what you're trying to do, but I think I see a couple of problems. But these are questions, not judgements ... 1. What's this "$prefix".account table? Looks like you are having a lot of little account tables; generally not necessary as the account number in an "account" table should be sufficient identification. HAving tables multiply like rabbits can be a real headache. 2. Why are you carrying a balance field - SQL databases execute SUM() so quickly there's no point. (Exception - historical invoice records, where rounding on the sums can give one-cent grief!) 3. Is the for loop just for testing purposes? Is it cut and pasted from live code, or have you edited it? Are you taking shortcuts with the braces? (Call me anal - but using ALL the required braces , and clicking on "Match brace" in the editor, or saving as a .phps and displaying it has been humbling more than once.) 4. How do you guarantee that $query11 returns only one row? What if it fails? 5. Have you guaranteed unique user ($Cusername) names? 6. Have you written out a 2~3 line statement of what this code is supposed to accomplish? 7. Are you using persistent connections? (Not that this question has any meaning, it's just a random thought.) 8. Are the last two queries supposed to execute whether there's a balance or not? 9. Where is $bal used? 10. How do you know you when have a problem? Have fun - Miles Thompson At 11:01 PM 5/17/2003 +0530, System wrote:

A tricky and interesting mysql-php problem................ I am executing a script in php. This executes a 3 mysql queries e.g. $query11="select * from ".$prefix."account where username='$Cusername' and current=1"; $result11=mysql_query($query11); $total11=mysql_num_rows($result11); if($total11) { $row11=mysql_fetch_object($result11); $bal=$row11->balance; } else $bal=0; $queryc="update ".$prefix."account set currentrec=0 where username='$user'"; $resultc=mysql_query($queryc); $queryacc="insert into ".$prefix."account(username,deposited,balance)values('$user','$pay','$newbal ')"; $resultacc=mysql_query($queryacc); This updates account of a user If user purchase 10 items at a time then this 3 mysql queries executes 10 times in a loop as per the requirement. Now when there are 10-20 such users online at a time and if they purchase 10-10 items this mysql queries executes 100-200 times. Untill here its ok. But here after the problem starts. Mysql should not allow the second user for loop to execute until the first user for loop is completed. It randomly takes the user and starts executing the mysql query in for loop before satisfying the first user. for($i=1,$i<=10;$i++) { $query11="select * from ".$prefix."account where username='$user' and current=1"; $result11=mysql_query($query11); $total11=mysql_num_rows($result11); if($total11) { $row11=mysql_fetch_object($result11); $bal=$row11->balance; } else $bal=0; $queryc="update ".$prefix."account set currentrec=0 where username='$user'"; $resultc=mysql_query($queryc); $queryacc="insert into ".$prefix."account(username,deposited,balance)values('$user','$pay','$newbal ')"; $resultacc=mysql_query($queryacc); } It executes $query11 for first user and then again $query11 for second user then $queryc for first user and so on ............ anything randomly happens here. We just cant predict what will happen. This leads to all wrong accounts .And its a big problem. I want that it should first complete request for first user and then for second and so on..... If first users request is not completed it should hold second user for that time and so on... Is anybody having same problem or any solution? Thank you, Tina... -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php