I am having trouble using PDO bind param when using foreach. There is an array in which the key is a named parameter ':param' = array(data=>$param, type=> PDO::PARAM_INT)
[0] => Array
(
[order_id] => Array
(
[data] => 141,
[type] => PDO::PARAM_INT
)
[product_id] => Array
(
[data] => 65,
[type] => PDO::PARAM_INT
)
[quantity] => Array
(
[data] => 1,
[type] => PDO::PARAM_INT
)
)
[1] => Array
(
[order_id] => Array
(
[data] => 141,
[type] => PDO::PARAM_INT
)
[product_id] => Array
(
[data] => 66,
[type] => PDO::PARAM_INT
)
[quantity] => Array
(
[data] => 1,
[type] => PDO::PARAM_INT
)
)
Method that generates a query to the database:
class Order
{
private static function insertOrderProduct($productData, $orderId)
{
if (!empty($productData) || !empty($orderId)) {
$db = Registry::get('db');
$query = 'INSERT INTO orders_product ( '
. 'order_id, product_id, quantity'
. ') VALUES ( '
. ':order_id, '
. ':product_id, '
. ':quantity '
. ')';
foreach ($productData as $key => $product) {
$queryParam[] = array(
'order_id' => array(
'data' => $orderId,
'type' => PDO::PARAM_INT
),
'product_id' => array(
'data' => $product['product_id'],
'type' => PDO::PARAM_INT
),
'quantity' => array(
'data' => $product['quantity'],
'type' => PDO::PARAM_INT
)
);
}
//p::a($queryParam);
$db->query($query, $queryParam);
}
}
public static function insert($orderData)
{
if ($orderData) {
$db = Registry::get('db');
$query = 'INSERT INTO orders ( '
. 'user_id, name, payment_method, shipping_method, summ, status_id '
. ') VALUES ( '
. ':user_id, '
. ':name, '
. ':payment_method, '
. ':shipping_method, '
. ':summ, '
. ':status_id '
. ')';
$queryParam[] = array(
'user_id' => array(
'data' => (!empty($orderData['user_id'])) ? $orderData['user_id'] : 0,
'type' => PDO::PARAM_INT
),
'name' => array(
'data' => $orderData['name'],
'type' => PDO::PARAM_STR
),
'payment_method' => array(
'data' => $orderData['payment_method'],
'type' => PDO::PARAM_STR
),
'shipping_method' => array(
'data' => $orderData['shipping_method'],
'type' => PDO::PARAM_STR
),
'summ' => array(
'data' => $orderData['summ'],
'type' => PDO::PARAM_INT
),
'status_id' => array(
'data' => 1,
'type' => PDO::PARAM_INT
)
);
$lastId = $db->query($query, $queryParam)->last_id;
if (!empty($lastId)) {
self::insertOrderProduct($orderData['products'], $lastId);
}
}
}
}
Class method that inserts a record into the database:
public function query($query = '', $queryParam = array())
{
if ($query) {
try {
$resultQuery = $this->db->prepare($query);
if ($queryParam) {
foreach ($queryParam as $key => &$valueParam) {
foreach ($valueParam as $param => &$dataParam) {
var_dump($param);
$resultQuery->bindParam(":$param", $dataParam['data'], $dataParam['type']);
}
}
}
$resultQuery->execute();
$last_id = $this->db->lastInsertId();
$data = $resultQuery->fetchAll(PDO::FETCH_ASSOC);
$result = new stdClass();
$result->row = isset($data[0]) ? $data[0] : [];
$result->rows = $data;
$result->num_rows = count($data);
$result->last_id = $last_id;
return $result;
} catch (PDOException $e) {
throw new Exception('Error: ' . $e->getMessage() . ' Error Code : ' . $e->getCode() . ' <br />');
}
}
}
Only the last element of the array is written to the database.
Method bindValue :
if ($queryParam) {
foreach ($queryParam as $key => $valueParam) {
foreach ($valueParam as $param => $dataParam) {
$resultQuery->bindValue(":$param", $dataParam['data'], $dataParam['type']);
}
}
}
also writes only one value from the array. I read a lot of topics, I did not find the necessary information.
Sorry for my english