Solved

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

Posted on 2008-06-11
10
1,846 Views
Last Modified: 2013-12-13
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?


0
Comment
Question by:gingera
  • 5
  • 3
  • 2
10 Comments
 
LVL 50

Expert Comment

by:Steve Bink
ID: 21763741
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
 

Author Comment

by:gingera
ID: 21763899
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
 
LVL 5

Expert Comment

by:pbeirne
ID: 21764075
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 5

Expert Comment

by:pbeirne
ID: 21771884
Actually replace the line above:

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

with:

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

0
 
LVL 50

Accepted Solution

by:
Steve Bink earned 500 total points
ID: 21773242
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
 

Author Comment

by:gingera
ID: 21775136
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
 
LVL 50

Assisted Solution

by:Steve Bink
Steve Bink earned 500 total points
ID: 21784232
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
 

Author Comment

by:gingera
ID: 21840501
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
 

Author Closing Comment

by:gingera
ID: 31466312
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
 

Author Comment

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

For clarity... by MySQL query.
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
This article discusses how to create an extensible mechanism for linked drop downs.
The viewer will learn how to dynamically set the form action using jQuery.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question