0

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

  • how many times your INSERT query gets executed? – Your Common Sense Apr 08 '22 at 11:03
  • Why are you binding by reference? – Dharman Apr 08 '22 at 11:04
  • Why are you catching and rethrowing an exception? – Dharman Apr 08 '22 at 11:05
  • "how many times your INSERT query gets executed?" - Depends on the number of items in the cart – Денис Корнецов Apr 08 '22 at 11:10
  • Try to look closer. I cannot count that many – Your Common Sense Apr 08 '22 at 11:11
  • "Try to look closer. I cannot count that many" - one entry. the last element of the array – Денис Корнецов Apr 08 '22 at 11:26
  • Also, which one lastInsertId you are going to get? From these 3 inserts? – Your Common Sense Apr 08 '22 at 11:33
  • So why you 're wondering it inserts only one row if you're executing your insert query only once? – Your Common Sense Apr 08 '22 at 11:38
  • $lastInsertId - it is the identifier of the previous request. which writes the order data to another table. It is used to link products to an order. – Денис Корнецов Apr 08 '22 at 11:40
  • I know. But you want here **three** identifiers, not one. How you're going to get all three? – Your Common Sense Apr 08 '22 at 11:41
  • [Here is how your methods should really look like](https://phpize.online/sql/mysql57/undefined/php/php8/cb459138e4e26ad8b23d74d432b855a5/), without all that cargo cult code of yours. – Your Common Sense Apr 08 '22 at 11:44
  • I receive it using other request for other table. I get it once. I write it to a variable and pass it to another method that uses it. I asked it permanently, to eliminate the problem in this place. But nothing has changed – Денис Корнецов Apr 08 '22 at 11:47
  • Денис, вот почему бы не спросить на https://ru.stackoverflow.com/, особенно если такие проблемы с английским? Там конечно нормальных спецов нет, но хотя бы заметить что $resultQuery->execute() вызывается только один раз, там смогли бы. В любом случае для вставки нескольких строк нужен отдельный метод, который выполняет запросы в цикле. Как показано у меня по ссылке. – Your Common Sense Apr 08 '22 at 11:59
  • В том то и дело, что хочу избежать запросов в цикле. Судя по документации, bindParam можно цикличном использовать, передавая ему ссылку на переменную. https://localcoder.org/php-pdo-bindparam-was-falling-in-a-foreach – Денис Корнецов Apr 08 '22 at 12:08
  • bindParam можно циклично использовать. но запрос у тебя все равно ОДИН. На вставку ОДНОЙ строки. хоть обциклись, но больше одной он не вставит. Сделать одним запросом можно, но тогда тем более без этой кривой функции query. И запрос будет совсем другой. Пример есть по ссылке, которую я прикрепил сверху вопроса. В любом случае я бы порекомендовал сначала сделать в цикле, а потом уже писать запросы с подвыподвертом. – Your Common Sense Apr 08 '22 at 12:16

0 Answers0