my sql change to nearest date if data not exists for a query auto matically

[peter162in]

How to find out nearest date data available in my sqldata base, If data not exists for a particular date.

for eg (without syntax)

$date = getdate

query select all where date is $date:

now no data in the data base for this date.

if empty result = $date = $date-1

keep on looping

any way to proceed .

 

any old page to get some reference

thanks in advance

[manwhoeatsrats]

use something like this

 

<?php
$totalrows = mysql_num_rows($the_results_of_your_query);  //gets the total number of rows with this information

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

 

as for the nearest date...I am not sure.  see I have always stored a unix time stamp in the database instead.  if it is in a unix time stamp, then you can easily preform another query with something like.  if it is not there, then is there one within the next 3600 seconds?

 

[akitchin]

one option is to ORDER BY the date difference between the input and the date column:

 

SELECT * FROM table ORDER BY DATEDIFF('$date', date_column) ASC

 

keep in mind that the input $date needs to be in the MySQL DATE or DATETIME format. see here for more date/time functions.

[peter162in]

Thanks in advance.

this is my code I derived from as your help.May be my question is mis interpreted.

 

<?php

 

include "dbcon1.php";

 

$date1=$_GET['date'];

 

$sql = "SELECT *  FROM table WHERE date=$date1;

 

$result = mysql_query($sql) or die(mysql_error());

 

$totalrows = mysql_num_rows($result);  //gets the total number of rows with this information

 

if ($totalrows == '0')

{

echo"$totalrows". "<br />";

echo "Date is not in database";

$difference = strtotime ( '-1 month,-1 day' , strtotime ( $date1 ) ) ;

 

$date2=date ( 'Y-m-d' , $difference ) . "<br />\n" ;

echo"$date2". "<br />";

$sql3 = "SELECT *  FROM table WHERE date='$date2'";

 

$result2 = mysql_query($sql3) or die(mysql_error());

 

$totalrows1 = mysql_num_rows($result2);  //gets the total number of rows with this information

 

echo"$totalrows1". "<br />";

 

} else {

    echo "date is found in database woot!";

 

 

}

 

 

 

 

  ?>

Query should loop through if data is not there in the database! Now it is working if data is not there specified date( one date is reduced) . if more than one date is to reduced( data is not there for the second date also - program has to find nearest  data available  date -it has to loop through. Any Idea. Thank for great reply. I get something which I can think to get part of target. But for more accurate I need help .