asked on

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 https://www.experts-exchange.com/questions/23476744/MYSQL-PHP-How-do-I-calculate-percentage-change-from-mysql-table.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?

Steve Bink

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.

gingera

ASKER

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

pbeirne

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);
}
.
.
.

pbeirne

Actually replace the line above:

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

with:

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

ASKER CERTIFIED SOLUTION

Steve Bink

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

gingera

ASKER

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.

SOLUTION

Steve Bink

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

gingera

ASKER

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.

gingera

ASKER

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)!

gingera

ASKER

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

For clarity... by MySQL query.