How to properly concat/escape this sql query in php

Question

I am trying to insert multiple rows in mysql database using php. A portion of the code is as below.

        $b_address = $_POST["b_address"];
        $s_address = $_POST["s_address"];

        $query = "INSERT INTO order VALUES";
        foreach ($_SESSION['buy'] as $products) {

            $username = $_COOKIE["uname"];
            $Product_Name = $products["Product_Name"];
            $qty = $products["qty"];
            $price = $products['qty'] * $products['Price'] ;
            $query .= "('', 

                      (select id from user_detail where user_name =  $username    ) ,
                      (select Product_id from products where Product_Name = $Product_Name  ) ,
                      $qty,
                      $price ,
                      $b_address ,
                      $s_address ,
                      NOW()

                ),";
        }

        rtrim($query, ',');

But i am getting some syntex error where selecting id. How to get rid of the syntex error and run the code properly?

error i am getting is as below :

errorYou have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'order VALUES('', (select id from user_detail where user_name = ar' at line 1

EDIT

I changed the line into $query = "INSERT INTOordersVALUES"; and now the error i am getting is :

errorYou have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 21

EDIT 2

Here is the whole code of the page, incase i am blindly mistaking somewhere.

<?php

session_start();

$con=mysql_connect('localhost','root','mypass');
    if(!$con)
    {
        die ('connection error'.mysql_error());
    }
    mysql_select_db('test1',$con);


if (isset($_POST['submit'])) {
    if (!empty($_POST['b_address'])  && !empty($_POST['s_address'])  ) {

        $b_address = $_POST["b_address"];
        $s_address = $_POST["s_address"];

        $query = "INSERT INTO `orders` VALUES ";
        foreach ($_SESSION['buy'] as $products) {

            $username = $_COOKIE["uname"];
            $Product_Name = $products["Product_Name"];
            $qty = $products["qty"];
            $price = $products['qty'] * $products['Price'] ;
            $query .= "('', 

                      (select id from user_detail where user_name =  '$username'    ) ,
                      (select Product_id from products where Product_Name = '$Product_Name'  ) ,
                      '$qty',
                      '$price' ,
                      '$b_address' ,
                      '$s_address' ,
                      NOW()

                ),";
        }

        rtrim($query, ',');

        if(!mysql_query($query,$con))
        {
            die ("error".mysql_error());
        }
        else
        {
            echo "Thank you for your purchase. Your order is under processing.";
            unset($_SESSION['buy']);

        } 


    }else{
        echo 'All fields are required.';
    }
}

Use prepared statements with parameterization for your variables. — Gumbo, Nov 20 '14 at 10:24

Pupil · Answer 1 · 2014-11-20T11:07:03.273

0

Try this:

Please observe order

$query = "INSERT INTO `order` VALUES ";
        foreach ($_SESSION['buy'] as $products) {

            $username = $_COOKIE["uname"];
            $Product_Name = $products["Product_Name"];
            $qty = $products["qty"];
            $price = $products['qty'] * $products['Price'] ;

$query .= "('', 
  (select id from user_detail where user_name =  '$username') ,
  (select Product_id from products where Product_Name = '$Product_Name') ,
    '$qty',
    '$price' ,
    '$b_address' ,
    '$s_address' ,
    NOW()
    ),";

Explanation: Order is MySQL reserved word.

You can not use it in your SQL for any Table name or field name.

edited Nov 20 '14 at 11:07

answered Nov 20 '14 at 10:23

Pupil

23,834
6
44
66

Still getting the same error – user1906399 Nov 20 '14 at 10:31
Answer updated, please check. – Pupil Nov 20 '14 at 10:34
Now the error i am getting is `errorYou have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 21` – user1906399 Nov 20 '14 at 10:50
post printed the SQL here – Pupil Nov 20 '14 at 10:52
Please check the Edit at the bottom of my question – user1906399 Nov 20 '14 at 11:05
Answer updated. Added a space after INSERT INTO order VALUES – Pupil Nov 20 '14 at 11:07
Please check the second edit of the question at the bottom. Using the whole code and still the error i am getting is `errorYou have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 21` That line is where we are writing insert into – user1906399 Nov 20 '14 at 11:27
I tried changing the name of the table orders to product_orders and still getting the same syntax error – user1906399 Nov 20 '14 at 11:38
As I said you already, what the is the exact query you are sending to MySQL server. Without it I can not help you. Hope you understand. – Pupil Nov 20 '14 at 11:49

How to properly concat/escape this sql query in php

EDIT

EDIT 2

1 Answers1