Error When Preparing a Multiple Insert Query

Error when preparing a multiple insert query

This error you are receiving is because the number of elements in $values & $matches does not match.

If $values & $matches do not contain the same number of elements then the insert will fail, due to the query expecting X params but it is receiving Y data $matches. In your case, $values probably already contains some values, which is the reason for the count mismatch. To avoid that, you must always initialize an array before the loop.

I believe you will also need to ensure the column hash has a unique index on it as well.

$matches = array('1');
$count = count($matches);
$values = [];
for($i = 0; $i < $count; ++$i) {
    $values[] = '(?)';
}

// INSERT INTO DATABASE
$sql = "INSERT INTO hashes (hash) VALUES " . implode(', ', $values) . " ON DUPLICATE KEY UPDATE hash=values(hash)";
$stmt = $dbh->prepare($sql);
$data = $stmt->execute($matches);

Error inserting multiple values with mysqli prepared statement

I think it's seeing $data as a single value

Yes, of course. Why would it do otherwise if by any means it is a single value?

i don't know what to do now

Well, the best thing you could do is to ask a question. Not that stub you asked here but a real question explaining what are you trying to do and why. As there is no such question we can only guess that you need to do a multiple insert, but some peculiar way.

To do so, create a single array that holds all the data.

$data = [];
$data[] = $userid;
$data[] = $ortype;
$data[] = $amount;
$data[] = 1;
$data[] = 3;
$data[] = 500;
$count = count($data);

then create a string with placeholders

$values = implode(',', array_fill(0,  $count, '(?, ?, ?)'));

then create a string with types

$types = str_repeat("iii", $count);

and finally create your query and execute it

$stmt = $conn->prepare("INSERT INTO tranx (user, type, amount) VALUES $values");
$stmt->bind_param($types, ...$data);
$stmt->execute();

MySQL - multiple INSERT INTO statements in query result in useless error message

So, this is strange.

a) The actual error for me:
Source spreadsheet contained a wrongly formatted number on row 516, resulting in the excel #VALUE! error making its way into my generated SQL where a DECIMAL value should have been. MySQL stating an error on line 9 did not really help me finding the problem somewhere around line 5000.

b) Online validators:
Why online validators stated the exact same error even when there was no bad value in the statements remains a mystery. In fact, they still throw the error now on the exact same SQL that just ran correctly on the server.

Mysqli can't insert multiple rows

Just get rid of that multi query thing. Use a prepared statement instead

$stmt = $conn->prepare("INSERT INTO games (name) VALUES (?)");
$stmt->bind_param("s", $name);
foreach($decodeall as $game) {
    $name = $game['name'];
    $stmt->execute();
}
echo "New records created successfully";

Note that your current code with multi_query won't work as intended anyway, even with that silly typo fixed. You will have the result of only first query, having no idea what happened to all others.

Error cannot insert multiple commands into a prepared statement

I've got a tip at the mailing list to get rid of the temp. tables and it works for me:

select r.rid, r.cards, to_char(r.stamp, 'DD.MM.YYYY HH24:MI') as day,
    c.bid, c.trix, c.pos, c.money, c.last_ip, c.quit,
    u.id, u.first_name, u.avatar, u.female, u.city, u.vip > CURRENT_DATE as vip
    from pref_rounds r, pref_cards c, pref_users u
    where u.id = c.id and
    r.rid = c.rid and
    r.rid in (
        select rid
            from pref_cards
            where stamp > CURRENT_TIMESTAMP - interval '1 day' and
            id in (
                select id
                    from pref_money
                    where yw = to_char(CURRENT_TIMESTAMP - interval '1 week', 'IYYY-IW')
                    order by money
                    desc limit 10) and
                   bid = 'Misere' and
                   trix > 0
    )
    order by r.rid, c.pos

---

Related Topics