Re: [PHP-DB] Mysql query

[email protected] 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/