1

I'm trying to copy products from a cart (from cart_product) to the order_product table

Can this query work this way by preparing the the column name to become a value of the order_id

    // Create the order

    $order_id = $this->order->store();

    // Copy products from cart and assign them to the order

    $req = "INSERT INTO order_product (order_id, product_id)
    SELECT :order_id, product_id, 
    FROM cart_product, cart
    WHERE cart.user_id = :id";

    $bind = array(
        "id" => $_SESSION['id'],
        "order_id" => $order_id
    );

    return  $this->Sql($req);

I'm using a micro framework, where the Sql function came.

I want the query to become something like this

INSERT INTO order_product (order_id, product_id)
SELECT 3, product_id, 
FROM cart_product, cart
WHERE cart.user_id = 2
  • Are you trying to sell all your stock at once? If not, better join the cart_product to the cart. – Gerard H. Pille Jun 02 '18 at 16:00
  • No I'm only trying to copy the products from the cart that belong to a specific customer and create an order with them and then clean the cart from all products –  Jun 02 '18 at 16:07
  • You're copying all records from cart_product, not only those from the customer's cart. – Gerard H. Pille Jun 02 '18 at 16:09
  • Well cart_product is the customer's cart. Each customer has one cart that gets renewed after each order validation. I specified that I'm only copying those from the specific cart owner using the WHERE clause. Am I wrong ? –  Jun 02 '18 at 16:13
  • You cannot bind columns. You do know that right? – Rotimi Jun 02 '18 at 16:17
  • I thought since we can do mathematical operations with the SELECT statement like (SELECT 7 + 5) maybe we could do the same with prepared queries. –  Jun 02 '18 at 16:19
  • So how can I copy a portion of data from a table and prepare the other half? –  Jun 02 '18 at 16:20
  • @GerardH.Pille You should learn something. Have a look https://stackoverflow.com/questions/182287/can-php-pdo-statements-accept-the-table-or-column-name-as-parameter – Rotimi Jun 03 '18 at 07:44
  • @AkintundeOlawale and there I was hoping to learn something, but it was only that you seem to ignore the difference between a column and a column name. Again: try "select :bindvar from ...". – Gerard H. Pille Jun 03 '18 at 12:21

1 Answers1

0

Solution : The problem was caused by the comma at the end of the FROM line. That is what was causing the problem and not the inability to bind columns as values. I had no error messages because I'm using a micro proprietary framework.