sql php Get my head around it!

mladams

I am trying to figure out how to do this.

I am trying to pull info from a database in php, see DB attachment.

As you will see, there are 3 main columns
price_listing | price_date | price_value

What I am trying to achieve is...

The values in price_value are daily prices in £´s

Select a value from price_value and when this changes output the start date and and to date and divided the price_value by 7, making the result, prices per week.

i.e. In the attached DB image you will see price_listing (unique property id) the value 123 and this starts from the 30th June to the 4th July. It then changes from the 5th July to the 19th July to 157 and so on through the year.

What I would like is the output like this...

30 Jun - 4th Jul £861.00
5th July - 18th July £1099.00
19th July - 29th July £1400.00

This will take the start and end date from price_date where price_value is the same.

Anyone have any ideas how to achieve this.

Thanks in advance.

niroshan

Hi mladams,

Can price_value have previous price at a later date?

ex:

2010-6-30 the price_value is 123. Is it possible to have 123 on a later date like 2010-7-30?

if not you can use group by,

SELECT max(price_date),min(price_date), price_value FROM listing_price group by price_value

Thanks,
regards,
niroshan

mladams

Hi niroshan

Yes, prices throughout the year will rise from January, peek in August, then fall off to December.

So November and December could be the same as Jan & Feb.

niroshan

hi mladams,

since prices can be same sometimes i`m afraid the previous sql statement i have given wont work or I dont think we can write one statement to do it. (some sql expert might 😃)

since i`m not an sql expert i have written it from what i know best 😃


$sql = "SELECT price_date, price_value FROM listing_price ORDER BY price_date";
$row = mysql_query($sql);
$i = 0;
$oldPrice = '';
while($result = mysql_fetch_array($row))
{
	$price = $result['price_value'];

if($oldPrice == '')
{
	$dateRanges[$i]['start'] = $result['price_date'];
	$dateRanges[$i]['price'] = $price;

	$oldPrice = $price;
}
else
{
	if($oldPrice <> $price)
	{
		$dateRanges[$i]['end'] = $prvDate;

		$i++;

		$dateRanges[$i]['start'] = $result['price_date'];
		$dateRanges[$i]['price'] = $price;

		$oldPrice = $price;
	}
}

$prvDate = $result['price_date'];
}

print '<pre>';
print_r($dateRanges);
print '</pre>';

Hope this would help.

Thanks,
best regards,
niroshan