Results 1 to 1 of 1

Thread: Help with a query

  1. #1
    Join Date
    May 2007
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default Help with a query

    I'm having a problem with an SQL query and I was hoping someone could give me a hand. Here's the query
    PHP:
    PHP Code:
    $query "SELECT DISTINCT shops.id,
                        IF(((
                            ( DATE_SUB(CONCAT(shop_date,' ',shop_time), INTERVAL 1 HOUR) <= '
    $today' )
                            AND
                            ( DATE_ADD(CONCAT(shop_date,' ',shop_time), INTERVAL 1 HOUR) >= '
    $today' )
                        ) OR
                        ISNULL( shop_time )), 'true','false')AS valid,
                  shop_time, shop_date, store_brands.name, stores.store_number,
                        stores.address_l1 AS addy1, stores.address_l2 AS addy2, stores.city, shop_date
                  FROM black_hawk.employees, little_bird.shops, sea_stallion.stores, sea_stallion.times, little_bird.tests, sea_stallion.store_brands, black_hawk.regions
                  WHERE stores.id = times.storeid AND
                        tests.time = times.id AND
                        shops.testid = tests.id AND
                        store_brands.id = stores.brandid AND
                        ((shopper = 
    $myid OR
                        (employees.id = 
    $myid AND
                        employees.region = regions.id AND
                        regions.zipcode = stores.zip AND
                        employees.clearance >= 2)) AND
                        ((shop_date = '
    $today' OR
                        (CONCAT(shop_date,' ',shop_time) <= DATE_ADD('
    $today', INTERVAL 2 HOUR) AND
                        shop_date >= '
    $today'))))"
    and here's some sample SQL as the db would receive it:
    Code:
    SELECT DISTINCT shops.id,
                        IF(((
                            ( DATE_SUB(CONCAT(shop_date,' ',shop_time), INTERVAL 1 HOUR) <= '2007-7-11 15:02:46' )
                            AND
                            ( DATE_ADD(CONCAT(shop_date,' ',shop_time), INTERVAL 1 HOUR) >= '2007-7-11 15:02:46y' )
                        ) OR
                        ISNULL( shop_time )), 'true','false')AS valid,
                  shop_time, shop_date, store_brands.name, stores.store_number,
                        stores.address_l1 AS addy1, stores.address_l2 AS addy2, stores.city, shop_date
                  FROM black_hawk.employees, little_bird.shops, sea_stallion.stores, sea_stallion.times, little_bird.tests, sea_stallion.store_brands, black_hawk.regions
                  WHERE stores.id = times.storeid AND
                        tests.time = times.id AND
                        shops.testid = tests.id AND
                        store_brands.id = stores.brandid AND
                        ((shopper = 2 OR
                        (employees.id = 2 AND
                        employees.region = regions.id AND
                        regions.zipcode = stores.zip AND
                        employees.clearance >= 2)) AND
                        ((shop_date = '2007-7-11 15:02:46' OR
                        (CONCAT(shop_date,' ',shop_time) <= DATE_ADD('2007-7-11 15:02:46', INTERVAL 2 HOUR) AND
                        shop_date >= '2007-7-11 15:02:46'))))
    The long and short of it is that the first value (valid) seems to always return false. It's supposed to return true if the current time ($today) is within an hour (plus an hour, or minus an hour) of the assigned time. It worked in the past but then I made a change so that I acquired the current date and time from the client computer (instead of using NOW() - NOW() is only useful if the server is in the same time zone).

    Is there something going on that I've missed here? The if statement should be returning true if: assigned time - 1 hour is less than or equal to the current time and the assigned time + 1 hour is greater than or equal to the current time OR the assigned time is NULL, but as mentioned it seems to always return false.

    EDIT:
    Sample of returned data from above query (extraneous data removed):
    Code:
    id      valid          time                minus 1 hour            plus 1 hour          shop_time        shop_date
    23  	false  	2007-07-11 14:00:00  	2007-07-11 13:00:00  	2007-07-11 15:00:00  	14:00:00  	2007-07-11
    24 	false 	2007-07-11 16:00:00 	2007-07-11 15:00:00 	2007-07-11 17:00:00 	16:00:00 	2007-07-11
    As you can see, in number 24 all the times are accurate, and the time used (2007-7-11 15:02:46) is between 15:00:00 and 17:00:00, valid returning false anyway.

    Any ideas?
    Last edited by Torin Mai; 07-11-2007 at 07:46 PM. Reason: added sample

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
  •