Page 1 of 1

Help- A tricky and interesting mysql-php problem.....

Posted: Mon May 19, 2003 12:56 am
by coolpravin
Help- A tricky and interesting mysql-php problem.....

I am having a script in php.
This executes a bunch of 3 mysql queries
e.g.

$query1="select * from account where username='$user' and current=1";
$result1=mysql_query($query1);
$total1=mysql_num_rows($result1);
if($total1)
{
$row1=mysql_fetch_object($result1);
$bal=$row1->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,currentrec)values('$user','$pay','$newbal',1)";
$resultacc=mysql_query($queryacc);

what this does is - updates accounts of a user
IF a user purchase 10 items at a time then this buch executes 10 times in a loop.
Ans this is a requirement i can not make it one time.

Now when there are 10-20 such users online at a time and if they purchase 10-10 items
this buch executes 100-200 times
But here it makes problem
It doesnt happen that it first executes all the queries for one user then all the queries for second user.
As they are in loop it supposed to do like that.

for($i=1;$i<=10;$i++)
{
$query1="select * from account where username='$user' and current=1";
$result1=mysql_query($query1);
$total1=mysql_num_rows($result1);
if($total1)
{
$row1=mysql_fetch_object($result1);
$bal=$row1->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,currentrec)values('$user','$pay','$newbal',1)";
$resultacc=mysql_query($queryacc);
}

It executes $query1 for first user and then again $query1 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 thst time and so on...
I know that coding is 100% right.Because it runs well when we allow max purchase of 2-3
But when we increase it to 20 or 30 then above loop executes 20-30 time for each user.
But it gives a lod on sever and hence the probelm starts.

Is anybody having same problem or any solution? :?: