I want to write script, with function to multiple insert query. let me explain this better.
I have one quantity input in html form.
and I have MySQL query to insertregistered user into the table.
so i want my function to insert this query for "quantity" times.
mysql_query("INSERT INTO `pxlot` (realname,email,address,phone,status,regtime,ip)
VALUES ('$realname','$email','$address','$phone','0','$dateTime','$ip')")
or die (mysql_error()); // Inserts the user.
To insert this for example 3 times. Any suggestions?
In most cases inserting multiple records with one Insert statement is much faster in MySQL than inserting records with for/foreach loop in PHP.
Let's assume $column1 and $column2 are arrays with same size posted by html form.
You can create your query like this:
<?php
$query = 'INSERT INTO TABLE (`column1`, `column2`) VALUES ';
$query_parts = array();
for($x=0; $x<count($column1); $x++){
$query_parts[] = "('" . $column1[$x] . "', '" . $column2[$x] . "')";
}
echo $query .= implode(',', $query_parts);
?>
If data is posted for two records the query will become:
INSERT INTO TABLE (
column1,column2) VALUES ('data', 'data'), ('data', 'data')
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sql = "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('John', 'Doe', '[email protected]');";
$sql .= "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('Mary', 'Moe', '[email protected]');";
$sql .= "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('Julie', 'Dooley', '[email protected]')";
if ($conn->multi_query($sql) === TRUE) {
echo "New records created successfully";
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}
$conn->close();
Source W3schools
If you would like to insert multiple values lets say from multiple inputs that have different post values but the same table to insert into then simply use:
mysql_query("INSERT INTO `table` (a,b,c,d,e,f,g) VALUES
('$a','$b','$c','$d','$e','$f','$g'),
('$a','$b','$c','$d','$e','$f','$g'),
('$a','$b','$c','$d','$e','$f','$g')")
or die (mysql_error()); // Inserts 3 times in 3 different rows
Here are a few ways to do it
INSERT INTO pxlot (realname,email,address,phone,status,regtime,ip)
select '$realname','$email','$address','$phone','0','$dateTime','$ip'
from SOMETABLEWITHTONSOFROWS LIMIT 3;
or
INSERT INTO pxlot (realname,email,address,phone,status,regtime,ip)
select '$realname','$email','$address','$phone','0','$dateTime','$ip'
union all select '$realname','$email','$address','$phone','0','$dateTime','$ip'
union all select '$realname','$email','$address','$phone','0','$dateTime','$ip'
or
INSERT INTO pxlot (realname,email,address,phone,status,regtime,ip)
values ('$realname','$email','$address','$phone','0','$dateTime','$ip')
,('$realname','$email','$address','$phone','0','$dateTime','$ip')
,('$realname','$email','$address','$phone','0','$dateTime','$ip')
While inserting multiple rows with a single INSERT statement is generally faster, it leads to a more complicated and often unsafe code. Below I present the best practices when it comes to inserting multiple records in one go using PHP.
To insert multiple new rows into the database at the same time, one needs to follow the following 3 steps:
INSERT statementUsing database transactions ensures that the data is saved in one piece and significantly improves performance.
PDO is the most common choice of database extension in PHP and inserting multiple records with PDO is quite simple.
$pdo = new \PDO("mysql:host=localhost;dbname=test;charset=utf8mb4", 'user', 'password', [
\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
\PDO::ATTR_EMULATE_PREPARES => false
]);
// Start transaction
$pdo->beginTransaction();
// Prepare statement
$stmt = $pdo->prepare('INSERT
INTO `pxlot` (realname,email,address,phone,status,regtime,ip)
VALUES (?,?,?,?,?,?,?)');
// Perform execute() inside a loop
// Sample data coming from a fictitious data set, but the data can come from anywhere
foreach ($dataSet as $data) {
// All seven parameters are passed into the execute() in a form of an array
$stmt->execute([$data['name'], $data['email'], $data['address'], getPhoneNo($data['name']), '0', $data['regtime'], $data['ip']]);
}
// Commit the data into the database
$pdo->commit();
The mysqli extension is a little bit more cumbersome to use but operates on very similar principles. The function names are different and take slightly different parameters.
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new \mysqli('localhost', 'user', 'password', 'database');
$mysqli->set_charset('utf8mb4');
// Start transaction
$mysqli->begin_transaction();
// Prepare statement
$stmt = $mysqli->prepare('INSERT
INTO `pxlot` (realname,email,address,phone,status,regtime,ip)
VALUES (?,?,?,?,?,?,?)');
// Perform execute() inside a loop
// Sample data coming from a fictitious data set, but the data can come from anywhere
foreach ($dataSet as $data) {
// mysqli doesn't accept bind in execute yet, so we have to bind the data first
// The first argument is a list of letters denoting types of parameters. It's best to use 's' for all unless you need a specific type
// bind_param doesn't accept an array so we need to unpack it first using '...'
$stmt->bind_param('sssssss', ...[$data['name'], $data['email'], $data['address'], getPhoneNo($data['name']), '0', $data['regtime'], $data['ip']]);
$stmt->execute();
}
// Commit the data into the database
$mysqli->commit();
Both extensions offer the ability to use transactions. Executing prepared statement with transactions greatly improves performance, but it's still not as good as a single SQL query. However, the difference is so negligible that for the sake of conciseness and clean code it is perfectly acceptable to execute prepared statements multiple times. If you need a faster option to insert many records into the database at once, then chances are that PHP is not the right tool.
Source: Stackoverflow.com