ron.piggott@xxxxxxxxxxxxxxxxxx wrote:
The query from my previous post was only part of a larger query. This is
the entire query:
SELECT GREATEST( IF( CURDATE( ) >= DATE_SUB( DATE( FROM_UNIXTIME(
1239508800 ) ) , INTERVAL LEAST( 14, (
SELECT COUNT( * )
FROM `verse_of_the_day_Bible_verses`
WHERE seasonal_use =1 ) )
DAY )
AND CURDATE( ) <= DATE( FROM_UNIXTIME( 1239508800 ) ) , 1, 0 ) , IF(
CURDATE( ) >= DATE_SUB( DATE( 2009 -12 -25 ) , INTERVAL LEAST( 14, (
SELECT COUNT( * )
FROM `verse_of_the_day_Bible_verses`
WHERE seasonal_use =2 ) )
DAY )
AND CURDATE( ) <= DATE( 2009 -12 -25 ) , 2, 0
)
) AS verse_application
It took me a while to work out what this was trying to do, that's
complicated.
Reformatted a little:
SELECT
GREATEST(
IF
(
CURDATE() >=
DATE_SUB(
DATE(FROM_UNIXTIME(1239508800)),
INTERVAL LEAST(14, (SELECT 1)) DAY)
AND CURDATE() <= DATE(FROM_UNIXTIME(1239508800)),
1,
0
),
IF
(
CURDATE() >=
DATE_SUB(
DATE('2009-12-25'),
INTERVAL LEAST(14, (SELECT 2)) DAY)
AND CURDATE() <= DATE('2009-12-25'),
2,
0
)
) AS verse_application;
(which isn't much better in email).
You're not getting '2' because the second part is returning 0.
I substituted dummy variables for your subqueries (select 1 and select 2).
SELECT COUNT( * )
FROM `verse_of_the_day_Bible_verses`
WHERE seasonal_use =2;
What does that return by itself?
that is what your query will run instead of my 'select 2'.
That in turn goes into the
select least(14, result_from_above_query);
and takes that away from date('2009-12-25');
If the current date is not in that range, it will return 0.
Here's the second part of your query isolated for you to test:
SELECT
IF
(
CURDATE() >=
DATE_SUB(
DATE('2009-12-25'),
INTERVAL LEAST(14, (SELECT COUNT(*) FROM
verse_of_the_day_Bible_verses WHERE seasonal_use=2)) DAY)
AND CURDATE() <= DATE('2009-12-25'),
2,
0
)
;
--
Postgresql & php tutorials
http://www.designmagick.com/
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
