CODEWITHSUNDEEP
phpmysqlooppdo
speedy
speedy

Reputation: 375

Pdo multiple row insert issue

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

Upvotes: 0

Views: 370

Answers (1)

Ryan Vincent
Ryan Vincent

Reputation: 4513

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();
*/

Upvotes: 1

Related Questions