Chris I spent 3 hours debugging this query myself. I got as far as
putting '' around 2009-12-25 to get the desired results. I just added the
word DATE. It works, thanks.
Chris I run a verse of the day e-mail list. This query determines the
logic of the content (year round, Easter and Christmas). It is quite the
query to say the least.
Thanks for your help.
Sincerely,
Ron
www.TheVerseOfTheDay.info
> 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
