Pdo multiple row insert issue

debugcn Published at Dev

speedy

enter image description here

I'm having some problem with inserting multiple rows in mysql with pdo prepare statements.

The insert works but only once.

I have tried to put the query before the foreach, but same problem.

  $product_id = $_POST['id'];
  if (isset($_POST['product_attribute'])){
        $att_id = array();

    foreach ($_POST['product_attribute'] as $product_attribute) {

      $att_id[] = $product_attribute['attribute_id'];

      if ($product_attribute['attribute_id']) {
          $stmt = $database->prepare("DELETE FROM product_attribute WHERE product_id =:product_id AND attribute_id=:attribute_id");
          $stmt->bindParam(':product_id', $product_id, PDO::PARAM_INT);
          $stmt->bindParam(':attribute_id',  $product_attribute['attribute_id'], PDO::PARAM_INT);
          $stmt->execute();


        foreach ($product_attribute['product_attribute_description'] as $product_attribute_description) {
          $stmt = $database->prepare("INSERT INTO product_attribute SET product_id=:product_id, product_code=:product_code, attribute_id=:attribute_id, `text`=:atext");
          $stmt->bindParam(':product_id', $product_id,  PDO::PARAM_INT);
          $stmt->bindParam(':product_code', $_POST['codprodus'], PDO::PARAM_INT);
          $stmt->bindParam(':attribute_id', $product_attribute['attribute_id'], PDO::PARAM_INT);
          $stmt->bindParam(':atext',  $product_attribute_description['text'], PDO::PARAM_STR);
          $stmt->execute();

        }
      }
    }

    $vals = implode( ",", $att_id );
    $stmt = $database->prepare("DELETE FROM product_attribute WHERE product_id =:product_id AND attribute_id NOT IN(:vals)");
    $stmt->bindParam(':product_id', $product_id, PDO::PARAM_INT);
    $stmt->bindParam(':vals', $vals, PDO::PARAM_STR);
    $stmt->execute();

  }else {
    $stmt = $database->prepare("DELETE FROM product_attribute WHERE product_id =:product_id");
    $stmt->bindParam(':product_id',  $product_id, PDO::PARAM_INT);
    $stmt->execute();
  }

Print_r($_POST['product_attribute'] :

Array([103] => Array(
            [attribute_id] => 103
            [product_attribute_description] => Array
                (
                    [2] => Array
                        (
                            [text] => 56
                        )

                )

        )

    [102] => Array
        (
            [attribute_id] => 102
            [product_attribute_description] => Array
                (
                    [2] => Array
                        (
                            [text] => da
                        )

                )

        )

    [104] => Array
        (
            [attribute_id] => 104
            [product_attribute_description] => Array
                (
                    [2] => Array
                        (
                            [text] => da
                        )

                )

        )

)

And here is my product_attribute table schema.

product_id      int(11)     NO  PRI         
attribute_id    int(11)     NO  PRI         
product_code    int(11)     NO          
text            text        NO

Ryan Vincent

This code is deleting the newly inserted rows. Comment it out and all is fine.

/*
$vals = implode( ",", $att_id );
$stmt = $database->prepare("DELETE FROM product_attribute WHERE product_id =:product_id AND attribute_id NOT IN(:vals)");
$stmt->bindValue(':product_id', $product_id, PDO::PARAM_INT);
$stmt->bindValue(':vals', $vals, PDO::PARAM_STR);
$stmt->execute();
*/

Collected from the Internet

Please contact [email protected] to delete if infringement.