Jump to content

Database tables for customer and order information


hurray

Recommended Posts

Hi,

I am using version 1.5.6.2

I am trying to retrieve name, email and order number of customers who have placed an order that has been shipped already, for a specific period of time to send them a message asking for their review.

I have looked in the database but cannot find the exact tables that contain these information.

This is what I have found:

#ps_customer for firstname, lastname, email, id_customer

#ps_orders for id_customer, id_order

#ps_order_history for id_order, date_add, id_order_state

But I fee like I am missing something or I am not looking at the right tables.

Any help would be greatly appreciated.

Thanks.

 

 

 

Link to comment
Share on other sites

Something like this. 

SELECT c.firstname, c.email FROM ps_customer c WHERE c.id_customer IN (SELECT o.id_customer FROM ps_orders o WHERE o.id_order IN (SELECT os.id_order FROM os_orde_state os WHERE os.id_order_state = "the state you need" AND os.date_add > "the date you want")) 

Then you will get an array of result and for each result you send an email. 

Link to comment
Share on other sites

  • 2 weeks later...

Hi Haumea,

Your SQL is incorrect.

(SELECT os.id_order FROM os_orde_state os WHERE os.id_order_state = "the state you need" AND os.date_add > "the date you want")) 

 

In ps_order_state table there is no_id_order and os.date_add field. There is only id_order_state. For me  id_order_state is "4" for an ordered delivered.

And since there is no id_order in ps_order_state table, I cannot join it with ps_orders table.

I am using version 1.5.6.2

 

 

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...