PDO bindParam() Multiple insert foreach

32 Views Asked by At

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

0

There are 0 best solutions below