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,836 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
 
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Wordpress syntax error 1 28
MySQL database data submission 7 39
mysql left join sentence 7 22
SQL inner join confusion 15 21
Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

758 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now