how to assign associative array to variables php

Good day Guru,

A beginner almost getting a bald head here, How can i assign variables to an associative array fetched from the database. I have an associative array which is retrived as follows.What I want is to assign variables to this array and then iteratively insert into another table tbl_product.

Array
(
    [0] => Array
        (
            [pid] => 1
            [pname] => Delta Café
            [pcategory] => Mobiliário de Cozinha
            [purchaseprice] => 120
            [pstock] => 120
            [pdescription] => 120
            [pimage] => 61379859e4d6d.jpeg
        )

    [1] => Array
        (
            [pid] => 2
            [pname] => Baygon
            [pcategory] => Material de Higiene e Limpeza
            [purchaseprice] => 500.58
            [pstock] => 8
            [pdescription] => 
            [pimage] => 613b649f23a5f.jfif
        )

My code,

        
if(isset($_POST['btnaddproduct'])){
    $select=$pdo->prepare("select * from tbl_pending");
    $select->execute();
    
  $row=$select->fetchAll(PDO::FETCH_ASSOC);
   
    foreach($row as $product_details=>$key){
    
        $productname=$product_details['pname'];
        $category=$product_details['pname'];
        $stock=$product_details['stock'];
        $description=$product_details['pdescription'];
        $productimage=$product_details['pimage'];
        
   
    $insert=$pdo->prepare("insert into tbl_product(pname,pcategory,purchaseprice,pstock,pdescription,pimage) values(:pname,:pcategory,:purchaseprice,:pstock,:pdescription,:pimage)");     
     $insert->bindParam(':pname',$productname); 
     $insert->bindParam(':pcategory',$category);
     $insert->bindParam(':purchaseprice',$productprice);
     $insert->bindParam(':pstock',$stock);
     $insert->bindParam(':pdescription',$description);
     $insert->bindParam(':pimage',$productimage);     
   }

I noticed that only 1 item is being inserted. What i want is to iterate through the arrays, insert into the tbl_product table and after the insert is successful I want to delete the inserted roles from the tbl_pending table. I would appreciate the help.

Solution

You're not using prepared statements correctly.

Prepared statements lets you prepare a statement once that you later can reuse multiple times with different values.

You're also using foreach() wrong. In your example:

foreach($row as $product_details => $key)

you're setting $product_details as the array index while the $key will contain the actual data.

See the below example for how it should be:

// Prepare the statement only once, before the loop so we can reuse it inside
$insert = $pdo->prepare("insert into tbl_product(pname,pcategory,purchaseprice,pstock,pdescription,pimage) values(:pname,:pcategory,:purchaseprice,:pstock,:pdescription,:pimage)");

// Iterate each row as $product_details
foreach($row as $product_details) {

    // Now all we need to do is bind the new values for the placeholders    
    $insert->bindParam(':pname', $product_details['pname']); 
    $insert->bindParam(':pcategory', $product_details['pcategory']);
    $insert->bindParam(':purchaseprice', $product_details['pproductprice']);
    $insert->bindParam(':pstock', $product_details['stock']);
    $insert->bindParam(':pdescription', $product_details['pdescription']);
    $insert->bindParam(':pimage', $product_details['pimage']);     
    
    // And now we need to execute the statement to store those bound values
    $insert->execute();
}

Note:
You need to fix some variable/array key names though, since your code seem to have some typos/duplicates (like both productname and category was set to pname etc). But this shows the correct flow.