SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Member
    Join Date
    Nov 2009
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Excluding certain results from a query

    Hi folks. I have a database with a date field where the dates are stored in a format like yyyy-mm-dd. I want my users to be able to search the entire date range but exclude results from certain years. Here's the query I've got:

    Code:
    $query = "select * from table where date like \"%$trimmed%\" order by date";
    How should I rewrite this to get the effect of
    Code:
    $query = "select * from dailies where date like \"%$trimmed%\" (EXCEPT IF DATE IS 1980-06 to 1980-12) order by date";
    Thanks greatly for your help.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,328
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    first of all, if this is an actual DATE or DATETIME column (as opposed to VARCHAR), then

    1. it isnt stored in the format you think it is, and
    2. you shouldn't be using LIKE on it

    could you please explain a bit more what you want to search for?

    the "except" part of the query will look like this --
    Code:
    AND ( datecolumn < '1980-06-01' 
       OR datecolumn >= '1981-01-01' )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Nov 2009
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Rudy - the date column is listed as a "date" field and not a varchar field and the dates within it have the format of 1980-09-27 for example.

    This may be a bit of an oddity - the data in this database was converted from an MS Access database into MySQL and so there might be something funny about the contents of the date column. The column type is definitely "date" though.

    What I'm trying to do is create a searchable archive of comic strips where we allow people to search all the records using the date column, but we want to exclude six month's worth from near the beginning. So we'd return no results or a friendly message when people search for anything from January 1st to June 1st of 1980, but show them strips from any other date range. It's complicated, and we have to be able to change the date range later.

    Right now the date search works, but I'm having trouble restricting access to that six-month set of data. I'll try your suggestion.

  4. #4
    SitePoint Member
    Join Date
    Nov 2009
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That worked perfectly - thanks Rudy. You've helped me out so often I'm going to go buy your book. Much appreciated.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,328
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    okay, let me know if you have any problems

    and remember not to use LIKE on a DATE column

    as for the format, it only ~appears~ that the stored format of a DATE column is yyyy-mm-dd, probably because the year-month-day sequence is the only way mysql will accept a date literal value

    note that you can specify valid date formats not only like '2010-02-13' but also like '2010#01#13' and '2010;01;13' and 20100113 (integer)

    regardless of which valid input format you use, it is converted to an internal format for storage

    internally, the format is stored in a way that you wouldn't believe, and that you never get to see, because when you display a date, it is always converted to an output format, which, by default, is yyyy-mm-dd
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Member
    Join Date
    Nov 2009
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    and remember not to use LIKE on a DATE column
    Regarding the above, what reasons are there for not using LIKE? It seems to work as required and has the beneficial effect of letting people search for, say, 2002-02 and getting all the data for February of 2002, or searching for just 2002 and getting a year's worth of data.

    Thanks for your help.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,328
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    it's inefficient (that's the main reason) and susceptible to changes in default data display format
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Member
    Join Date
    Nov 2009
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks again Rudy. I just ordered your book.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •