RonB
|
| Posted: 09/13/2003, 1:21 AM |
|
I've got some code in an after insert event to perform some database actions. The problem is in the part where I use the while loop to go through the result of a query and update the rb_product table to make sure inventory is reduced by the amount ordered.
I had to open three separate connections to the database to get this working, probably because when using 1 connection you get conflicting resultsets. I'm just wondering if the same thing could be done with les resources because I'm afraid that this code would place a havy load on the server in a production enviroment. So the code below does what it's supposed to do but I feel it's a bit inefficient.
Here's the code:
$orders_AfterInsert = true;
//End orders_AfterInsert
//Custom Code @29-80D51CB7
// -------------------------
global $orders;
$db=new clsDBConnection1();
$db->query("insert into rb_orders_spec select * from rb_temp_orders where order_nr=" .CCGetSession("ident",""));
$db->query("delete from rb_temp_orders where order_nr=" .CCGetSession("ident",""));
unset($db);
$dbTemp=new clsDBConnection1();
$dbTemp1=new clsDBConnection1();
$dbTemp->query("select order_product, order_amount from rb_orders_spec where order_nr=" .CCGetSession("ident",""));
while($dbTemp->next_record())
{
$dbTemp1->query("update rb_product set prod_inventory=(prod_inventory)-" .$dbTemp->f("order_amount") ."where prod_id=" .$dbTemp->f("order_product"));
}
unset($dbTemp);
unset($dbTemp1);
// -------------------------
//End Custom Code
//Close orders_AfterInsert @10-BAFA3CE5
return $orders_AfterInsert;
}
//End Close orders_AfterInsert
Ron
|
|
|
 |
feha
|
| Posted: 09/13/2003, 1:59 AM |
|
|
|
|
|
feha
|
| Posted: 09/13/2003, 1:59 AM |
|
You can use several loop's within same connection if is the same DB.
global $orders;
$db=new clsDBConnection1();
$SQL1="insert into rb_orders_spec select * from rb_temp_orders where order_nr=" .CCGetSession("ident","");
$db->query(SQL1);
$SQL2="delete from rb_temp_orders where order_nr=" .CCGetSession("ident","");
$db->query($SQL2);
//unset($db);
//$dbTemp=new clsDBConnection1();
//$dbTemp1=new clsDBConnection1();
$SQL3="select order_product, order_amount from rb_orders_spec where order_nr=" .CCGetSession("ident","");
$dbtemp=$db->query($SQL3);
while($db->next_record())
{
$SQL4="update rb_product set prod_inventory=(prod_inventory)-" .$dbtemp->f("order_amount") ."where prod_id=" .$dbtemp->f("order_product"));
$db->query->($SQL4);
}
unset($db);
//unset($dbTemp1);
This is the way I would do it.
(the code is not tested)
regards
feha
[www.vision.to]
www.vision.to/webshop/
|
|
|
|
RonB
|
| Posted: 09/13/2003, 3:52 AM |
|
The problem is the resultset that is returned from the query in the while loop. It overides any previous resultset.
so the query that gets the products and amount ordered in the order:
$dbTemp->query("select order_product, order_amount from rb_orders_spec where order_nr=" .CCGetSession("ident",""));
equals to result 1, lets say an array with three product id's and three matching amount numbers.
the query in the loop:
while($dbTemp->next_record())
{
$dbTemp1->query("update rb_product set prod_inventory=(prod_inventory)-" .$dbTemp->f("order_amount") ."where prod_id=" .$dbTemp->f("order_product"));
}
is resultset 2 wich replaces somehow resultset one.This means the
while($dbTemp->next_record())
will return true just once instead of three times.
That's why your code suggestion does not work. I know because it looks exactly like the code I had first.
Ron
|
|
|
|
feha
|
| Posted: 09/13/2003, 7:47 AM |
|
Sorry, I din't understood the problem.
I thought only about efficiency.
Regards
feha
[www.vision.to]
www.vision.to/webshop/
|
|
|
|
Si Cranmer
|
| Posted: 09/15/2003, 7:34 AM |
|
Ron,
Sorry if I am talking out of my ass but I thought (well you can with Oracle) you could "stack" the command and use 1 execute.
ie
$x = ""
loop
$x .= 'update ....;'
end loop
execuet $x
HTH
Si.
|
|
|
|
RonB
|
| Posted: 09/15/2003, 1:13 PM |
|
Hi Si,
The while loop in the php code is sort of doing that already. The problem is that the nex_record()function fetches an array and stores it as a resultset. This resultset is overridden by the query performed inside the loop. Your idea is excelent and it is what I'm doing now. It's just that I do not want to create a heavy workload on the server when a lot of customers are making a purchase. I'd like to stay in the CCS framework and use the functions provided. I'm testing the thing now and if the load on the server becomes to heavy I'll probably revert to the php list construction, get the values from the database into an array and walk through the array much like your oracle example does.
Ron
|
|
|
|
|
