how to use nested foreach loops in php using mysql queries

Question

hi all i am trying to insert / update data in a table using 2 nested foreach loops... i have tables -

1. temp

vendor_ID | component | Qty

2. stock

stock_ID | component | Qty

$query2 = "SELECT * FROM sample.temp";
$rslt = $dbo->query($query2);
if($rslt->rowCount() > 0)
{
    foreach($rslt as $item)
    {
        $Qty = $item['Qty'];
        $component = $item['component'];
        $vendor_ID = $item['vendor_ID'];
$query5 = "SELECT * FROM sample.stock";
$reslt = $dbo->query($query5);
if($reslt->rowCount() > 0)
{
    foreach($reslt as $itm)
    {
        $Qty1 = $itm['Qty'];
        $stock_ID = $itm['stock_ID'];
        $component1 = $itm['component'];
        if(($vendor_ID!=$stock_ID && $component!=$component1) || ($vendor_ID!=$stock_ID && $component==$component1) || ($vendor_ID==$stock_ID && $component!=$component1))
        { 
        $query6 = "INSERT INTO sample.stock (stock_ID, component, Qty) VALUES ($vendor_ID, '$component', $Qty)";//inserting new entry
            if ($dbo->query($query6))
            {echo "Data inserted !";}
            else {echo "Production not updated!";}
        }
        else { $query4 = "UPDATE sample.stock SET Qty=(Qty+$Q) WHERE stock_ID=$vendor_ID AND component='$component'";//updating single existing entries
            if ($dbo->query($query4))
            {echo "Production updated !";}
            else {echo "Production not updated!";}}
        }
      }
   }
}

Firstly i select a temporary table from where i transfer data into stock table on the basis of 3 checks -

updating existing values in stock table if they have same stock_ID and component as it is in temporary table
inserting new entry in stock table if it is not having the component corresponding to that stock_ID
inserting new entry in stock table if it is having the component but does not corresponds to any stock_ID in stock table

Note- vendor_ID in temporary table is same as stock_ID in stock table.

@Epodax the insert query inserts values infinitely... i dont understand how to use nested foreach for condition checking of each component from both tables... — Kaus
– Kaus, Commented Jan 25, 2016 at 10:27
@iamgory i solved it myself but your code is also correct ! Thanks ! — Kaus
– Kaus, Commented Jan 27, 2016 at 14:30

imnotanelephant · Accepted Answer · 2016-01-25 10:45:08Z

A single foreach would be of more use, you don't need a nested foreach query.

$sql = "SELECT * FROM sample.temp";
$query = $dbo->prepare($sql);
$query->execute();

$result = $dbo->fetchAll(PDO::FETCH_ASSOC);
if(!empty($result)) {
    foreach($result as $item) {
    $Qty = $item['Qty'];
    $component = $item['component'];
    $vendor_ID = $item['vendor_ID'];

    $sql = "SELECT * FROM sample.stock WHERE stock_ID = :vendor_ID";
    $query = $dbo->prepare($sql);
    $query->bindValue(':temp_stock', $vendor_ID);
    $query->execute();
    $result = $query->fetch(PDO::FETCH_ASSOC);

        if (!empty($result)) {
            $sql = "UPDATE sample.stock SET Qty = (Qty + :Qty) WHERE stock_ID = :vendor_ID AND component= :component";
            $query = $dbo->prepare($sql);
            $query->bindValue(':vendor_ID', $vendor_ID);
            $query->bindValue(':component', $component);
            $query->bindValue(':Qty', $Qty);
            $query->execute();

            if ($query->rowCount > 0) {
            echo "Success: Updated $vendor_ID quantity";
            } else {
            echo "Error: Failed to modify $vendor_ID quantity";
            }

        } else {
            $sql = "INSERT INTO sample.stock (stock_ID, component, Qty) VALUES ($vendor_ID, '$component', $Qty)";
            $query = $dbo->prepare($sql);
            $query->bindValue(':vendor_ID', $vendor_ID);
            $query->bindValue(':component', $component);
            $query->bindValue(':Qty', $Qty);
            $query->execute();

            if ($query) {
            echo "Success: Added $vendor_ID";
            } else {
            echo "Error: Failed to add $vendor_ID";
            }
        }
    }
} else {
echo "No results in temp table";
}`

Collectives™ on Stack Overflow

how to use nested foreach loops in php using mysql queries

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related