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

PHP MYSQL


Hello,

This question is related to http://www.experts-exchange.com/Q_23476744.html (please read).
I have a dilemma.

I have a table with 2 fields
[ Date ] [ Value]

I want to obtain the percentage change over 1 month from the last date (which is June 11). But 1 month ago, May 11 is a Sunday, so there is no data on that date.

How can I select a date immediately prior to May 11 if there is no data for that date, and one more day prior if there is still no data for that date either?


gingeraAsked:
Who is Participating?
 
Steve BinkConnect With a Mentor Commented:
For my solution, I changed the query to use a UNION.  This is basically two queries merged together into one result.  In this particular scenario, it fits.  The first SELECT pulls the record with the latest `date` field.  The second SELECT looks for all records at least $query_interval in the past, and pulls the first one it finds.  In this example, the interval is "1 MONTH", so it begins looking one month in the past, relative to MAX(`date`).

You should consider renaming your fields.  "date" is a reserved word in MySQL, and it can get confusing to use that as an object name.  At the very least, changing it to "dateField" or "closeDate" will help keep it straight.

One last thing: I'm not sure how the optimizer will handle the second subquery.  It SHOULD just run it once.  If not, this query will start slow, and get slower as time goes on.
<?php 
$connection = mysql_connect('localhost', 'username', 'password');
$db = mysql_select_db('prices', $connection);
 
// Note the new query.  You can set the interval to whatever you want.
$query_interval="1 MONTH";
$query="SELECT `close`,`date` FROM `rice` WHERE `date`=(SELECT MAX(`date`) FROM `rice`) " .
       "UNION " .
       "(SELECT `close`,`date` FROM `rice` WHERE " .
       "`date`<=DATE_SUB((SELECT MAX(`date`) FROM `rice`),INTERVAL $query_interval) " .
       "ORDER BY `date` DESC LIMIT 1)";
if (!($result = mysql_query($query))) {
  // query failed.  report and ...
  die();
}
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?
}
$percentage_change = ($lastclose['close'] - $prevclose['close'])/$prevclose['close'];
echo $percentage_change;
?>

Open in new window

0
 
Steve BinkCommented:
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.
0
 
gingeraAuthor Commented:
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";
        }
?>

Open in new window

0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
pbeirneCommented:
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);
   }
.
.
.
0
 
pbeirneCommented:
Actually replace the line above:

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

with:

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

0
 
gingeraAuthor Commented:
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.

0
 
Steve BinkConnect With a Mentor Commented:
In my example, $lastclose will hold the row associated with the latest close date, while $prevclose will hold the row associated with the next row found after the interval.  In terms of your previous code, $lastclose['close'] = t1.close, and $prevclose['close'] = t2.close.

The two sections with comments are indeed there for you to fill in.  You can put anything you want in there, even nothing at all.  If absolutely zero rows are returned (couldn't find the date for either the current OR the previous entry), how do you want your application to respond?  If you get the current row, but not a previous row, how would you like it to respond?  Code to those ends, and drop the algorithms in the respective section.
0
 
gingeraAuthor Commented:
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.
0
 
gingeraAuthor Commented:
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)!
0
 
gingeraAuthor Commented:
> Instead, I have chosen to just retrieve data within a time frame, and choose the smallest date.

For clarity... by MySQL query.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.