Problem composing a working query with dates in DDBB

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Perfidus
Forum Contributor
Posts: 114
Joined: Sun Nov 02, 2003 9:54 pm

Problem composing a working query with dates in DDBB

Post by Perfidus »

Hi there!

I have a table in a database called events with the following rows:
dateopen => When the event starts, is a 'date' field.
dateclose => When the event finishes, another 'date' field
event => Description of the event

What I would like to do is to get all the values that have a coincidence in a certain period:
Let's say the period goes from $fecha1 = 2009-09-01 to $fecha2 = 2009-09-05.
This is the farest I have gone and it retruns nothing event though I have 2 events in DDBB which are:
2009-09-01 / 2009-09-05 and 2009-09-03 / 2009-09-15.
I guess there's something wrong with the syntax or maybe the operators are messy.
Any clues?

Code: Select all

    $sql2 = "SELECT * FROM events WHERE 
    ((dateclose <= $fecha2) && (dateclose >= $fecha1)) || 
    ((dateopen >= $fecha1) && (dateopen <= $fecha2)) || 
    ((dateopen <= $fecha1) && (dateclose >= $fecha2))";
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Problem composing a working query with dates in DDBB

Post by requinix »

$fecha1 and $fecha2 may be strings in PHP, but MySQL doesn't know that. You didn't use quotes in the query so all MySQL sees is 2009-09-01 - which looks a lot like "2009 minus 09 minus 01".
Perfidus
Forum Contributor
Posts: 114
Joined: Sun Nov 02, 2003 9:54 pm

Re: Problem composing a working query with dates in DDBB

Post by Perfidus »

I have asked before to thank you for the light in the way, it was exactly what you said.
I don't know why my comment haven't been published.
By the way, I have a different enigma right now:
Now I'm trying to see if certain date, like 2009-09-04 is inside one of the periods in my table and this is what I have found:

Code: Select all

$sql2 = "SELECT * FROM events WHERE 
    ((dateopen = '$fecha') || (dateclose = '$fecha') || ((dateopen < '$fecha') && (dateclose > '$fecha')))";
This code returns some of the rows, but not all, for example a period that starts before the date and finishes after it...
I think this case is already considered in the query but there must be something wrong...
Perfidus
Forum Contributor
Posts: 114
Joined: Sun Nov 02, 2003 9:54 pm

Re: Problem composing a working query with dates in DDBB

Post by Perfidus »

Forget it, it is working, it was a silly mistake somewhere else...
Post Reply