PDO, update foreach loop after an sql update

[GPhilipp]

Hello Everybody.

i have a problem with the following:

$sql = "SELECT * FROM Inventory WHERE Onhand > 0";
foreach ($o_DB->query($sql) as $row) {
...
$sql_AType = "SELECT * FROM ProdCateg where ProdID = $row['ProdID']";
foreach ($o_DB->query($sql_AType) as $row_AType) {
...
$c_SQL = "UPDATE Inventory SET
Onhand = $n_NewOnhand,
Allocated = $n_NewAllocated
WHERE
ProdID = $row['ProdID']";
$stmt = $o_DB->prepare($c_SQL);
$stmt->Execute();
}
}

Now, the inner foreach loops twice in this scenario.
My problem is that the $row['onhand'] does not get updated.
Any Ideas on how to do this correctly?

Any Help is greatly appreciated!

 

[jpadie]

you can't use PDO like that. when you reuse the connection you lose the data handle. you need to use fetchAll instead.

$sql = "SELECT * FROM Inventory WHERE Onhand > 0";
$s = $o_DB->query($sql);
$results = $s->fetchAll($sql);
foreach ($results as $row):
    $sql_AType = "SELECT * FROM ProdCateg where ProdID = ?";
    $s = $o_DB->prepare($sql_AType);
    $s->execute(array($row['Prog_ID']));
    $results2 = $s->fetchAll();
    $c_SQL = "UPDATE Inventory SET
            Onhand    = ?,
            Allocated = ?
                            WHERE
            ProdID = ?";
    $s->prepare($c_SQL);
    foreach ($results2 as $row2): 
        $s->execute(array($n_NewOnhand, $n_NewAllocated, $row['ProdID']));
    endforeach;
    }
}

but I have no idea where you are setting n_newOnhand and n_NewAllocated.