asked on

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

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.

ASKER CERTIFIED SOLUTION

Rob Siklos

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

Thanks Rob. Hmmm... I yielded no result with your query.

i.e. when I

echo "$result";

nothing appeared.

Rob Siklos

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?

Rob Siklos

Oh, also, for robustness I would change the LEFT JOIN to INNER JOIN

gingera

ASKER

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

gingera

ASKER

I got error message

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

Rob Siklos

before "extract($row);" can you add "print_r($row);" and see what gets printed?

gingera

ASKER

OK, did that. Nothing displayed.

Rob Siklos

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

gingera

ASKER

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

gingera

ASKER

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

Rob Siklos

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.

gingera

ASKER

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!

gingera

ASKER

Thanks for your patience for this newbie.