Order By in 2 tables MYSQL

Question

Order By in 2 tables MYSQL

Navigation

#1 by (0 votes)
#2 by (0 votes)

0

Good morning! I'm having a hard time and I'd like your help with this problem!

Now, I'm doing a select in MYSQL where multiple tables are selected, in opencart to generate an XML for facebook ads.

My XML is like this

SELECT DISTINCT t1.product_id as product_idT1, t1.manufacturer_id as manufaturer, t1.price as price, t1.date_added as dataAdd, t1.quantity as quantity, t2.name as nameT2, t2.description as descriptionT2, t3.name as nameT3, t4.name as nameT4, t5.image, t6.name as nameT6, t7.query as urlT7, t7.keyword as keyword 
FROM oc_product t1 
INNER JOIN oc_product_description t2 ON (t1.product_id = t2.product_id) 
INNER JOIN oc_manufacturer t3 ON (t1.manufacturer_id = t3.manufacturer_id) 
INNER JOIN oc_option_value_description t4 
INNER JOIN oc_product_image t5 ON (t1.product_id = t5.product_id) 
INNER JOIN oc_stock_status t6 ON (t1.stock_status_id = t6.stock_status_id) 
INNER JOIN oc_url_alias t7 
WHERE t7.query like CONCAT('%', t1.product_id , '%') 
GROUP BY product_idT1 ASC

In this case, the image table oc_product_image must be in DECREASE order because I want the last image.

I'm using PHP.

Thank you!

php mysql sql

asked by anonymous 03.11.2017 / 12:29

2 answers

0

I did this:

SELECT DISTINCT t1.product_id as product_idT1, t1.manufacturer_id as manufaturer, t1.price as price, t1.date_added as dataAdd, t1.quantity as quantity, t2.name as nameT2, t2.description as descriptionT2, t3.name as nameT3, t4.name as nameT4, t5.image as imagemT5, t6.name as nameT6, t7.query as urlT7, t7.keyword as keyword, t8.product_id, t8.price as produtoDesconto 
FROM oc_product t1 
LEFT JOIN oc_product_description t2 ON (t1.product_id = t2.product_id) 
LEFT JOIN oc_manufacturer t3 ON (t1.manufacturer_id = t3.manufacturer_id) 
LEFT JOIN oc_option_value_description t4 
LEFT JOIN oc_product_image t5 ON (t1.product_id = t5.product_id) 
LEFT JOIN oc_stock_status t6 ON (t1.stock_status_id = t6.stock_status_id) 
LEFT JOIN oc_product_special t8 ON (t1.product_id = t8.product_id)
LEFT JOIN oc_url_alias t7 
ORDER BY t5.product_id DESC

I get the following 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 'ORDER BY t5.product_id DESC LIMIT 0, 25 'at line 10

Can you help me with what I'm doing wrong? Thanks

04.11.2017 / 16:11

Send information to rest and trigger notification Array of struct accepting more than defined, what's wrong?

score 0 · Accepted Answer

Make ORDER BY t5.product_id DESC , so it will sort by order of the product. The fact that you are not listing all products should be related to INNER JOINS or even WHERE . Change the Inners by LEFT JOIN (test only) and remove Where . This way you will find out what is stopping you from fetching all the products.