CODEWITHSUNDEEP
phpmysqlsql
user3149107
user3149107

Reputation: 33

You have an error in your SQL syntax; check the manual

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE j.id_customer = 1' at line 4

SELECT j.`id_customer`, j.`id_order`, m.`id_shop`
        FROM `ps_orders` j     
        LEFT JOIN `ps_order_detail` m   
        WHERE j.`id_customer` = 1

this is generated from original code in prestashop php on if detailed error correction is turned on -------

 foreach ($result as $key)
        {
                        $customer_id_is = 1;
             $product_id_is = 5;
                    $result2 = Db::getInstance()->executeS(
        'SELECT j.`id_customer`, j.`id_order`, m.`id_shop`
        FROM `'._DB_PREFIX_.'orders` j     
        LEFT JOIN `'._DB_PREFIX_.'order_detail` m   
        WHERE j.`id_customer` = '.$customer_id_is.'


          ');  

        }

Upvotes: 3

Views: 2502

Answers (2)

GarethD
GarethD

Reputation: 69759

You are missing the ON clause in your JOIN

SELECT j.id_customer, j.id_order, m.id_shop FROM ps_orders j
LEFT JOIN ps_order_detail m
    ON m.SomeField = j.SomeField <-- HERE 
WHERE j.id_customer = 1

If you are joining your tables you need to link them on a field. Otherwise you are performing a cross join.

The MySQL Docs state:

In MySQL, JOIN, CROSS JOIN, and INNER JOIN are syntactic equivalents (they can replace each other). In standard SQL, they are not equivalent. INNER JOIN is used with an ON clause, CROSS JOIN is used otherwise.

So this would work:

SELECT j.id_customer, j.id_order, m.id_shop FROM ps_orders j
JOIN ps_order_detail m
WHERE j.id_customer = 1

as would this:

SELECT j.id_customer, j.id_order, m.id_shop FROM ps_orders j
INNER JOIN ps_order_detail m
WHERE j.id_customer = 1

But it does not work for OUTER JOINs.

Upvotes: 9

juergen d
juergen d

Reputation: 204746

When you link two tables you have to define a column on each table that connection them.

You do that in the ON condition of a JOIN which you did not specify. Example:

SELECT *
FROM table1 
LEFT JOIN table2 ON table1.pk_id = table2.fk_id

Upvotes: 3

Related Questions