Query Order

[Canman2005]

Hi all

 

I have a database with products in it and in each row is a field called "dateadded", in that field I store dates when products were added, such as '2008-08-31'.

 

Is there a way to run a QUERY which returns all rows in the database but places at the top, the rows which were added 15 days ago with newest added at top and then the rest of the rows that were added more than 15 days ago ordered by `product_title`

 

Is there a way to QUERY that?

[revraz]

Look up INTERVAL for MySQL

[Canman2005]

Been playing with that but cannot seem to get it to work

 

the query I use which works is just

 

SELECT * FROM `myproducts"

 

anyone?

[solon]

can you give an example of the database table and an example of the result you want?

[Canman2005]

Sure

 

My `products` table looks like

 

CREATE TABLE `products` (
  `id` int(10) NOT NULL,
  `title` varchar(255) NOT NULL,
  `position` int(10) NOT NULL,
  `date` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `products` (`id`, `title`, `position`, `date`) VALUES
(1, 'HotDog', 2, '2008-04-20'),
(2, 'Burger', 4, '2008-10-24'),
(3, 'Chips', 1, '2008-10-31'),
(4, 'Drinks', 3, '2008-10-02');

 

My current QUERY looks like

 

SELECT * FROM `test` WHERE date <= '2008-10-29' ORDER BY `position` ASC

 

which gets me

 

1  HotDog  2  2008-04-20

4 Drinks 3 2008-10-22

2 Burger 4 2008-10-24

 

What I want to do is change the QUERY so that the first rows reurned would be any rows which have  `date` that started 15 days ago.

 

Row 4 has a date of 2008-10-22 and row 2 has a date 2008-10-24, therefore those dates passed less than 15 days of todays date (if that make sense), therefore they would appear at the top of the results.

 

The end result would look something like

 

2 Burger 4 2008-10-24

4 Drinks 3 2008-10-22

1  HotDog  2  2008-04-20

 

Row 1 has a date of 2008-04-20, therefore it's not within 15 days of todays date, therefore it would just return it's self in a `position` order at the bottom.

 

As you will noticed, the order of rows 1 and 4 are in date order showing the one with the closest date to today, at the top.

 

Does that make sense?

 

Can anyone help?

 

Thanks

[bobbinsbro]

am i misunderstanding, or do you just want to us "ORDER BY date DESC"...?

[Canman2005]

Well no, the order by position should remain, I just wanted to put at the top of the results the rows with dates that have passed in the last 15 days. So if ROW 1 has a date of 2008-09-01 and ROW 2 has a date of 2008-10-25 then ROW 1 would be at the top because its date field passed less than 15 days ago. But then keeping the ORDER BY `position` for the rest of the results.

 

If that makes sense?

[solon]

it does!

You should use somthing like:

$today = date("d.m.y");
$fifteen_days_ago = mktime(0,0,0,date("m"),date("d")-15,date("y"));

 

and you use 2 queries:

In your 1st query you do something like this:

SELECT * FROM `test` WHERE `date` ='
$fifteen_days_ago' && `date` <= '$today' ORDER BY `position` ASC

 

And in your 2nd query you use :

SELECT * FROM `test` WHERE `date` > '$today' ORDER BY `position` ASC

 

I think this should do it! 

P.S. change the date() format to the same as your DB.

[Canman2005]

So I should run 2 queries? Run a first one and then run a second under that?

[revraz]

Show the code on how you tried to use INTERVAL.

[Mchl]

Or use UNION statement

 

SELECT * FROM `test` 
WHERE `date` >='$fifteen_days_ago' AND`date` <= '$today' 
ORDER BY `date` DESC, `position` ASC
UNION
SELECT * FROM `test` 
WHERE `date` < '$fifteen_days_ago' 
ORDER BY `position` ASC

 

that's one query, that returns one resultset composed of resultsests from the two queries appended

[Canman2005]

Thanks everyone

 

Because my QUERY is quite complex now, I have decided just to run it twice.