Thread

Hiya,

Just wondering if it’s possible to do something directly within a query.

Let’s say you have 100 entries, each of which have different expiration dates. What I want to do (which I do have working at the moment but I’m positive there must be a better way than I’ve done it with a lot of PHP in a template currently) is bring back the entries with regards to the expiration dates and place them in a table sorted and coloured such as this :

1 - If expiration date is within 1 month of now place these at the top of the table, sorted and coloured red.
2 - If expiration date is within 3 months of now place these after any red entries and colour those orange.
3,4,5 & 6 - Same sort of thing.

So the question is basically is it possible to bring back a load of results using one query and have them sorted in this way already and somehow marked so that I can colour code them easily?

Thanks for any help on this query gurus 😉

Best wishes,

Mark

Hi Mark,

not easy in only 1 query. Maybe by using sub-queries, but it will not be very effective nor readable.

Maybe if you do something like that (I only put channel_titles, but you can join with channel_data of course):

SELECT 'red' as color, exp_channel_titles.* FROM exp_channel_titles WHERE exp_channel_titles.expiration BETWEEN DATE_SUB(NOW(), INTERVAL 1 MONTH) AND NOW();

for the first query, than

SELECT 'orange' as color, exp_channel_titles.* FROM exp_channel_titles WHERE exp_channel_titles.expiration BETWEEN DATE_SUB(NOW(), INTERVAL 1 MONTH) AND DATE_SUB(NOW(), INTERVAL 3 MONTH);

for the second part, than

SELECT 'green' as color, exp_channel_titles.* FROM exp_channel_titles WHERE exp_channel_titles.expiration  < DATE_SUB(NOW(), INTERVAL 3 MONTH);

for a third query. Like that you can reuse “color” as css class for instance.

Not sure it’s exactly what you need?

Cheers,

Pv

Hiya,

Thanks for that. Yep that’s pretty much what I’m doing at the moment with many different queries but I thought that there might be a better way of doing things.

Thanks for the answer though, much appreciated.

Best wishes,

Mark

Not sure degree it might gain you in efficiency or concept/code simplicity, Mark, but how about a two-step process, having created a field for your color.

- run quick update queries to set the color field according to current date-time. This one can be run on a cron/EEcron basis so it runs only once a day.

- then go ahead with a simple date-time-sorted query to get the on-screen results, using the color field to determine your indicator.

Regards,
Clive

Hi Rob,

Do I need to place something in those WHEN 1 THEN ‘red’ areas?

I’ve never seen anything like that before so not too sure what it all means?

I tried it as a query but everything came back as blue so not too sure what that all means?

Thanks for what looks to be very interesting though 😉

Best wishes,

Mark

So the CASE is run on that calculation, which is meant to calculate how many months are left. I think I have it backwards maybe.

SELECT *,
    CASE FLOOR((exp_channel_titles.expiration_date - NOW())/(24*60*60*30))
        WHEN 1 THEN 'red'
        WHEN 2 THEN 'orange'
        WHEN 2 THEN 'yellow'
        WHEN 3 THEN 'green'
        ELSE 'blue'
    END AS color
FROM exp_channel_titles
ORDER BY exp_channel_titles.expiration_date ASC

If there’s one month left, then red, if there’s two, then orange, and so forth.

Ah I see what it’s doing now.

Only problem is it doesn’t seem to be working as all entries are still coming back as being set to blue although there are definitely many different expiration dates in the channel. Some being within 1 month and some further back and so on and so forth :-(

Best wishes,

Mark

probably needs mentioning also that this kind of method is going to be all the same/all different depending on which database you are actually using.

A little discussion of the kind: http://stackoverflow.com/questions/4622/sql-case-statement-syntax

Appreciate that it’s fun when you can get a declarative software arrangement to do what you mean, though, Rob 😉

Experience has probably made me simpler-minded…

C.

Aha got it!

Needed to use

UNIX_TIMESTAMP(NOW())

as I wasn’t too sure what NOW() was bringing back and so brought that back as one of the fields and saw that it was formatted as YYYY-MM-DD HH:MM:SS so needed to convert it to a timestamp in order for the calculation to make sense.

Thanks loads for that code Rob it should come in really handy on this.

Best wishes,

Mark

Sweeeeeeet.