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