DATE LOOPING IN QUERY

[peter162in]

I have TABLE1 in which data arranged as per date-wise.

I want to find data for previous MONTH/DAY when date is given. if there is no data on previous MONTH/DAY,

my sql has to loop through to find available data for a nearest date.I am using this code. it is working fine but ,

It is manual way and limited to 2-5 days. Any idea to help me. Thanks in advance.

$date1=$_GET['date'];
$difference = strtotime ( '-1 month' , strtotime ( $date1 ) ) ;
$date2=date ( 'Y-m-d' , $difference ) . "<br />\n" ;
$sql2 = "SELECT item, price FROM table1 WHERE date='$date2'"; //first date
$result2 = mysql_query($sql2) or die(mysql_error());
$totalrows = mysql_num_rows($result2);  //gets the total number of rows with this information

if ($totalrows != '0')
{
echo "date is not found in database $date2 !";
} 
else {

$difference1 = strtotime ( ' -1 month -1 day' , strtotime ( $date1 ) ) ;
echo "$difference1". "<br />". "<br />";
$date3=date ( 'Y-m-d' , $difference1 ) . "<br />\n" ;
}

$sql2 = "SELECT item, price FROM table1 WHERE date='$date3'";  // second date
$result2 = mysql_query($sql2) or die(mysql_error());
$totalrows = mysql_num_rows($result2); 
if ($totalrows != '0')
{
echo "date is not found in database $date3!";
} 
else 
{

$difference1 = strtotime ( ' -1 month -2 day' , strtotime ( $date1 ) ) ;
echo "$difference1". "<br />". "<br />";
$date4=date ( 'Y-m-d' , $difference1 ) . "<br />\n" ;
echo"$date4";
}

$sql2 = "SELECT item, price FROM table1 WHERE date='$date4'";  // third date
// Keep on 
//at leat for 5 days. 



while($row = mysql_fetch_array($result2)) {
//  additional code here

 

[PFMaBiSmAd]

find available data for a nearest date

 

In general, you would do this all in your query by forming the absolute value (see the mysql ABS() function) of the difference between the target date and the actual date (see the mysql DATEDIFF() function) and doing an ORDER BY the_difference DESC

 

A difference of zero would mean the date was same as the target date. A difference of 1 (day) would mean that the date was within one day (plus or minus due to the use of the absolute value) of the target date...

[peter162in]

It is possible to calculate nearest date out side databse.. but what about nearest records as per date in the database...I still searching for a solution. any idea how to proceed..pls help me.Thanks in advance. I am a new be..not sound enough in good coding...

Thank you..  Peter