Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    New to the CF scene
    Join Date
    Mar 2009
    Location
    Whisky Country
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    DATE_FORMAT() with LEFT JOIN Problem

    I have a server at home I use for testing code and it is running MYSQL version 5.0.27 . When I run the query below it works fine -

    $sql = "SELECT voucher AS voucher, DATE_FORMAT(slotDateTime, '%H:%i') AS st, firstName AS fn,lastName AS ln, address1 AS a1, address2 AS a2, address3 AS a3, tel1 AS t1, tel2 AS t2, clientNote AS cn FROM Booking LEFT JOIN Voucher USING (voucher) WHERE DATE_FORMAT(slotDateTime, '%D %M') = '$_POST[seldate]' ORDER BY slotDateTime ASC";

    However my website hosting company is running MYSQL version 4.1.22 and that query doesn't work. If I amend it to the one below it works but I only want the time information displayed on the page so the DATE_FORMAT(slotDateTime, '%H:%i') bit is quite important.

    $sql = "SELECT slotDateTime AS sdt, voucher AS voucher, firstName AS fn, lastName AS ln, address1 AS a1, address2 AS a2, address3 AS a3, tel1 AS t1, tel2 AS t2, clientNote AS cn FROM Booking LEFT JOIN Voucher USING (voucher) WHERE DATE_FORMAT(slotDateTime, '%D %M') = '$_POST[seldate]' ORDER BY slotDateTime ASC";

    The coding is part of an online booking system for a local club and it allows the instructors to look at the client details on booked time slots. I'm pretty sure that if I approach the web hosting company they will not update the MYSQL version they are running or want to charge me an arm and a leg to do so.

    Is there another way to write the query that will work in version 4.1.22 ?

    Any help appreciated as this is driving me mad.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,585
    Thanks
    80
    Thanked 4,497 Times in 4,461 Posts
    I'd fix this in the PHP code.

    I don't know PHP, but surely there must be a way in PHP to separate the "D" (day) and "M" (month) parts from the user's input $_POST[seldate], yes?

    So then just check for them separately:
    Code:
    ... WHERE DAYOFMONTH(slotDateTime) = $seldate_DAY AND MONTH(slotDateTime) = $seldate_MONTH ...
    (I made up those variable names--$seldate_DAY, $seldate_MONTH--that I expect you to supply. Note that the should be integers, or at least strings that are integer values.)

    The MYSQL functions DAYOFMONTH() and MONTH() date back to at least 3.23.

    (And chances are this will be somewhat more efficient than using FORMAT would be, in any case.)

  • #3
    bdl
    bdl is offline
    Regular Coder
    Join Date
    Apr 2007
    Location
    Camarillo, CA US
    Posts
    590
    Thanks
    4
    Thanked 83 Times in 82 Posts
    Quote Originally Posted by foxfour
    However my website hosting company is running MYSQL version 4.1.22
    Holy crap, really? Can you move? Have you asked if they offer 5.x on an alternate server? Our old web host is using a 'beta' 5.0.x server (even though 5.1 is the current production release) inline with their legacy 4.1 server, and you can use either if you need the updated functions.


    Code:
    SELECT
     voucher AS voucher
    , DATE_FORMAT(slotDateTime, '%H:%i') AS st
    , firstName AS fn
    , lastName AS ln
    , address1 AS a1
    , address2 AS a2
    , address3 AS a3
    , tel1 AS t1
    , tel2 AS t2
    , clientNote AS cn
    FROM Booking
    LEFT JOIN Voucher USING (voucher)
    WHERE DATE_FORMAT(slotDateTime, '%D %M') = '$_POST[seldate]'
    ORDER BY slotDateTime ASC
    This doesn't work in 4.1? You're still using DATE_FORMAT in the WHERE clause in either statement, so I don't get why it doesn't work. Is there an error message returned?



    Quote Originally Posted by Old Pedant
    I don't know PHP, but surely there must be a way in PHP to separate the "D" (day) and "M" (month) parts from the user's input $_POST[seldate], yes?
    Yes of course you can do this with PHP. I'm more concerned that the OP is actually allowing POST data straight into the SQL statement without validating or escaping it.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,585
    Thanks
    80
    Thanked 4,497 Times in 4,461 Posts
    LOL! Darned good point, BDL!

    I admit I glossed over that, just thinking he was showing sample code, not the real thing. But I'll bet you are right!

    So if he validates the POST data and separates it into INTEGER month and day and uses that as I suggested, he will have accomplished it all: Better performance and no SQL Injection vulnerabilities.

    p.s.: Yes, I really knew PHP could do that. I should have said that I just don't happen to know the right functions to call to do so.

  • #5
    New to the CF scene
    Join Date
    Mar 2009
    Location
    Whisky Country
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for the replies guys. I've had a response form the hosting company and they have offered to move me on to a server running MYSQL version 5.0.67 for a one off fee of £15+VAT ($25). I think I will go with that, I'm not too happy with paying but considering how much of my time I've wasted with this it's probably worth it.


  •  

    Posting Permissions

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