Need sql query for prestashop to generate csv file


I need a sql query that generate a csv from a prestashop database, value required values in the csv are the following:

id_order -> order number

id_order_invoice -> invoice number

total_products -> total paid of the order vat excluded and without shipping

total_shipping_tax_excl -> shipping paid vat excluded

state -> order state

date -> order purchase date

prod_ref -> prod reference

uds -> number of units

product_price -> unit product price vat excluded

Name -> customer first name + last name

ai_country -> invoice address prestashop country number

ai_address1 -> invoice address 1 field

ai_address2 -> invoice address 2 field

ai_postcode -> invoice post code field

ai_city-> invoice city field

ai_other-> invoice other field

ai_phone-> invoice phone field

ai_phone_mobile-> invoice mobile phone field

email -> invoice mobile phone field

I have a query that is almost working except that invoice number that should be extracted from ps_orders table and here is not the correct one (don't know why)

So you can fix it or create a new one

SELECT d.id_order as order_number,

id_order_invoice as invoice_number,

ROUND(total_products, 2) as total_product_tax_excl,

ROUND(total_shipping_tax_excl, 2) total_shipping_tax_excl,

[login to view URL] AS state,

o.date_upd AS date,

d.product_reference AS prod_ref,

d.product_quantity AS uds,

ROUND(d.product_price, 2) product_price,

CONCAT_WS( ' ', [login to view URL], [login to view URL] ) AS Name,

ad.id_country AS ad_country, [login to view URL] AS ad_address1, [login to view URL] AS ad_address2, [login to view URL] AS ad_postcode, [login to view URL] AS ad_city, [login to view URL] AS ad_other, [login to view URL] AS ad_phone, ad.phone_mobile AS ad_phone_mobile,

ai.id_country AS ai_country, [login to view URL] AS ai_address1, [login to view URL] AS ai_address2, [login to view URL] AS ai_postcode, [login to view URL] AS ai_city, [login to view URL] AS ai_other, [login to view URL] AS ai_phone, ai.phone_mobile AS ai_phone_mobile,

[login to view URL] FROM ps_order_detail d

LEFT JOIN ps_orders o ON ( d.id_order = o.id_order )

LEFT JOIN ps_customer g ON ( o.id_customer = g.id_customer )

LEFT JOIN ps_stock_available s ON (d.product_id = s.id_product)

LEFT JOIN ps_address ad ON (o.id_address_delivery = ad.id_address)

LEFT JOIN ps_address ai ON (o.id_address_invoice = ai.id_address)

LEFT JOIN ps_group_lang gl ON ( g.id_default_group = gl.id_group )

LEFT JOIN ps_order_state_lang os ON ( o.current_state = os.id_order_state )

WHERE 1 GROUP BY d.id_order, d.product_name ORDER BY d.id_order DESC LIMIT 40

Evner: MySQL, PHP, Prestashop, SQL

Se mere: sql express 2005 import csv file, generate csv file rss feed, can articulate generate csv file, prestashop export orders, prestashop 1.7 export products, prestashop export products with images, prestashop export products module free, export combinations prestashop, update sql database aspnet using csv file, query mysql export csv file, mysql query display results csv file, prestashop sample csv file, javascript generate csv file, generate csv file php, sql server dts import csv file, sql 2008 express import csv file, generate csv file dts 2005, script run mysql query write results csv file, html generate csv file javascript, api generate csv file

Om arbejdsgiveren:
( 8 bedømmelser ) Acigne, France

Projekt ID: #18146131

Tildelt til:


hi, i would like to rewrite the attached query to get the correct invoice number from the ps_orders table.

€13 EUR in 0 dage
(92 bedømmelser)

5 freelancere byder i gennemsnit €16 på dette job


Hello I will do it for you sir. Thanks

€23 EUR på 1 dag
(120 bedømmelser)

Hi How do you do! As I am an experienced web developer, I have much experience with php, mysql and database management. I am sure I can handle your project successfully in short time. I am available to start immedi Flere

€14 EUR på 1 dag
(9 bedømmelser)

Hi I have read your requirements carefully. I am highly skilled and rich experienced developer. I would like to confirm project details through message. Thanks for reading my proposal.

€15 EUR på 1 dag
(5 bedømmelser)

i am database expert professional database developer and designer will solve your problem quickly i will not disappoint you the task is not lengthy but tricky it is solvable task and require some focus that is known as Flere

€14 EUR in 0 dage
(0 bedømmelser)