Database tables for customer and order information

[hurray]

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.

 

 

 

[Haumea]

What are you missing? Did you manage to write a script? 

[hurray]

I was planning to run separate sql on each table and then put them in Access.

The thing is I can't figure out which field name corresponds to the information I am looking for.

 

[hurray]

Hi,

No one knows answer to this?

 

[Haumea]

It does not seem to me that you are missing something. I sent you a pm. 

 

[hurray]

Hi Haumea,

What would your SQL be like?

I am not sure if I am looking at the right tables for the information I need. If you think I am looking at the right place, then am I looking at the right fields?

[Haumea]

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. 

[hurray]

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