#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2000
    Posts
    166
    Rep Power
    15
    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
  2. #2
  3. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    19
    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.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2000
    Posts
    166
    Rep Power
    15
    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
  6. #4
  7. Banned (not really)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 1999
    Location
    Brussels, Belgium
    Posts
    14,643
    Rep Power
    4492
    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 *
    *************************************************************

Similar Threads

  1. Cant read sql queries from dump file
    By nukeu666 in forum MySQL Help
    Replies: 1
    Last Post: February 10th, 2004, 12:44 AM
  2. Using textarea to run SQL queries with php
    By OSX in forum PHP Development
    Replies: 4
    Last Post: December 18th, 2003, 05:37 PM
  3. Optimizing SQL queries using MS ACCESS
    By lovingvik27 in forum Database Management
    Replies: 1
    Last Post: November 19th, 2003, 04:44 AM
  4. I need help on SQL queries pleaseee
    By ijakumo in forum MS SQL Development
    Replies: 2
    Last Post: October 22nd, 2003, 05:59 PM
  5. Poor SQL Server Programming, Part 4
    By DavidM in forum MS SQL Development
    Replies: 0
    Last Post: October 21st, 2003, 10:28 PM

IMN logo majestic logo threadwatch logo seochat tools logo