How to insert an array into a single MySQL Prepared statement w/ PHP and PDO
You could build the query programatically...:
$sql = 'INSERT INTO table (memberID, programID) VALUES ';
$insertQuery = array();
$insertData = array();
foreach ($data as $row) {
$insertQuery[] = '(?, ?)';
$insertData[] = $memberid;
$insertData[] = $row;
}
if (!empty($insertQuery)) {
$sql .= implode(', ', $insertQuery);
$stmt = $db->prepare($sql);
$stmt->execute($insertData);
}
PHP - MySQL prepared statement to INSERT an array
No... this was definitely harder than PDO with any array because of how mysqli_stmt_bind_param() works... and this works fine by changing $array to removing/adding data for other columns.
$mysqli = new mysqli('localhost', 'root', 'password', 'test');
$array = array("name"=>"pineapple", "color"=>"purple");
$table_name = "fruit";
insert_data($mysqli, $array, $table_name);
function insert_data($mysqli, $array, $table_name)
{
$placeholders = array_fill(0, count($array), '?');
$keys = array();
$values = array();
foreach($array as $k => $v) {
$keys[] = $k;
$values[] = !empty($v) ? $v : null;
}
$query = "insert into $table_name ".
'('.implode(', ', $keys).') values '.
'('.implode(', ', $placeholders).'); ';
// insert into fruit (name, color) values (?, ?);
$stmt = $mysqli->prepare($query);
// create a by reference array...
$params = array();
foreach ($array as &$value) {
$params[] = &$value;
}
$types = array(str_repeat('s', count($params)));
$values = array_merge($types, $params);
/*
$values = Array
(
[0] => ss
[1] => pineapple
[2] => purple
)
*/
call_user_func_array(array($stmt, 'bind_param'), $values);
$success = $stmt->execute();
if ($success) { print "it worked..."; }
else { print "it did not work..."; }
}
I got some help from these SO posts:
- https://stackoverflow.com/a/15933696/623952
- https://stackoverflow.com/a/6179049/623952
So... in $stmt->bind_param() the first parameter is a string that has one char for each parameter passed in. And that char represents the parameter data type. In the example above, both of the two parameters are strings so it becomes ss. A string is always assumed in the example above, too.
I found this chart in the bind_param() documentation:
types
A string that contains one or more characters which specify the types for the corresponding bind variables:
Type specification chars
Character Description
i corresponding variable has type integer
d corresponding variable has type double
s corresponding variable has type string
b corresponding variable is a blob and will be sent in packets
Best practice inserting array to DB with PDO
Proper example is something like this:
$arr = ["10","11","12","13"];
$sql = "INSERT INTO `test` (`user_id`, `view`) VALUES (:user_id, :view)";
$stmt = $this->conn->prepare($sql);
foreach ($arr as $value) {
$stmt->execute(array('user_id' => $value, 'view' => 'small'));
}
How to insert an array as a PROCEDURE using PDO to MYSQL
You could be a little creative and build the entire query dynamically by interrogating the database schema and determining the parameters used within the stored procedure and from that build the placeholder string and generate the correctly formatted data parcel used in the execute method. For instance:
Given some source data to emulate the $_POST data in the question.
$_POST=array(
array(
'hidden_pid'=>23,
'hidden_p_code'=>'abc-34',
'hidden_p_name'=>'geronimo',
'hidden_dekorA'=>'blah',
'hidden_p_quantity'=>43,
'hidden_p_listprice'=>99,
'hidden_p_netprice'=>120,
'hidden_p_total'=>150,
'hidden_preorderno'=>2,
'hidden_yetkili'=>'tiger'
),
array(
'hidden_pid'=>65,
'hidden_p_code'=>'def-72',
'hidden_p_name'=>'flatfoot',
'hidden_dekorA'=>'aarrrggghhh',
'hidden_p_quantity'=>643,
'hidden_p_listprice'=>69,
'hidden_p_netprice'=>420,
'hidden_p_total'=>150,
'hidden_preorderno'=>8,
'hidden_yetkili'=>'leopard'
),
array(
'hidden_pid'=>84,
'hidden_p_code'=>'toto-x1',
'hidden_p_name'=>'desperate dan',
'hidden_dekorA'=>'zing',
'hidden_p_quantity'=>98,
'hidden_p_listprice'=>89,
'hidden_p_netprice'=>92,
'hidden_p_total'=>100,
'hidden_preorderno'=>5000,
'hidden_yetkili'=>'lynx'
)
);
And the given table structure
mysql> describe siparis;
+-------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------------+------+-----+---------+-------+
| product_id | int(10) unsigned | NO | MUL | 0 | |
| p_code | varchar(50) | YES | MUL | NULL | |
| p_name | varchar(50) | YES | | NULL | |
| p_type | varchar(50) | YES | | NULL | |
| p_quantity | varchar(50) | YES | | NULL | |
| p_listprice | varchar(50) | YES | | NULL | |
| p_netprice | varchar(50) | YES | | NULL | |
| p_total | varchar(50) | YES | | NULL | |
| preorderno | int(11) | YES | | NULL | |
| yetkili | varchar(10) | YES | | NULL | |
+-------------+------------------+------+-----+---------+-------+
And a simple Stored Procedure
DELIMITER //
CREATE PROCEDURE `NEWLIST`(
IN `pid` int(11),
IN `p_code` varchar(100),
IN `p_name` varchar(100),
IN `dekorA` varchar(100),
IN `p_quantity` varchar(100),
IN `p_listprice` varchar(100),
IN `p_netprice` varchar(100),
IN `p_total` varchar(100),
IN `preorderno` int(11),
IN `yetkili` varchar(10)
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
INSERT INTO siparis
( `product_id`, `p_code`, `p_name`, `p_type`, `p_quantity`, `p_listprice`, `p_netprice`, `p_total`, `preorderno`, `yetkili` )
VALUES
( pid, p_code, p_name, dekorA, p_quantity, p_listprice, p_netprice, p_total, preorderno, yetkili);
END //
DELIMITER ;
Then, in PHP, to build the query dynamically you could do this:
$sp='NEWLIST'; // The name of the Stored Procedure
$sql=sprintf('SELECT group_concat( ":",`parameter_name` ) as `placeholders`
FROM `information_schema`.`parameters`
WHERE `SPECIFIC_NAME`="%s" and `specific_schema`=database()', $sp );
/*
The above would yield a concatenated string such as
:pid,:p_code,:p_name,:dekorA,:p_quantity,:p_listprice,:p_netprice,:p_total,:preorderno,:yetkili
which looks good as the placeholder argument that you would
supply to the stored procedure.
*/
$res=$connect->query( $sql )->fetch( PDO::FETCH_OBJ );
$placeholders=$res->placeholders;
# create the basic sql string statement
$sql=sprintf( 'call `%s`( %s );', $sp, $placeholders );
# we need the keys to generate the payload data used in `execute`
$keys=explode( ',', $placeholders );
# create the PDO prepared statement
$stmt=$connect->prepare( $sql );
# process the POST data using foreach for simplicity
foreach( $_POST as $arr ){
# combine the keys with the values and execute the query
$data=array_combine( $keys, array_values( $arr ) );
$stmt->execute( $data );
}
This successfully populates the db with 3 records.
mysql> select * from siparis;
+------------+---------+---------------+-------------+------------+-------------+------------+---------+------------+---------+
| product_id | p_code | p_name | p_type | p_quantity | p_listprice | p_netprice | p_total | preorderno | yetkili |
+------------+---------+---------------+-------------+------------+-------------+------------+---------+------------+---------+
| 23 | abc-34 | geronimo | blah | 43 | 99 | 120 | 150 | 2 | tiger |
| 65 | def-72 | flatfoot | aarrrggghhh | 643 | 69 | 420 | 150 | 8 | leopard |
| 84 | toto-x1 | desperate dan | zing | 98 | 89 | 92 | 100 | 5000 | lynx |
+------------+---------+---------------+-------------+------------+-------------+------------+---------+------------+---------+
3 rows in set (0.00 sec)
edit
In response to the update you posted there are a couple of issues that need addressing, hopefully covered in the following. The changes made have not been tested in any way by me so, as you know, there might be errors.
try{
/*
create and prepare SQL that analyses the stored procedure
outside of any loop. Use the info obtained therein to build the
dynamic SQL query that calls the stored procedure.
This shuld be done ONCE, outside the loop. Within the loop that
processes the POST data you only need to create the payload for
the SQL and execute the statement.
As you had written it there was a new `$res=$connect->query( $sql )->fetch( PDO::FETCH_OBJ );`
and `$stmt=$connect->prepare( $sql );` within the loop.
*/
$sp='NEWLIST'; // The name of the Stored Procedure
$sql=sprintf('SELECT group_concat( ":",`parameter_name` ) as `placeholders`
FROM `information_schema`.`parameters` WHERE
`SPECIFIC_NAME`="NEWLIST" and
`specific_schema`=mydbname', $sp );
$res=$connect->query( $sql )->fetch( PDO::FETCH_OBJ );
$placeholders=$res->placeholders;
$sql=sprintf( 'call `%s`( %s );', $sp, $placeholders );
$keys=explode( ',', $placeholders );
$stmt=$connect->prepare( $sql );
for( $count = 0; $count < count( $_POST['hidden_p_code'] ); $count++ ) {
$main_arr = array(
':pid' => $_POST['hidden_pid'][$count],
':p_code' => $_POST['hidden_p_code'][$count],
':p_name' => $_POST['hidden_p_name'][$count],
':dekorA' => $_POST['hidden_dekorA'][$count],
':p_quantity' => $_POST['hidden_p_quantity'][$count],
':p_listprice' => $_POST['hidden_p_listprice'][$count],
':p_netprice' => $_POST['hidden_p_netprice'][$count],
':p_total' => $_POST['hidden_p_total'][$count],
':preorderno' => $_POST['hidden_preorderno'][$count],
':yetkili' => $_POST['hidden_yetkili'][$count]
);
/*
create the payload used in the SQL execute method and then commit to db.
*/
$data=array_combine( $keys, array_values( $main_arr ) );
$stmt->execute( $data );
}
} catch( PDOException $e ){
return "Insert failed: " . $e->getMessage();
}
INSERT INTO array with MySQL and PDO
I finally solved my problem. For those interested in the solution, here it is (with a "get the last id bonus"):
try {
if(isset($_POST['add'])){
if(isset($_POST['nature_contact'])) {
$sql = "INSERT INTO db.int_contact (id_g,id_nature_contact) VALUES " .rtrim(str_repeat('('.$lastID.', ?),', count($_POST["nature_contact"])), ',');
$statement = $pdo->prepare($sql);
$count = 1;
foreach($_POST["nature_contact"] as $nature_contact) {
$statement->bindValue($count++, $nature_contact);
}
$statement->execute();
}
}
}
// and then add a catch exceptions
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.
Related Topics
Replace Woocommerce_Add_Order_Item_Meta Hook in Woocommerce 3.4
Mysql_Fetch_Assoc(): Supplied Argument Is Not a Valid MySQL Result Resource in PHP
Find First Character That Is Different Between Two Strings
How to Switch from Post to Get in PHP Curl
When Should I Use Stdclass and When Should I Use an Array in PHP Oo Code
Codeigniter Routes Regex - Using Dashes in Controller/Method Names
How to Get Final Url After Following Http Redirections in Pure PHP
How to Test If a User Has Selected a File to Upload
PHP + SQL Server - How to Set Charset for Connection
PHP Get Index of Last Inserted Item in Array
How to Allow Duplicate Keys in PHP Array
Php: Check If Variable Exist But Also If Has a Value Equal to Something
Can PHP's Glob() Be Made to Find Files in a Case Insensitive Manner
Saving Wav File Recorded in Chrome to Server