MYSQL PHP: How to select date 1 year ago (when there is no data for exactly 1 year ago)?

Calculate the date for one month ago.  In the query, pull a range of results (say from "one month ago" to "one month and seven days ago") ordered by the date descending.  Cycle through the records until you hit one with a non-zero value.  If none exist in the recordset, you can either default to a zero value (first entry?) or query further back.

routinet, that idea sounds great!   But I have no idea how to implement it (sorry, it is way beyond my knowledge level).

Could you please show me how you code it?

This is where I was (retrieving data from 1 month ago). How do I amend it to achieve what you said?

<?php
 
$connection = mysql_connect('localhost', 'username', 'password');
$db = mysql_select_db('prices', $connection);
$query_percentage_change =      "SELECT ((t1.`close` - t2.`close`)/t2.`close`) AS percentage_change
                                        FROM `rice` t1
                                        INNER JOIN `rice` t2
                                        ON t2.`date` = DATE_SUB(t1.`date`, INTERVAL 1 MONTH)
                                        WHERE t1.`date` = (SELECT MAX(`date`) FROM `rice`) ";
        $result_percentage_change = mysql_query($query_percentage_change);
        while ( $row = mysql_fetch_array($result_percentage_change) )
        {
                        extract($row);
                        echo "$percentage_change";
        }
?>

Select allOpen in new window

Another alternative would be to use an IF statement to rerun the query if the Value turns out to be empty.

$targetDate = strtotime(1 month ago); # I think you could also use "last month" here

$query = "select Value from table where date = '$targetDate '";
if ($query = False)
   {
   $targetdate = strtotime("-1 day",  $targetdate);
   }
ElseIf ($query = "")
   {
   $targetdate = strtotime("-1 day",  $targetdate);
   }
.
.
.

Actually replace the line above:

     $targetdate = strtotime("-1 day",  $targetdate);

with:

     $targetDate= mktime(0,0,0,date("m"),date("d")-1,date("Y"));

Hi pbeirne,

I think you suggested solution is relative to the current date? I need to -1 day from the latest date from the column instead. The latest date may not be today's date.


Hi routinet,

I don't understand this part

if (!($lastclose = mysql_fetch_assoc($result))) {
  // no rows at all...what do you need to do?
}
if (!($prevclose = mysql_fetch_assoc($result))) {
  // no 'previous' row...what do you need to do?

I suppose I need to add something to where your comments are?

I don't really understand what $lastclose and $prevclose are.

For closure, I want to mention that I have abandoned the idea of obtaining the closest non-weekend date if the date retrieved happens to be a weekend.

Instead, I have chosen to just retrieve data within a time frame, and choose the smallest date.

THANKS for your help, routinet!

I have not tested your suggestion solution, because as I've mentioned on my last comment, I have decided to use an alternative method. Nevertheless, thanks for your guidance to this newbie (me)!

> Instead, I have chosen to just retrieve data within a time frame, and choose the smallest date.

For clarity... by MySQL query.