[SOLVED] Performing Multiple Update Statements While Fetching Row ONLY processing 1

[lmktech]

Hi Guys,

 

I seem to be having a problem where I am trying to process 2 or more sql update statements with variable data, during a while odbc_fetch_row function. It only updates 1 record, the first row it fetches from my table. However if I choose to echo the $query rather than odbc_exec the query, it shows them both/all (if more than 2 entries in my table)  :-\

 

function update_details($whse, $code, $qty, $auxkey, $newsinqty, $newsoutqty) {

global $today;

$query = "UPDATE stock SET stock_in = '".$newsinqty."', stock_out = '".$newsoutqty."' WHERE name = '".$code."' AND location = '".$whse."'";

get_result($query); // if i replace this line with echo $query it shows me the multiple queries the function below makes this return

$query2 = "UPDATE staux000 SET lupdate = '".$today."', lupdateby = 'STOCK TRANSFER' WHERE auxkey = '".$auxkey."'";

get_result($query2); // again as above, replace with echo $query2 i get the multiple queries

}

 

function process_whse_details($fromorto) {

global $result, $count;

if ($fromorto == "From") {

  $whse = "fromwhse"; }

else {

  $whse = "towhse"; }

$query = "select s.name, s.stock_in, s.stock_out, s.location, RIGHT(s.number, 7) as \"number\", t.code, t.".$whse.", t.qty from stock s, stktrans t where t.code = s.name and s.location = t.".$whse;

get_result($query);

$count = 0;

while (odbc_fetch_row($result)) {

  $$whse = strtoupper(trim(odbc_result($result, $whse)));

  $code = addslashes(strtoupper(trim(odbc_result($result, "code"))));

  $qty = odbc_result($result, "qty");

  $sinqty = odbc_result($result, "stock_in");

  $soutqty = odbc_result($result, "stock_out");

  $auxkey = odbc_result($result, "number");

  if ($fromorto == "From") {

    $newsinqty = $sinqty - $qty;

    $newsoutqty = $soutqty + $qty; }

  else {

    $newsinqty = $sinqty + $qty;

    $newsoutqty = $soutqty; }

  update_details($$whse, $code, $qty, $auxkey, $newsinqty, $newsoutqty);

  ++$count;

}

}

 

function get_result($query)  {

global $result, $con_id;

$result = odbc_exec($con_id, $query);

}

 

 

This is my stktrans table:

 

CODE	FROMWHSE	TOWHSE	QTY
DABW/1716	PADS01	GOSF01	1
DRID/14106	PADS01	GOSF01	1

 

Thankyou in advanced.

 

[btherl]

What happens if you both echo and execute the queries?  Can you do that and copy and paste the exact queries that it prints out so we can examine them?

[lmktech]

The "From" statement the echo produces, and again is only writing the one.

 

UPDATE stock SET stock_in = '0', stock_out = '4' WHERE name = 'DABW/1716' AND location = 'PADS01'

UPDATE staux000 SET lupdate = '2007-12-25', lupdateby = 'STOCK TRANSFER' WHERE auxkey = '0065068' 

 

This is just html output with a header and the count of items.

 

Stock Transfered From

 

1 Records Transfered

 

 

The "To" statement.

 

UPDATE stock SET stock_in = '1', stock_out = ' 1' WHERE name = 'DABW/1716' AND location = 'GOSF01'

UPDATE staux000 SET lupdate = '2007-12-25', lupdateby = 'STOCK TRANSFER' WHERE auxkey = '0230420' 

 

Again the html output.

 

Stock Transfered To

 

1 Records Received

[lmktech]

I found my issue, silly me musn't have been thinking straight, too much beer over christmas 

 

When I call my get_result function it resets the global $result var which the while function is using inside my process_whse_details function.

 

Solved the problem with another function to just process the sql statement, and not actually set the result to a var.

 

New function:

 

function get_result_non_global($query) {

global $con_id;

odbc_exec($con_id, $query);

}

 

Modified code in update_details function:

 

$query = "UPDATE stock SET stock_in = '".$newsinqty."', stock_out = '".$newsoutqty."' WHERE name = '".$code."' AND location = '".$whse."'";

get_result_non_global($query);

$query2 = "UPDATE staux000 SET lupdate = '".$today."', lupdateby = 'ALEX FINEKI' WHERE auxkey = '".$auxkey."'";

get_result_non_global($query2);

 

Thanks though.

[btherl]

Good to see you've solved it

 

That's why it's a good idea to execute the queries as well as echoing them out.  If you echo only, then you don't get any of the side effects that may come from executing the queries, and that can give you misleading results.