Page 1 of 1

Filtering data depending upon date

Posted: Mon Oct 30, 2006 4:09 pm
by impulse()
In a MySQL table I have a date field which is in the format of YYYY-MM-DD. I also have a switch statement to filter the results depending on the value of a drop-down list (Jan, Feb, Mar etc). I have used the following switch statement:

Code: Select all

switch ($dropdownValue) {
  case "January":
    mysql_query("SELECT * FROM counter WHERE date BETWEEN 2006-09-01 AND 2006-09-31");
    break;
And that continues for each month.

But this doesn't display any dates no matter which month I select. I have a feeling it's the date itsself. Am I going to have to convert each date to a unix timestamp to perform the sort correctly?

Regards,

Posted: Mon Oct 30, 2006 4:19 pm
by volka
  1. The field `date` is of what type?
  2. You're sending the dates as string literals -> BETWEEN '2006-09-01' AND '2006-09-31'
    otherwise they are treated as numbers, i.e. date between 1996 AND 1966
  3. case "January":
    mysql_query("SELECT * FROM counter WHERE date BETWEEN 2006-09-01 AND 2006-09-31");
    break;
    You do not store the result resource of that query. How do you fetch the data?

Posted: Tue Oct 31, 2006 3:38 am
by impulse()
The field type if of type 'Date'.

The code should've been:

Code: Select all

switch ($dropdownValue) {
  case "January":
    $query = mysql_query("SELECT * FROM counter WHERE date BETWEEN 2006-09-01 AND 2006-09-31");
    break;

So MySQLs 'BETWEEN' is used for strings and not integers?

Posted: Tue Oct 31, 2006 4:01 am
by volka
impulse() wrote:So MySQLs 'BETWEEN' is used for strings and not integers?
That's not what I've said.
If you do not mark your date literals it as strings they are treated as numbers and minus signs - this has nothing to do with between, it's how the mysql parser works. And the php parser as well.

Code: Select all

<?php
$a = 2006-9-1;
echo $a, " ", gettype($a);
?>
1996 integer

Posted: Tue Oct 31, 2006 5:41 am
by impulse()
I was just being stupid, as usual. I have now wrote some working code to perform this. I'm not using MySQLs 'BETWEEN' but a simple:

Code: Select all

WHERE date >= '2006-01-01' AND date <= '2006-01-31'
I have a problem of thinking everything's a lot more complicated than it actually is.

Posted: Tue Oct 31, 2006 8:14 am
by volka
would be working with BETWEEN as well.

Posted: Tue Oct 31, 2006 8:22 am
by xpgeek
Keyword date is reserved in mysql.
You must use `date` instead of date.
Thank You.

Posted: Tue Oct 31, 2006 8:41 am
by Weirdan
xpgeek wrote: You must use `date` instead of date.
'date' is a special case, it's not required (though desirable) to quote the 'date' keyword when it's used as a column name.