Solved

MYSQL PHP: How do I calculate percentage change from mysql table?

Posted on 2008-06-11
14
2,261 Views
Last Modified: 2013-12-13
MYSQL PHP

Hello,

I have a MySQL table with 2 fields:

[ Date ] [ Value ]
2008-06-11 2200
...
2008-05-11 2000


I would like to calculate the percentage increase or decrease as at the last date (e.g. 2007-12-31), when compared to 1 month ago.

So in the example, I want to calculate (2200-2000) / 2000

How do I write this in a mysql query in a PHP script?


Thanks.
0
Comment
Question by:gingera
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 6
14 Comments
 
LVL 9

Accepted Solution

by:
Rob Siklos earned 500 total points
ID: 21762713
Assuming there is only one record for each day, you should be able to do something like this:
$date = "2008-06-11";
 
$sql = "SELECT (t1.value - t2.value)/t2.value AS result
FROM table t1
LEFT JOIN table t2
ON t2.`date` = DATE_SUB(t1.`date`, INTERVAL 1 MONTH)
WHERE t1.`date` = '$date'";

Open in new window

0
 

Author Comment

by:gingera
ID: 21763029
Thanks Rob. Hmmm... I yielded no result with your query.

i.e. when I

echo "$result";

nothing appeared.
0
 
LVL 9

Expert Comment

by:Rob Siklos
ID: 21763071
I just tested it and it worked for me.  Can you post the PHP code you're using to execute the query and print the result?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 9

Expert Comment

by:Rob Siklos
ID: 21763082
Oh, also, for robustness I would change the LEFT JOIN to INNER JOIN
0
 

Author Comment

by:gingera
ID: 21763169
OK this is the code I tried

Table name: rice

Table fields: date, close
<?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
 

Author Comment

by:gingera
ID: 21763187
I got error message

PHP Warning:  mysql_fetch_array(): supplied argument is not a valid MySQL result resource
0
 
LVL 9

Expert Comment

by:Rob Siklos
ID: 21763201
before "extract($row);" can you add "print_r($row);" and see what gets printed?
0
 

Author Comment

by:gingera
ID: 21763260
OK, did that. Nothing displayed.
0
 
LVL 9

Expert Comment

by:Rob Siklos
ID: 21763263
Seems like there's something wrong with the result of the call to mysql_query().

After that line, try adding this:

if (!$result_percentage_change) {
    $message  = 'Invalid query: ' . mysql_error() . "\n";
    die($message);
}
0
 

Author Comment

by:gingera
ID: 21763294
OK I have a possible explanation.

Maybe there is no data for the date exactly 1 month before. For example, a weekend?

If that is the case, how can we find the next closes date to 1 month (if possible)?
0
 

Author Comment

by:gingera
ID: 21763340
OK I changed "interval 1 month" to "interval 2 month" and a result is displayed.

So the problem is as I have described above.

How do I solve this problem (no data for exactly 1 month ago)?
0
 
LVL 9

Expert Comment

by:Rob Siklos
ID: 21763355
Ok, that is a different problem for sure.

At first thought, I would say that you need a routine in PHP to select the date you want to compare to.
0
 

Author Comment

by:gingera
ID: 21763376
OK I am closing this question and allocating the points to you Rob. Thanks very much for your help.

I will start another question. THANKS!
0
 

Author Closing Comment

by:gingera
ID: 31466259
Thanks for your patience for this newbie.
0

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MySQL 6 48
Code not executing correctly. 3 44
htaccess add www, redirect to https, keep query string 17 58
Developers / Staff Setup 10 46
This article discusses how to implement server side field validation and display customized error messages to the client.
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
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 look for a specific file type in a local or remote server directory using PHP.

738 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