Statement Comparing dates then display certain records.

[vmicchia]

So what I want to do is select the customer info  from an order table where their total is less than 30 but more than 0 and then see if they do or do not have another order placed at a later date from the results of the first search and then display the people who have not made an order at that later date. I really have no idea where to start or if this is even possible. If anyone could link me to some tutorial or help me out it would be very much appreciated.

[fenway]

Everything is possible -- but we'll need far more information.

[vmicchia]

Ok Then I will try to expand. I Have a table with information for orders(firstname, lastname, order_id, total, notes, timestamp(unix), phone, email, status). Now what I want to do is have a sql statement(s) that first check for orders that total $30 or less. Once that is done I want to check if there has been an order places since the order of less than thirty dollars. then display people who have not made an order since that order of $30.

 

I know how to check for the order total but once I need to check if more orders were placed on multiple records and then compare the dates I get lost.

 

Any help is much appreciated.

[vmicchia]

I guess where my problem comes in is that after

 

$sql = "SELECT * FROM orders WHERE total < 30";
        $result = mysql_query($sql);

 

A: Do i need to fetch the array or can I somehow use that just as the result?

 

B: How to I then Find if these customers(comparing by email) have a more recent order? And then display the customers that do not?

 

I'm unsure on how to use the arrays that are returned to accomplish this. I think it would have to be a while loop but not sure how to execute. Any help is much appreciated.

[DavidAM]

I think this query should give you the data you are looking for:

 

SELECT * FROM orders AS MainWHERE Main.total < 30AND NOT EXISTS(SELECT * FROM orders AS Later   WHERE Later.email = Main.email   AND Later.timestamp > Main.timestamp)

 

[vmicchia]

Thank you so much that worked perfectly. I really appreciate it.