PDA

View Full Version : Help with a query



Torin Mai
07-11-2007, 07:29 PM
I'm having a problem with an SQL query and I was hoping someone could give me a hand. Here's the query
PHP:


$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:

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):

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?