Help with constructing SQL queries

Questions on SQL query construction

1. I have an SQL query (say, "SELECT * from oink WHERE blah = 'foo' AND foo = 'oink' ORDER BY lala DESC") and wish to add an additional clause of "...for all articles before 'date'"
2. Ditto if I wanted to "between 'date1' and 'date2'"
3. Is there a way to say do 2. and add a parameter where date is "2 months previous to current month" (I want to publish 1 year's articles, sorted by month, and obviously that is a rolling figure, not fixed).

How would I construct those queries, please?

Finally, if a record has "NULL" in the "expiry" (date) field would that be interpreted as "never expiring" - if not how would I make a field that defaults to never expiring?


Thank you.
David

All of this depends on how you stored the date. If you used one of mysql's standard date fields it's fairly easy.

1. "select * from oink where blah='foo' and foo='oink' and date_field<'$date' order by lala desc" as long as $date is in an acceptable format (see the mysql manual) OR an easy way is to generate a unix timestamp for $date and replace date_field<'$date' with date_field<from_unixtime($date)

2. "select * from oink where blah='foo' and foo='oink' and date_field between '$date1' AND '$date2' order by lala desc" same comments apply.

3. "select * from oink where blah='foo' and foo='oink' and month(date_field)=month(subdate(now(), interval 2 month)) order by lala desc"

The question about never expiring is not easy. Mysql doesn't expire records, that's up to you. First, you have to determine what you mean by never expiring. Do you want those records to always be returned??? I don't see what you mean by expiring as far as the queries you've asked about since they seem more geared towards a publish date not an expiry date. If you have a seperate field for expiry and would be querying for expired in a seperate way, I would just set the date year to 9999 which mysql supports. It wouldn't require any additional logic in the query. Since an expiry type query would look like:

select * from query where expiry_date>now()

and wouldn't be subject to between, etc.

RE: "never expiring"

Apologies for ambiguity!

This is an article publishing system where we have a field called "created" and one called "expiry" - I want to have records stay valid forever by default in the "expiry" field and only where I set that field with a date will the record "expire"

Thanks

So if it never "expires" you want it to always show up in the listing, right? No matter what month it is, this record will always show?

Just do like rod said and make the year 9999. Then it'll always a value greater than 2 months ago, and it'll always show up.

When it gets to the year 10000, you'll probably have to download a new version of MySQL. Just keep that in mind

---JH

------------------
*************************************************************
* The manual can probably answer 90% of your questions... *
* *
* PHP Manual. www.php.net/manual *
* MySQL Manual: www.mysql.com/documentation/mysql/bychapter *
*************************************************************