Getting error in query using MySQL and PHP

Question

I am getting the following error inside my query using MySQL.

Error:#1064 - 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 'pr.pro_Id=p.pro_Id JOIN db_supplier AS s pr.supplier_id=s.supplier_id JOIN db_or' at line 1

I am explaining my query below.

 SELECT
 pr.pro_data_id,pr.pro_Id,pr.specification,pr.Discount,pr.Offer,pr.unit_cost_price,pr.unit_sale_price,pr.quantity
 AS
 total_quantity,pr.shipping_charge,pr.product_code,pr.policy,pr.supplier_id,pr.latest_sale_price,pr.pro_status,pr.sale_price,p.Product_name,s.user_name,op.order_id,op.quantity
 AS
 ordered_quantity,op.final_price,op.prod_tot_price,op.delstatus,op.status,o.order_date,o.shipping_id,sh.name
 FROM db_product_data AS pr JOIN db_product_info AS p
 pr.pro_Id=p.pro_Id JOIN db_supplier AS s pr.supplier_id=s.supplier_id
 JOIN db_order_products AS op pr.pro_data_id=op.pro_data_id JOIN
 db_order AS o op.order_id=o.order_id JOIN  db_shipping_address AS sh
 o.shipping_id=sh.shipping_id WHERE pr.pro_data_id='63' AND
 pr.pro_Id='36' ORDER BY pr.pro_data_id DESC

How can I resolve this error?

Ravi Hirani · Accepted Answer · 2016-02-05 06:31:17Z

You have missed ON many times in your query:-

SELECT
pr.pro_data_id,pr.pro_Id,pr.specification,pr.Discount,pr.Offer,pr.unit_cost_price,pr.unit_sale_price,pr.quantity
AS total_quantity,pr.shipping_charge,pr.product_code,pr.policy,pr.supplier_id,pr.latest_sale_price,pr.pro_status,pr.sale_price,p.Product_name,s.user_name,op.order_id,op.quantity
AS ordered_quantity,op.final_price,op.prod_tot_price,op.delstatus,op.status,o.order_date,o.shipping_id,sh.name
FROM db_product_data AS pr 
JOIN db_product_info AS p ON pr.pro_Id=p.pro_Id 
JOIN db_supplier AS s ON pr.supplier_id=s.supplier_id
JOIN db_order_products AS op ON pr.pro_data_id=op.pro_data_id 
JOIN db_order AS o ON op.order_id=o.order_id 
JOIN  db_shipping_address AS sh ON o.shipping_id=sh.shipping_id 
WHERE pr.pro_data_id='63' AND
pr.pro_Id='36' ORDER BY pr.pro_data_id DESC

Hope it will help you :)

Sougata Bose · Accepted Answer · 2016-02-05 06:21:10Z

2

Missing ON -

JOIN db_product_info AS p ON pr.pro_Id=p.pro_Id

answered Feb 5, 2016 at 6:21

Sougata Bose

31.8k8 gold badges52 silver badges89 bronze badges

1 Comment

devpro Over a year ago

In early days in my career I did this database contain 8 Millions records forgot ON and query multiply 8 million * 8 million what happened all servers was close site down for 15 minutes ... :)

Vadivel S · Accepted Answer · 2016-02-05 06:26:20Z

try this query Join table_name ON

SELECT pr.pro_data_id,pr.pro_Id,pr.specification,pr.Discount, pr.Offer,pr.unit_cost_price,pr.unit_sale_price,pr.quantity AS total_quantity,
pr.shipping_charge,pr.product_code,pr.policy,pr.supplier_id,pr.latest_sale_price,
pr.pro_status,pr.sale_price,p.Product_name,s.user_name,op.order_id,op.quantity 
AS ordered_quantity,op.final_price,op.prod_tot_price,op.delstatus,op.status,o.order_date,
o.shipping_id,sh.name FROM db_product_data AS pr
JOIN db_product_info AS p on pr.pro_Id=p.pro_Id JOIN db_supplier AS s on 
 pr.supplier_id=s.supplier_id 
JOIN db_order_products AS op on pr.pro_data_id=op.pro_data_id JOIN db_order AS 
o on op.order_id=o.order_id JOIN  db_shipping_address AS sh on o.shipping_id=sh.shipping_id WHERE  
pr.pro_data_id='63' AND pr.pro_Id='36'  ORDER BY pr.pro_data_id DESC

Jenz · Accepted Answer · 2016-02-05 06:22:24Z

ON operator is missing in all JOINS of your query

SELECT pr.pro_data_id,pr.pro_Id,pr.specification,pr.Discount,pr.Offer,pr.unit_cost_price,pr.unit_sale_price,pr.quantity AS total_quantity,pr.shipping_charge,pr.product_code,pr.policy,pr.supplier_id,pr.latest_sale_price,pr.pro_status,pr.sale_price,p.Product_name,s.user_name,op.order_id,op.quantity AS ordered_quantity,op.final_price,op.prod_tot_price,op.delstatus,op.status,o.order_date,o.shipping_id,sh.name 
FROM db_product_data AS pr JOIN db_product_info AS p ON pr.pro_Id=p.pro_Id 
JOIN db_supplier AS s ON pr.supplier_id=s.supplier_id 
JOIN db_order_products AS op ON pr.pro_data_id=op.pro_data_id 
JOIN db_order AS o ON op.order_id=o.order_id 
JOIN  db_shipping_address AS sh ON o.shipping_id=sh.shipping_id 
WHERE pr.pro_data_id='63' AND pr.pro_Id='36' ORDER BY pr.pro_data_id DESC

Collectives™ on Stack Overflow

Getting error in query using MySQL and PHP

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related