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.
How do you execute multiple sql insert statements in one php page
Your code is close to correct. I tweaked it a bit.
// establish DB connection (this includes creates $conn variable)
$conn = mysqli_connect("127.0.0.1", "app", "aaaa", "sss");
if ( ! $conn ) {
throw new Exception('Connection failed');
}
$userId = 123;
$supplierId = 200;
$order_items = [
['prodId' => 2000, 'qty' => 5, 'costPrice' => 1.25],
['prodId' => 2001, 'qty' => 7, 'costPrice' => 2.25],
['prodId' => 2002, 'qty' => 9, 'costPrice' => 1.20],
['prodId' => 2003, 'qty' => 15, 'costPrice' => 3.25],
['prodId' => 2004, 'qty' => 25, 'costPrice' => 5.22],
['prodId' => 2005, 'qty' => 35, 'costPrice' => 5.25],
['prodId' => 2006, 'qty' => 45, 'costPrice' => 11.51],
];
// create PO record
$sql = "INSERT INTO purchaseorders (PODate, supplierId, userId) VALUES (?,?,?);";
$stmt = mysqli_stmt_init($conn);
if (!mysqli_stmt_prepare($stmt, $sql)) {
// header("Location: ../place.php?error=sqlerror");
throw new Exception("SQL error! " . mysqli_error($conn));
}
else {
$date = date('Y-m-d H:i:s');
mysqli_stmt_bind_param($stmt, "sss", $date, $supplierId, $userId);
mysqli_stmt_execute($stmt);
}
// // get last PO record id
$POId = mysqli_insert_id($conn);
echo "POId = " . print_r($POId, true) . "\n";
$sql1 = "INSERT INTO podetails (PONumber, productId, poquantity,
pocostPrice, delivered, paidStatus) VALUES
(?,?,?,?,?,?);";
$stmt1 = mysqli_stmt_init($conn);
if (!$stmt1) {
throw new Exception("SQl Connect error! " . mysqli_error($conn));
}
if (!mysqli_stmt_prepare($stmt1, $sql1)) {
throw new Exception("SQl Connect error! " . mysqli_error($conn));
}
// for each record in grid - create PO details record
foreach ($order_items as $item) {
$delivered = "false";
$paidStatus = "false";
if ( ! mysqli_stmt_bind_param($stmt1, "ssssss", $POId,
$item['prodId'], $item['qty'], $item['costPrice'], $delivered, $paidStatus) ) {
throw new Exception("SQl error! " . mysqli_error($conn));
}
if( ! mysqli_stmt_execute($stmt1) ) {
throw new Exception("SQl error! " . mysqli_error($conn));
}
}
echo "PO Placed: PO Number = ".$POId . "\n";
I made these tables to run it locally.
CREATE TABLE `purchaseorders` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`PODate` date DEFAULT NULL,
`supplierId` int(10) unsigned DEFAULT NULL,
`userId` int(10) unsigned DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;
CREATE TABLE `podetails` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`PONumber` int(10) unsigned DEFAULT NULL,
`productId` int(10) unsigned DEFAULT NULL,
`poquantity` int(11) DEFAULT NULL,
`pocostPrice` decimal(9,2) DEFAULT NULL,
`delivered` varchar(20) NOT NULL DEFAULT 'false',
`paidStatus` varchar(20) NOT NULL DEFAULT 'false',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8;
Multiple mysql INSERT statements in one query php
For what it's worth, and depending on if you're inserting the same data into
the same tables, it's much better to insert multiple values with the one insert
e.g.
INSERT INTO a VALUES (1,23),(2,34),(4,33);
INSERT INTO a VALUES (8,26),(6,29);
Insert Multiple rows of mysql using php
Just build your query in a loop and then execute it when the loop is compete
require_once("connection.php");
$sql = "INSERT INTO results (id,swim_rank) VALUES ";
for ($i = 0; $i < 7; $i++) {
$sql .= "('".$id1[$i]."','".$timeRank1[$i]."'),";
}
$sql = rtrim($sql, ',');
// run your query here
You'll also notice I moved your include of your DB connection outside of the loop. No need to repeatedly make that call.
Also, make sure you either escape those values being inserted or use parametized queries to make your inserts to protect against SQL injections.
Insert multiple rows with one query MySQL
INSERT INTO table (a,b) VALUES (1,2), (2,3), (3,4);
http://dev.mysql.com/doc/refman/5.5/en/insert.html
PDO Prepared Inserts multiple rows in single query
Multiple Values Insert with PDO Prepared Statements
Inserting multiple values in one execute statement. Why because according to this page it is faster than regular inserts.
$datafields = array('fielda', 'fieldb', ... );
$data[] = array('fielda' => 'value', 'fieldb' => 'value' ....);
$data[] = array('fielda' => 'value', 'fieldb' => 'value' ....);
more data values or you probably have a loop that populates data.
With prepared inserts you need to know the fields you're inserting to, and the number of fields to create the ? placeholders to bind your parameters.
insert into table (fielda, fieldb, ... ) values (?,?...), (?,?...)....
That is basically how we want the insert statement to look like.
Now, the code:
function placeholders($text, $count=0, $separator=","){
$result = array();
if($count > 0){
for($x=0; $x<$count; $x++){
$result[] = $text;
}
}
return implode($separator, $result);
}
$pdo->beginTransaction(); // also helps speed up your inserts.
$insert_values = array();
foreach($data as $d){
$question_marks[] = '(' . placeholders('?', sizeof($d)) . ')';
$insert_values = array_merge($insert_values, array_values($d));
}
$sql = "INSERT INTO table (" . implode(",", $datafields ) . ") VALUES " .
implode(',', $question_marks);
$stmt = $pdo->prepare ($sql);
$stmt->execute($insert_values);
$pdo->commit();
Although in my test, there was only a 1 sec difference when using multiple inserts and regular prepared inserts with single value.
is there an alternate to using a for loop to insert multiple queries
Please See this query hope this will be improve our code and speed.
Avoid doing SQL queries within a loop
A common mistake is placing a SQL query inside of a loop. This results in multiple round trips to the database, and significantly slower scripts. In the example below, you can change the loop to build a single SQL query and insert all of your users at once.
foreach ($userList as $user) {
$query = 'INSERT INTO users (first_name,last_name) VALUES("' . $user['first_name'] . '", "' . $user['last_name'] . '")';
mysql_query($query);
}
Instead of using a loop, you can combine the data into a single database query.
$userData = array();
foreach ($userList as $user) {
$userData[] = '("' . $user['first_name'] . '", "' . $user['last_name'] . '")';
}
$query = 'INSERT INTO users (first_name,last_name) VALUES' . implode(',', $userData);
mysql_query($query);
How to use two queries in the same php file
1) Check your first query syntax. It should be like:
$sql = "INSERT INTO table1(mail, number, device, price, paymenttype,status,date) VALUES ('$name', '$email', '$number', '$device', '$price', '$payment','$status',NOW())";
2) You have closed the connection before second insert query.
Related Topics
Return Multiple Response Data in One Response
Mysqli_Stmt::Execute() Expects Exactly 0 Parameters, 1 Given
How to Return a Proper Success/Error Message for Jquery .Ajax() Using PHP
How to Alias a Function in PHP
Getting Static Property from a Class with Dynamic Class Name in PHP
How to Display Currency in Indian Numbering Format in PHP
PHP Strtotime "Last Monday" If Today Is Monday
Who Add "_" Single Underscore Query Parameter
Get Last Modified File in a Directory
How to Set 777 Permission on a Particular Folder
Codeigniter Sessions Not Working After Migration
What Is the Advantage of Using Try {} Catch {} Versus If {} Else {}
Codeigniter: Best Way to Structure Partial Views
How to Echo the Whole Content of a .HTML File in PHP