PHP MySQLi Multiple Inserts

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.

Best way to INSERT many values in mysqli?

You should be able to greatly increase the speed by putting your inserts inside a transaction. You can also move your prepare and bind statements outside of your loop.

$array = array("array", "with", "about", "2000", "values");
$query = "INSERT INTO table (link) VALUES (?)";
$stmt = $mysqli->prepare($query);
$stmt ->bind_param("s", $one);

$mysqli->query("START TRANSACTION");
foreach ($array as $one) {
    $stmt->execute();
}
$stmt->close();
$mysqli->query("COMMIT");

---

I tested this code with 10,000 iterations on my web server.

Without transaction: 226 seconds.
With transaction: 2 seconds.
Or a two order of magnitude speed increase, at least for that test.

Mysqli and binding multiple value sets during insert

Simple:

$stmt = $mysqli->prepare("INSERT INTO some_names (firstName, lastName) VALUES (?, ?),(?,?),(?,?)")
$stmt->bind_param('ssssss', 'Joe', 'Smith','Fred','Sampson','Lisa','Pearce');

PHP MySQLi Multiple Inserts

I went ahead and ran a test where one query uses a prepared statement, and the other builds the entire query then executes that. I'm probably not making what I'm wanting to know easy to understand.

Here's my test code. I was thinking prepared statements sort of held back execution until a $stmt->close() was called to optimize it or something. That doesn't appear to be the case though as the test that builds the query using real_escape_string is at least 10 times faster.

<?php

$db = new mysqli('localhost', 'user', 'pass', 'test');

$start = microtime(true);
$a = 'a';
$b = 'b';

$sql = $db->prepare('INSERT INTO multi (a,b) VALUES(?, ?)');
$sql->bind_param('ss', $a, $b);
for($i = 0; $i < 10000; $i++)
{
    $a = chr($i % 1);
    $b = chr($i % 2);
    $sql->execute();
}
$sql->close();

echo microtime(true) - $start;

$db->close();

?>

How to run multiple insert query in SQL using PHP in one go?

The problem is, I need to insert data to first table and if its successfully inserted then only run query for the second table.

This means you need a transaction.

A transaction is a set of queries that either all execute ok or if one fails - they all fail. This is to ensure you don't end up with crap data in your tables.

Do not

• Do not use multiquery.
• Do not use mysql_* function(s).
• Do not use bulk inserts.

People telling you to do that just have absolutely no clue what they're doing, ignore them.

Do

• Use PDO
• Use prepared statements
• Prepare the statement(s) ONCE, use them MULTIPLE times

Sample code - do NOT copy paste

$dsn = 'mysql:dbname=testdb;host=127.0.0.1;charset=utf8mb4';
$user = 'dbuser';
$password = 'dbpass';

$pdo = new PDO($dsn, $user, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$insert['first'] = $pdo->prepare("INSERT INTO table SET col1 = :val, col2 = :val2");
$insert['second'] = $pdo->prepare("INSERT INTO another_table SET col1 = :val, col2 = :val2"); 

$pdo->beginTransaction();

$insert['first']->bindValue(':val', 'your value');
$insert['first']->bindValue(':val2', 'anothervalue');
$insert['first']->execute();

$insert['second']->bindValue(':val', 'your value');
$insert['second']->bindValue(':val2', 'anothervalue');
$insert['second']->execute();

$pdo->commit();

The code above will save the data in two tables ONLY if both inserts are successful.

php - insert multiple rows into mysql from form loop

If you have 80 elements with this name:

name='username'

Then how will this code know which one to use?:

$_POST['username']

Essentially, as your browser builds the form submission, each successive element of the same name over-writes the previous one. So you end up with only the last record.

Instead, give them an array-style name:

name='username[]'

(Repeat for all your duplicated form elements.)

Then in the PHP code, this would itself be an array:

$_POST['username']

You could loop over that array:

for ($i = 0; $i < count($_POST['username']); $i++) {
    $username = $_POST['username'][$i];
    $title = $_POST['title'][$i];
    // etc.
}

This assumes that all of your arrays will be the same length. Which, given this HTML, I suppose they should be. But you can add error checking for that just in case. Either way, each iteration of the loop would build variables equating to that "record" which was submitted.

From there you should be able to build your SQL query with those variables. However, please note that your current way of doing that is extremely unsafe and wide open to SQL injection. To correct that, this is a good place to start.

Side note: Your HTML has invalid id attributes. Additionally, if you want to specify id attributes in that loop, you're going to need to ensure that they are somehow different. Duplicated ids in HTML is invalid.

Get insert_id for all rows inserted in single mysqli query (multiple values)

It should be safe to assume that your ids are mysql_insert_id + the next 3 (whatever) in succession, as your statement is done in one transaction

---

Related Topics