Ok, just a brief one, but important, for me, nonetheless!
I have a page on our site that displays various special offers – the CMS part is coded so that each offer is an entry in the a database, the form contains two date variables, offer start and offer end dates. The idea being that you can input a special offer record in advance, it will appear on the day the offer starts and disappear the day following the offer ending, as the advert says…We do a similar thing on our vacancies page, the only difference being that some offers do not have end dates.
Why have this? Isn’t it just overcomplicating? Sure, you could just enter a date far into the future, but that adds to the guesswork, so the section was designed to allow a NULL date entry on the end date field which would allow the record to stay on the site indefinitely, the problem? Constructing the SQL statement to let that happen….well it may be simple to some, but here’s my solution!
SELECT *
FROM specialofferstable
WHERE offerenddate >= date() or offerenddate is null
ORDER BY offerstartdate ASC
The problem was having the SQL select records that were in date OR nulls, of course I was trying =null and variations thereof, until I found a simple tutorial on SQL statements that showed the way. Hyper advanced SQL it may not be, but Dreamweaver says it’s advanced and I’m happy to have figured it out!!
select * is currently thought of as bad practice among DBAs, mainly because of performance problems and difficulty of debugging. Probably won’t affect your application, but maybe worth knowing about? http://www.tom-muck.com/blog/index.cfm?newsid=36
In one fall swoop exposing my shortcomings as any sort of DBA! I have to be honest I just do what I’m told (by Dreamweaver) but thanks for the link – very useful, I have just edited the offers page from * (nine) to three fields from the database, hopefully that will have a positive effect on the site – will sort out the rest of the dynamic pages when I can…