MySql Query - First Order Date per customer

[riteshuae]

Hi,

i have installed prestashop 1.4.8.2 & i need a report of first order date for each customers, please help me to create mysql query. i need below columns information from database.

id_customer, firstname, lastname, first order date

thanks,

Ritesh

[cereal]

Hi, here you can see the database schema:

• http://doc.prestashop.com/display/PS14/Fundamentals

And you can also download the MySQL Workbench file. Maybe you can try something like this:

SELECT customer.id_customer, customer.firstname, customer.lastname, orders.id_order, order_history.date_add FROM customer, orders, order_history WHERE customer.id_customer = ? AND customer.id_customer = orders.id_customer AND orders.id_order = order_history.id_order ORDER BY order_history.date_add ASC LIMIT 1;

This should return the first order made by the customer, to get the last one use desc, instead of asc, in the order by clause.

Note: the question mark in the query should be replaced by the customer id number.

[riteshuae]

thank you for your reply, i want it for all my 2000 customers.

i don't want to mention customer id each time, i want to generate for all customers.

thanks in advance.

[cereal]

Ok, then remove customer.id_customer = ? and the limit clause, and try to use GROUP BY:

SELECT customer.id_customer, customer.firstname, customer.lastname, orders.id_order, order_history.date_add FROM customer, orders, order_history WHERE customer.id_customer = orders.id_customer AND orders.id_order = order_history.id_order GROUP BY order_history.id_customer ORDER BY order_history.date_add ASC;

It should return the correct rows.

[riteshuae]

thank you very much.

[riteshuae]

In order_history table there is no id_customer column

mysql query giving error
Unknown column 'order_history.id_customer' in 'group statement'

[cereal]

Sorry for the mistake, in my previous example I was referring to orders.id_customer, but nevermind: I exported the schema from the workbench file and I saw a date_add column in the orders table, so try this query:

SELECT customer.id_customer, customer.firstname, customer.lastname, orders.id_order, orders.date_add FROM customer, orders WHERE customer.id_customer = orders.id_customer GROUP BY orders.id_customer ORDER BY orders.date_add ASC;

Live example: http://sqlfiddle.com/#!2/17133/1

[riteshuae]

Its working, thank you very much for your help.

[Florea]

Hy there!
I have prestashop 1.4.4.1 and i want to export database clients.
I tried the two codes from above but none of them work.

For the first it give me this:

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 '? AND customer.id_customer = orders.id_customer AND orders.id_order = order_hist' at line 1

And the second one:

1146 - Table 'rape0123_apetit.customer' doesn't exist

Can you please help me out?
Thx

[cereal]

@Florea

Hi, open your own thread with as much details as possible.

[Florea]

Ok, thank you!

[daniellemacey]

SELECT customer.id_customer, customer.firstname, customer.lastname, orders.id_order, order_history.date_add FROM customer, orders, order_history WHERE customer.id_customer = ? AND customer.id_customer = orders.id_customer AND orders.id_order = order_history.id_order ORDER BY order_history.date_add ASC LIMIT 1;

[Ritesh_5]

how to get last order id & date of each customers?