menu

Questions & Answers

Why am I having multiple rows inserted when I use random values?

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;
}
}

?> `

Comments:
2023-01-18 23:01:04
Please show the code that performs the inserts.
2023-01-18 23:01:05
We don't appear to have a minimal reproducible example of the issue. See also How to Ask. bintohex simply returns a single string, so it's unclear how that alone could possibly cause the issue you describe. I smell a coincidence. Once we have an end-to-end example of what you're doing with this data, we might be able to spot the real issue more easily.
2023-01-18 23:01:05
How you assign the id key can't possibly change the number of rows that are inserted. There must be a loop somewhere. Please post a minimal reproducible example.
2023-01-18 23:01:05
Your code is probably called four times instead of once. If you specify ID as constant only 1 insert is successful because other will cause duplicate key error. If the ID is generated randomly every time, there is no conflict in ID field and all inserts are successful.
2023-01-18 23:01:05
Updated my post with the codes...
2023-01-18 23:01:05
Probably something is calling MysqlPdo::insert multiple times then. Have you attempted to do any debugging?
Answers(0) :