pdo multiple rows inserting problem?

[shams]

After search i got this piece of php code to insert multiple rows to mysql database but this print error:

Fatal error: Uncaught PDOException: SQLSTATE[HY000]: General error: 2031 in /var/www/quran.eu.org/insert/pdotest.php:31 Stack trace: #0 /var/www/quran.eu.org/insert/pdotest.php(31): PDOStatement->execute() #1 {main} thrown in /var/www/quran.eu.org/insert/pdotest.php on line 31

   This is pdotest.php to insert values:

<?php
error_reporting(E_ALL);
ini_set('display_errors', '1');
$host = 'localhost';
$db   = 'test';
$user = 'user';
$pass = '';
$charset = 'utf8mb4';

$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];
$pdo = new PDO("mysql:host=$host;dbname=$db;charset=$charset", $user, $pass, $options);
///////
$cat_id=isset($_POST['cat-id']);
$subcategory=isset($_POST['subcategory']);
$subcat2=isset($_POST['subcat2']);
$subcategory2=isset($_POST['subcategory2']);
$pdo->beginTransaction();
///...
$stmt = $pdo->prepare('INSERT INTO subcategory (cat_id, subcategory, subcat2) VALUES (:cat_id, :subcategory, :subcat2), (:cat_id, :subcategory2, :subcat2)');
$stmt->bindValue(':cat_id', $cat_id);
$stmt->bindValue(':subcategory', $subcategory);
$stmt->bindValue(':subcat2', $subcat2);
$stmt->bindValue(':cat_id', $cat_id);
$stmt->bindValue(':subcategory', $subcategory2);
$stmt->bindValue(':subcat2', $subcat2);
///...
$stmt->execute();
?>

 

[requinix]

You can't reuse placeholders and you can't bind multiple values to one.

Split :cat_id into two. And fix your second :subcategory.

[Barand]

Alternatively

$stmt = $pdo->prepare('INSERT INTO subcategory (cat_id, subcategory, subcat2) VALUES (?, ?, ?), (?, ?, ?)');
$stmt->execute( [ $cat_id,
                  $subcategory,
                  $subcat2,
                  $cat_id,
                  $subcategory2,
                  $subcat2
                ] );  

However, IMHO, your problem starts with your form. Don't use input name like "subcategory", "subcategory2", ... , "subcategoryN".

Instead use array notation "subcategory[1]", "subcategory[2]", ... , "subcategory[N]". Then you can use a loop in the processing

$stmt = $pdo->prepare('INSERT INTO subcategory (cat_id, subcategory, subcat2) VALUES (?, ?, ?)');
                
foreach ($_POST['subcategory'] as $sc) {
    $stmt->execute( [ $cat_id,
                      $sc,
                      $subcat2
                    ] );  
}

 

[ginerjm]

Plus - you are getting only True/False values in your variables from those isset() calls.  Don't you want a real value instead?