Date function in Mysql Query 1

[SPYDERIX]

Hi there,

I'm wondering if it's possible to do a certain query in mysql without having to write multiple queries. I have a site that has some travel trips and store the trip date range in 2 fields: date_start and date_end

Let's for example assume the following trips:
1 - Hawaii Start: 2016-01-01 End: 2016-01-31
2 - Mexico Start: 2016-02-01 End: 2016-02-29
3 - Caribbean Start: 2016-03-01 End: 2016-03-31

I'm ordering these by date_start ASC but of course once the Hawaii trip is over it would still show up naturally because the start date is still before the others. I need it to stay in the list for only 1 or 2 months (to show people what kinds of trips the company does). Eventually based on date it will need to automatically fall off. I do have another field in the DB for visability to switch from visible to hidden but if that is forgotten I need this to eventually disappear. Once the old trip has come and gone though I need to show it at the end of the list or at least more importantly, show the next trip in the proper order. I'm thinking this will need to likely just be 2 different queries vs 1 but still need someway of coding my query to grab a trip where the date_end is within the last 2 months from the current time of the query. If this is possible with one call instead that would be ideal as I have navigation links that are auto-populated from this to show the whole list and then some pages that only grab the next 2 in the list that are upcoming. Any way to do this? If it's not possible to do as one call then that's just the way it is but I need the date thing to work if that's possible. Thanks.

 

[feherke]

Hi

Like this ? Only events that are visible and ended less than 30 days ago, with ongoing and future events first, ended events last.

[b]select[/b]
[teal]*[/teal]

[b]from[/b] spyderix

[b]where[/b] visible
[b]and[/b] datediff[teal]([/teal]end_date[teal],[/teal] current_date[teal]) > -[/teal][purple]30[/purple]

[b]order by[/b] end_date [teal]<[/teal] current_date[teal],[/teal] start_date

Feherke.
feherke.ga

 

[SPYDERIX]

I will try that out and get back to you. Thanks

NATE

 

[SPYDERIX]

That works! I tweaked it slightly to:

SELECT * FROM `trips` WHERE `viz` = 'Y' AND DATEDIFF(`date_end`, '2016-01-01') > -30 ORDER BY `date_end` < '2016-01-01', `date_end`

One thing I find curious and can't wrap my head around is the ORDER BY statement. Don't get me wrong, this SQL works exactly in the correct order but this part: `date_end` < '2016-01-01' looks to be written as if any number less than 2016-01-01 would show first yet it's the opposite. I guess it's a date thing and doesn't work like a strict number. A `date_end` less than 2016-01-01 is actually a date "after" it??? Seems backwards.

NATE

 

[feherke]

Hi

`date_end` < '2016-01-01' looks to be written as if any number less than 2016-01-01 would show first yet it's the opposite.

Putting that expression into [tt]select[/tt]'s field list explains it : it results
[ul]
[li]0 when the end_date before current_date is false[/li]
[li]1 when the end_date before current_date is true[/li]
[/ul]
So is just integer ordering. But indeed looks abit counterintuitive.

Feherke.
feherke.ga

 

[SPYDERIX]

Ah, that makes perfect sense. Thanks a ton!

NATE