Best Way to Insert Many Values in MySQLi

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.

How to insert multiple values in a specific field in a table using mysqli?

for what purpose ? why don't you just insert a new row with the same name and description with different ref_number ?

but if you would like that , you can concatenate your new ref_number with the existing ..

first check if it already exist
get its value then concatenate the new ref number ..
or if it doesn't exist , insert a new row ..

$sql = "SELECT `ref_number` FROM `transaction_tbl`
WHERE `name`='$name' AND `description`='$description'";


$query = mysql_query($sql);
if(mysql_num_rows($query) > 0)
{
    $row = mysql_fetch_array($query);
    $new_ref = $row['ref_number'] . '|' . $ref_number;
    $upd = "UPDATE `transaction_tbl` SET `ref_number`='$new_ref'
            WHERE `name`='$name' AND `description`='$description'";
}
else
{
    $ins = "INSERT INTO transaction_tbl (`name`,`description`,`ref_number`)
            VALUES ('$name','$desccription',$ref_number)";
    mysql_query($ins);
}

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');

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.

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.

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();

How to insert multiple records at once with mysqli similar to pdo

You've found one of the bigger gripes people have with the mysqli extension. PDO uses a token binding system where you can pass an array of parameters and keys and PDO will marry them up. Mysqli uses a much more vague binding system which can cause issues when you have an indeterminate number of elements to pass to your array.

A major problem is that mysqli wants to know what type of data to expect as the first argument. If you're not concerned with that level of filtering you could probably skate by just declaring everything a string. If that won't work, add some logic to change between string and integer. To do this, we'll add another parameter to your $insert_values at the start so we can pass the appropriate number of strings in as the first argument

$insert_values = array(0 => '');
for ($a = 0; $a < count($sub_data[$b]); $a++)
{
    $insert_values = array_merge($insert_values, array_values($sub_data[$b][$a]));
    $placeholder[] = '(' . implode(', ', array_fill(0, count($sub_data[$b][$a]), '?')) . ')';
    $insert_values[0] .= 's';
}

$insert_values[0] should now look like ssssss (with the same number of s as elements in your array). I am assuming doing this without refactoring your array_merge, which could cause issues. That string MUST be first because it will become the first argument of the mysqli bind_param call.

We can then use the Reflection class to do the binding

$reflect = new ReflectionClass('mysqli_stmt');
$method = $reflect->getMethod('bind_param');
$method->invokeArgs($prepare, $insert_values);
$prepare->execute();  

---

Related Topics