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/