php - How to query the order result in this table structure in MYSQL/ SQL? -

php - How to query the order result in this table structure in MYSQL/ SQL? -

i work on e-shopping cart project , need record tranasaction

the construction this:

order ------ id order_id * member_id fellow member ------ id member_id status -------- id description * order_id create_date

so, status store order alter status, e.g. when order received , add together order , , add together status description 100 <== define , hardcode . , if order deliver, insert 1 more status 200 <== define.

then , problem , how query result this?

(desc) create_date order_id member-id status_description e.g. 2014 06 13 05:00:00 #11111 #12345 order_receive 2014 06 13 03:00:00 #15555 #12222 order_deliver

notice e.g. store status time whenever order update. there should several status of order, , result should contain latest status only , need max (create_date). how construction query in scenario?

i have thought not in 1 query, wonder there way create in 1 query? thanks

update

my effort query this

select order_id status grouping order_id order create_date in php , each order_id, select * order order_id = id , bring together fellow member table

try this:

select src.max_date, src.order_id, src.member_id, case when s1.description = 100 'order_receive' when s1.description = 200 'order_deliver' end status_description (select max(s.create_date) max_date, s.order_id , o.member_id status s bring together `order` o on o.order_id = s.order_id grouping s.order_id, o.member_id) src bring together status s1 on s1.create_date = src.max_date;

demo

php mysql sql