Results 1 to 16 of 16
  1. #1
    Intergalactic Trader IGshop's Avatar
    Join Date
    January 18th, 2005
    Posts
    87
    Hi,

    I am trying to query a date field where the items are date ranges (ex. 75-79). In other words "SELECT * from table where date like '%75%'" would work in above field but "SELECT * from table where date like '%76%'" would not.

    Anyone have any ideas? I'm doing this with mysql and php.

    Thanks,

    Jim

  2. #2
    ABW Ambassador
    Join Date
    January 18th, 2005
    Posts
    1,663
    Never used MySQL or PHP but..

    Using ASP and an Access database, my first impulse is to break the field into two. I would use a query in the DB to parse the text and write out the parts to two new fields in yyyy format. Then write the SQL statement to select fields from the query using greater and less than to narrow the selection.

    Keep in mind the problem you'll have when the date range is "98-02." You'll need to write some code in the query to handle turn-of-the-century fields.

    Wayne

  3. #3
    Just Lurking
    Join Date
    January 18th, 2005
    Posts
    1,263
    What Wayne said. Looks like need to write some code any way you look at it. Either to support the query or fix the database. Fix it, save yourself trouble in the long run.

    ------------------------------
    "If all the newbies are going to start writing ebooks maybe I should get started on mine!" -- Buddha

  4. #4
    ABW Ambassador
    Join Date
    January 18th, 2005
    Posts
    4,423
    for this example< i am saying the input is 75.

    select * from table where 75>=left(data,2) and 75<=right(data,2)

    That should do it.

    You could also use the between function, but basically if the syntax is always lower on left, higher on right, there is not trouble parsing it. If you are not sure of the size, because they 01 might just be 1, you could use the locate function to split the field.

    to speed up the search, you should have indexs for both left(data,2) and right (data,2)

    Chet

  5. #5
    Intergalactic Trader IGshop's Avatar
    Join Date
    January 18th, 2005
    Posts
    87
    Thanks guys and Wayne your second point is the sticker. I got it to work with year ranges in the same century but more work is needed when the range spans centuries.

    I guess I will fix the data in the database before I import it and make it easier on myself.

    Regards,

    Jim

  6. #6
    Intergalactic Trader IGshop's Avatar
    Join Date
    January 18th, 2005
    Posts
    87
    Chet,

    Good example. I've been doing this for a month or so. Probably if I'd been doing this for longer than I have I would've known.

    I just found out about the between function and used that with a substring: select * from table where '75' between substring(date,1,2) and substring(date,4,2) works okay. Just not sure how to apply this to the 98-02 situation though.

    Jim

  7. #7
    ABW Ambassador
    Join Date
    January 18th, 2005
    Posts
    1,663
    Good reply Chet.

    I tend to make things harder than they need be. It's the problem with being self-taught.

    Jim,
    Assuming this might be the BFP datafeed and you won't be dealing with 1902 models, you might use "If Then" to write the statement using Chet's suggestion such as:

    If year > 50 Then
    select * from table where year>=left(data,2) and year<=right(data,2)
    Else
    select * from table where year<=left(data,2) and year>=right(data,2)
    End If

    That might handle the century issue?

    Wayne

  8. #8
    Just Lurking
    Join Date
    January 18th, 2005
    Posts
    1,263
    <BLOCKQUOTE class="ip-ubbcode-quote"><font size="-1">quote:</font><HR>Originally posted by Chez Noir:
    for this example&lt; i am saying the input is 75.

    select * from table where 75&gt;=left(data,2) and 75&lt;=right(data,2)

    That should do it.

    You could also use the between function, but basically if the syntax is always lower on left, higher on right, there is not trouble parsing it. If you are not sure of the size, because they 01 might just be 1, you could use the locate function to split the field.

    to speed up the search, you should have indexs for both left(data,2) and right (data,2)

    Chet<HR></BLOCKQUOTE>Damn Chet I actually learned something! Thanks!

    ------------------------------
    "If all the newbies are going to start writing ebooks maybe I should get started on mine!" -- Buddha

  9. #9
    Moderator MichaelColey's Avatar
    Join Date
    January 18th, 2005
    Location
    Mansfield, TX
    Posts
    16,232
    Much easier to clean up the data as you're loading it. You'll be able to select from it much quicker, too. Considering the size of the datafeed you're working with, that could be important.

    Michael Coley
    Amazing-Bargains.com

  10. #10
    Intergalactic Trader IGshop's Avatar
    Join Date
    January 18th, 2005
    Posts
    87
    Wayne,

    Yeah, BFP. I'm going to change the year ranges to four digits...easier to deal with. I'm enjoying the challenge of figuring out how to drill down through the data.

    Regards,

    Jim

  11. #11
    ABW Ambassador
    Join Date
    January 18th, 2005
    Posts
    1,663
    <BLOCKQUOTE class="ip-ubbcode-quote"><font size="-1">quote:</font><HR>Much easier to clean up the data as you're loading it.<HR></BLOCKQUOTE>
    I prefer to never touch the data prior to uploading. But if not possible, I'd try to automate any changes in the uploading process. It's a pain to have to manually modify data. (Or, in my case, remembering to modify the data.)

    Wayne

  12. #12
    ABW Ambassador
    Join Date
    January 18th, 2005
    Posts
    4,423
    sorry ,missed that, if dates don't vary to far, use the year type on the dates,

    You will have to handle the incoming year yourself.

    So
    select * from table where '1995'&gt;=year(left(data,2)) and '1995'&lt;=year(right(data,2))

    anything over 70 gets 1999, 69 and less get 2000.

    Chet

  13. #13
    ABW Ambassador
    Join Date
    January 18th, 2005
    Posts
    4,423
    Also, to add, if you build the indexes correctly and call the select with the same phrasing, the impact should be negligible to having them in seperate fields.

    You can use EXPLAIN to see if the query uses the indexes. the syntax is

    explain select * from table where '1995'&gt;=year(left(data,2)) and '1995'&lt;=year(right(data,2))

    Chet

  14. #14
    Moderator MichaelColey's Avatar
    Join Date
    January 18th, 2005
    Location
    Mansfield, TX
    Posts
    16,232
    <BLOCKQUOTE class="ip-ubbcode-quote"><font size="-1">quote:</font><HR>Originally posted by Chez Noir:
    So
    select * from table where '1995'&gt;=year(left(data,2)) and '1995'&lt;=year(right(data,2))

    anything over 70 gets 1999, 69 and less get 2000.
    <HR></BLOCKQUOTE>That wouldn't work with the BFP feed, as they have parts for cars as old as the 1950's.

    Michael Coley
    Amazing-Bargains.com

  15. #15
    Intergalactic Trader IGshop's Avatar
    Join Date
    January 18th, 2005
    Posts
    87
    Otherwise, it's a great solution, Chet.

    What I ended up doing is convert date ranges to 4 digits in my import script, now I just check to see if the model date is between the model year ranges. Also, in doing so I've become acquainted with Mr. Regex!

    Regards,

    Jim

  16. #16
    Just Lurking
    Join Date
    January 18th, 2005
    Posts
    1,263
    <BLOCKQUOTE class="ip-ubbcode-quote"><font size="-1">quote:</font><HR>Originally posted by IGshop:
    Otherwise, it's a great solution, Chet.

    What I ended up doing is convert date ranges to 4 digits in my import script, now I just check to see if the model date is between the model year ranges. Also, in doing so I've become acquainted with Mr. Regex!

    Regards,

    Jim<HR></BLOCKQUOTE>Mr. Regex! LOL Mr. Regex can fix a lot of problems when comes to data.

    ------------------------------
    "If all the newbies are going to start writing ebooks maybe I should get started on mine!" -- Buddha

  17. Newsletter Signup

+ Reply to Thread

Similar Threads

  1. Featured: How long is your mysql query taking
    By oranges in forum Programming / Datafeeds / Tools
    Replies: 2
    Last Post: August 31st, 2011, 05:37 PM
  2. Need help with MySQL query
    By mobilebadboy in forum Programming / Datafeeds / Tools
    Replies: 3
    Last Post: January 17th, 2007, 03:00 AM
  3. Exporting MYSQL export query to a text file
    By dflsports in forum Programming / Datafeeds / Tools
    Replies: 3
    Last Post: October 22nd, 2006, 11:49 AM
  4. How to speed up the mulit-condition query of mysql
    By concorde in forum Programming / Datafeeds / Tools
    Replies: 13
    Last Post: July 17th, 2005, 03:38 PM

Posting Permissions

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