Filtering data depending upon date

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
impulse()
Forum Regular
Posts: 748
Joined: Wed Aug 09, 2006 8:36 am
Location: Staffordshire, UK
Contact:

Filtering data depending upon date

Post 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,
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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?
impulse()
Forum Regular
Posts: 748
Joined: Wed Aug 09, 2006 8:36 am
Location: Staffordshire, UK
Contact:

Post 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?
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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
impulse()
Forum Regular
Posts: 748
Joined: Wed Aug 09, 2006 8:36 am
Location: Staffordshire, UK
Contact:

Post 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.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

would be working with BETWEEN as well.
User avatar
xpgeek
Forum Contributor
Posts: 146
Joined: Mon May 22, 2006 1:45 am
Location: Kyiv, Ukraine
Contact:

Post by xpgeek »

Keyword date is reserved in mysql.
You must use `date` instead of date.
Thank You.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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.
Post Reply