Mysql query

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

The result should be a "2". I am getting a 0.

When I try the first subquery / IF statement the error message is:

#1064 - You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near
'IF( CURDATE( ) >= DATE_SUB( DATE( FROM_UNIXTIME(1239508800) ) , INTERVAL
LEAST( '

The error message for the Christmas check which should be giving me a "2"
result is:

#1064 - You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near
'IF( CURDATE( ) >= DATE_SUB( DATE( 2009 -12 -25 ) , INTERVAL LEAST( 14, (

SELE' at line 1

Any help out there please?

Ron


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Mysql query

ron.piggott@actsministries.org 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

Re: Mysql query

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@actsministries.org 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