Questions & Answers

I am experiencing a weird issue...

When I use a random value for a column in Maria DB (10.4.27) and PHP (8.2.0), there are 4 new rows inserted, instead of 1.

However, when I see a fixed value for the same column, I get only 1 new record inserted as expected.

Does anyone know what the reason could be?

I am using PDO with prepared statements and bindValue for the columns.

This will insert 4 new records:

$test = [

    'id' => bin2hex(random_bytes(8)),
    'data' => 'same data ...'
];

// This will insert **1 new** records:

$test = [

    'id' => '1a4bcfb5e6693fcd', // <------- changed
    'data' => 'same data ...'
];

I tried different tables with different number of columns. The result is always the same: 4 new records inserted, instead of just 1.

There is only one loop which is inside the 'bindValueFromArray' method to go over the SQL prepare parameters.

I even added counters for that method and 'insert' to see how many times they are called. Both get called 1 time (as expected) but 4 records get inserted.

As I mentioned above, I tried this with different tables, number of columns, Autoincrement or not, UNIQUE or not, same result: 4 records. Except for when I use a literal string instead.

Here is the test file: ` <?php

MysqlPdo::instance(); // creates and assigns a \PDO instance

MysqlPdo::insert(
    table: 'test',
    data: [
        'id' => bin2hex(random_bytes(8)), 
        'data' => 'same data ...'
    ],
    transaction: true
);

?> `

And this is the class file: ` <?php

namespace test\Core\Pdo;

use test\Core\Exceptions\PdoException;

final class MysqlPdo
{
private static ?\PDO $pdo; // \PDO instance

private static int $numberOfBindValueCalled = 0; // test purposes
private static int $numberOfInserts = 0; // test purposes

////// INSERT INTO table 
    //        ( column1, column2, column3 ) 
    // VALUES ( :column1, :column2, :column3 );
    // 
    // $data = [
    //            'columnX' => valueX,
    // ]

private static function bindValueFromArray(
    \PDOStatement $stmt, 
    array $data,
) : bool
{
    try {
        foreach ($data as $key => $val) {
            if (!$stmt->bindValue(":{$key}", $val, 
                    match(true) {
                        is_string($val) => \PDO::PARAM_STR,
                        is_int($val)    => \PDO::PARAM_INT,
                        is_bool($val)   => \PDO::PARAM_BOOL,
                        is_null($val)   => \PDO::PARAM_NULL,
                        default         => \PDO::PARAM_STR
                    })
            ) return false;
        }
    } catch (\PDOException $err) {
        $stmt = null;
        throw PdoException::valueBindingFailed($err);
        return false;
    } finally {
        self::$numberOfBindValueCalled += 1; // test purposes
    }
    return true;
}

public static function insert(
    string $table, 
    array $data, 
    bool $transaction = true,
) : bool
{
    self::$numberOfInserts += 1; // test purposes

    $keys     = [];
    $keysBind = [];

    foreach ( $data as $key => $val) {
        $keys[]     = $key;
        $keysBind[] = ":{$key}";
    }

    $sql  = "INSERT INTO {$table} \n";
    $sql .= "       ( " . implode( ', ', $keys ) . " )\n";
    $sql .= "VALUES ( " . implode( ', ', $keysBind ) . " );";

    if (!self::beginTransaction(self::$pdo, $transaction)) return false;

    $stmt = self::prepare(self::$pdo, $sql);
    if ($stmt === false) return false;

    if (!self::bindValueFromArray($stmt, $data)) return false;

    if (!self::execute($stmt)) return false;

    if (!self::rowCount($stmt)) return false;

    if (!self::commit(self::$pdo, $transaction)) return false;

    return true;
}
}

?> `