need help with queries.plz

[weekyd]

i have a simple Q for the experts..this is driving me nuts..not really good at SQL..pls help..

Here's the table structure..I have a user table where i store user information...

table: order
id user_id orderTime orderType
3 45 2005-08-29 20:38:12 OnLine
4 49 2005-08-29 20:45:12 OnLine

table:order_offline

id user_id orderTime orderType
3 45 2005-08-29 20:30:12 OffLine
4 49 2005-07-29 23:46:12 OffLine

how do i find the latest order for a particular user that matches the last four digits of his phone no..
i have to use the like operator and use something like GREATEST (max(each time stamp)...can someone please help me

[sixdeep]

could u post your php code please? maybe I can help :)

[coopster]

Welcome to WebmasterWorld, weekyd. And you too, sixdeep.

Before we begin with the query statement, let's stop and take a look at the table structure here. First off, we know you have an additional table that stores the user information. So, you have separated the user information from the order information. Good start.

But a closer look at the two tables listed here make us wonder why? Why separate the two? The columns are exactly the same in both tables with the differentiating factor being the orderType -- do you really need two separate tables when you already have a column type that is doing what you need?

[omoutop]

u can always use flags as well if u want two different but identical tables in one

[mcibor]

I would combine those two tables. However with the phone number matching I would make a little mischief: I would store the last four digits of it in separate field, and the rest in another field. Moreover DON'T use name order for anything (table, field, etc)!

Then the query (with order tables combined) would look like:

SELECT orders.id AS id, orders.orderType AS type, users.name AS name WHERE users.four_digits = '$four_digits' AND orders.user_id = users.id ORDER BY orders.orderTime DESC LIMIT 1

Best regards
Michal Cibor

PS. The tables:
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
id int(11) NOT NULL auto_increment,
name varchar(30),
four_digits char(4) NOT NULL default '0000',
rest_tel varchar(13),
PRIMARY KEY (id));

DROP TABLE IF EXISTS `orders`;
CREATE TABLE `orders` (
id int(11) NOT NULL auto_increment,
user_id int(11) NOT NULL default '0',
orderTime datetime,
orderType varchar(11),
PRIMARY KEY (id));

I'm not sure about the four_digits and about the orderTime if that's the best solution for those fields.