SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Enthusiast lrempel's Avatar
    Join Date
    Jan 2005
    Location
    Vancouver
    Posts
    42
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Inconsistent ColdFusion/MySql results

    i have a mysql query which i have written in a coldfusion template. when i test it on my local machine (running CFMX and MySql 4.1.5-gamma-nt) it works perfectly. when i upload it onto my host's server (running CFMX and MySql 4.0.15-nt) and test it there, it doesn't work in the same way (i'll explain the details below). however, i also tested it through my hosts phpMyAdmin interface (i thought it might be the different MySql versions which was causing the problem) and it works perfectly.

    Here's my query:

    SELECT DISTINCT fld_fname, fld_lname, min(fld_dte) AS fromDte, max(fld_dte) AS toDte
    FROM tbl_athletes, tbl_events
    WHERE tbl_events.fld_dte BETWEEN DATE '2005-01-01' and DATE '2005-06-01'
    AND tbl_athletes.fld_athlete_id = tbl_events.fld_athlete_id
    GROUP BY fld_lname, fld_fname

    on my local machine and the phpMyAdmin interface the results for fromDte and toDte come out looking like dates. on my host's server however, fromDte and toDte come out as a bunch of numbers - it says it's binary format.

    i can't figure out why this is happening, but i know it has to do with the max and min, because just selecting all the fld_dte results works fine (it gives them in date format). the problem is, i really need to only get the oldest and most recent dates in my results, and i can't think of another way to do this.

    so if you can tell me what i'm doing wrong (or why things are happening the way they are) or else suggest a different way of achieving the results i want, i would appreciate it.

    thanks.

  2. #2
    SitePoint Wizard davidjmedlock's Avatar
    Join Date
    Dec 2002
    Location
    Nashville, TN USA
    Posts
    1,688
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Are the versions of ColdFusion MX the same? Maybe there's a driver on your server that's not playing nice with the version of CFMX on your server... Could be that version of MySQL, too.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    the use of the DATE keyword is unusual, i think that's standard sql but i rarely see it, i know the query will work in mysql without it

    fld_dte is a DATE or DATETIME datatype?

    also, you don't need DISTINCT here
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Enthusiast lrempel's Avatar
    Join Date
    Jan 2005
    Location
    Vancouver
    Posts
    42
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    they are the same versions of ColdFusion MX.

    fld_dte is a date datatype - i don't need or want time info.

    thanks for the tip about DISTINCT - i had it in before i was using min/max

    anyways - i removed the DATE keyword in my BETWEEN clause, and still the same thing happened - the result gets converted to a binary datatype, which i can't work with because i need them to be dates!

    any suggestions of a different way of achieving the same results? i'm open to changing my query. any other help you can provide would be good, i'm kinda stuck here...

    thanks

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    how about forcing the min/max to operate on strings instead? unfortunately this means that you would have to see the results in yyyy-mm-dd format
    Code:
    select fld_fname
         , fld_lname
         , min(date_format(fld_dte,'%Y-%m-%d')) as fromDte
         , max(date_format(fld_dte,'%Y-%m-%d')) as toDte
      from ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Enthusiast lrempel's Avatar
    Join Date
    Jan 2005
    Location
    Vancouver
    Posts
    42
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    r937,

    i tried the code you posted, but the results are exactly the same - fromDte and toDte come out as binary. it was a good idea, i don't mind working with dates in yyyy-mm-dd format, that's how they are in the database.

    any other thoughts? anyone who might know WHY this is happening feel free to jump in. i still don't know if it is a mysql or coldfusion problem, or a combination of the two.

    thanks for the suggestions so far.


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
  •