Prev: Mysql query
Next: EZPDO Replacement
From: ron.piggott on 13 Dec 2009 19:06
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

From: Chris on 14 Dec 2009 21:36
ron.piggott(a)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/

 | 
Pages: 1
Prev: Mysql query
Next: EZPDO Replacement