List:General Discussion« Previous MessageNext Message »
From:Chris W Date:September 29 2010 12:33am
Subject:Re: Not to show until a certain date
View as plain text  
  On 9/28/2010 10:04 AM, Patrice Olivier-Wilson wrote:
> Figured it out
>
> SELECT *
> FROM announcements
> WHERE announcements.announcements_expiredate >CURDATE() AND 
> announcements.announcements_postdate<CURDATE()
> ORDER BY announcements_expiredate ASC
>
>

I think you probably should do it like this.

SELECT *
FROM announcements
WHERE announcements_expiredate > CURDATE()
AND announcements_postdate <= CURDATE()
ORDER BY announcements_expiredate ASC

Otherwise they won't show till after the postdate.  I assume you want to 
display them on the post date and not the next day?  This of course 
assumes your field is of type 'date' and not 'datetime'.

Prefixing the field name with the table name is not needed unless you 
have a join with a table with the same field names.  Based on your field 
naming method it appears as though that won't happen.  If it does, it is 
much less to type and easier to read if you alias the table name. like 
this......

SELECT *
FROM announcements a
WHERE a.announcements_expiredate >CURDATE()
AND a.announcements_postdate<=CURDATE()
ORDER BY a.announcements_expiredate ASC

also it is a good habit to get into to have all filed and table names 
enclosed in back ticks just in case you have field names that are sql 
reserved words or otherwise would confuse MySQL.

SELECT *
FROM `announcements` a
WHERE a.`announcements_expiredate` >CURDATE()
AND a.`announcements_postdate` <= CURDATE()
ORDER BY a.`announcements_expiredate` ASC


Also to me it just makes it easier to read/ understand if you second 
condition is rewritten like this...

AND CURDATE() >= announcements_postdate

Just my opinion on that.

Chris W

Thread
Not to show until a certain datePatrice Olivier-Wilson28 Sep
  • Re: Not to show until a certain datePatrice Olivier-Wilson28 Sep
    • Re: Not to show until a certain dateChris W29 Sep
      • Re: Not to show until a certain datePatrice Olivier-Wilson29 Sep
        • Re: Not to show until a certain dateHank29 Sep
          • Re: Not to show until a certain dateJoerg Bruehe1 Oct
            • Re: Not to show until a certain dateHank1 Oct
              • RE: Not to show until a certain dateBMBasal1 Oct
                • Re: Not to show until a certain dateHank2 Oct
              • Re: Not to show until a certain dateJoerg Bruehe1 Oct