[SOLVED] SQL statement

[avo]

Hi All

 

if i have lots of dates in a sql table formated like 2008-10-01

 

How would i write a sql function please to allow me to get the closest date to NOW() or if there is one the date in the past and forget the future date closest to NOW()

 

Thanks in advance.

 

[themistral]

Think you might need 2 queries

 

function functionName ($now) {
    //get last date prior to now
    $query1 = mysql_query("SELECT field_date FROM table WHERE field_date < '$now' ORDER BY field_date DESC LIMIT 1");
    $result1 = mysql_fetch_array($result1);
    // get next future date
    $query2 = mysql_query("SELECT field_date FROM table WHERE field_date > '$now' ORDER BY field_date ASC LIMIT 1");
    $result2 = mysql_fetch_array($result2);

    // do a calculation to find which of $result1[field_date] and $result2[field_date] is closest to $now
}

 

Be interested to know how you get on!

HTH

[avo]

Hi

 

This looks like it is working well can anyone just have a quick look to see if im missing anything

You was correct i did need two statements.

 

This is what i ended up with

//check to see if there any over due dates
$date_query = mysql_query ("SELECT cal_due FROM serials WHERE cal_due < NOW() ORDER BY cal_due ASC LIMIT 1") or die (mysql_error());
$cal_due=mysql_fetch_array($date_query) ;

if(empty($cal_due['cal_due']))
{
//if not do the future dates and select the closest to NOW()
$date_query = mysql_query ("SELECT cal_due FROM serials WHERE cal_due >= NOW() ORDER BY cal_due ASC LIMIT 1") or die (mysql_error());
$cal_due=mysql_fetch_array($date_query) ;
}

		echo $cal_due['cal_due'] ;
[code/]

[themistral]

Looks OK to me - any errors coming up at all or are you just asking to see if there are improvements that can be made?

[trq]

Better off with something like....

 

<?php

function functionName($now) {
  if ($result = mysql_query("SELECT field_date FROM table WHERE field_date < '$now' ORDER BY field_date DESC LIMIT 1")) {
    if (!mysql_num_rows($result)) {
      if ($result = mysql_query("SELECT field_date FROM table WHERE field_date > '$now' ORDER BY field_date ASC LIMIT 1")) {
        if (!mysql_num_rows($result)) {
          return false;
        }
      }
    }
  }
  $row = mysql_fetch_assoc($result);
  return $row['field_date'];
}

?>

[avo]

Hi

 

Thank you

 

Appreciated.