Go Premium for a chance to win a PS4. Enter to Win

x
?
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
Medium Priority
?
1,900 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 51

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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
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 51

Accepted Solution

by:
Steve Bink earned 2000 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 51

Assisted Solution

by:Steve Bink
Steve Bink earned 2000 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

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
The viewer will learn how to count occurrences of each item in an array.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

885 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