Calculation Query & Some Looping

transfield

Hello,
I've got a table called condo03. In this table there are 3 fields called date, price & full_add respectively. I want to:-

Query the full_add field using a keyword that I specify. Then group all the dates together in the date field. Then calculate the median price in the price field according to the individual date groups. As such, if there are 10 different dates in the date field, then there should be 10 groups created & 10 different median prices generated.

I've written the code below to the best of my ability & I hope that you can edit it for me. I've also attached a screen shot to show what is working & what is not working.

Thanks a lot for your help.

<?php
$query1=("SELECT price FROM condo03 WHERE (full_add like '%$Text_Box_1%' OR full_add like '%$Text_Box_2%' OR full_add like '%$Text_Box_3%' OR full_add like '%$Text_Box_4%') AND (status = '$status') AND (size >= '$Text_Box_15' AND size <= '$Text_Box_16')"); 
//$query2 is working fine.
$query2=("SELECT distinct(date), COUNT(date) AS count FROM condo03 WHERE (full_add like '%$Text_Box_1%' OR full_add like '%$Text_Box_2%' OR full_add like '%$Text_Box_3%' OR full_add like '%$Text_Box_4%') AND (status = '$status') AND (size >= '$Text_Box_15' AND size <= '$Text_Box_16') GROUP BY 'date' ASC");

$result1=mysql_query($query1);
$num1=mysql_num_rows($result1);
$result2=mysql_query($query2);
$num2=mysql_num_rows($result2);
//the median calculation starts here. The code works fine.
$thearray=array(); 
while ( $row=mysql_fetch_array($result1,MYSQL_NUM) ) { 
$thearray[]=$row[0]; 
}

$num0=count($thearray); 
if ($num0 % 2) { 
$median = $thearray[floor($num0/2)]; 
} else { 
$median = ($thearray[$num0/2] + $thearray[$num0/2 - 1]) / 2; 
} 
//The median calculation ends here.
while ($row2 = mysql_fetch_assoc($result2)){
?>
<table border="1" cellspacing="2" cellpadding="2">
<tr>
<td style="border-style: solid; border-width: 1" bgcolor="#CCFFFF" align="left" width="165"><b><div align="center"><font size="4" face="Arial, Helvetica, sans-serif"><? echo "$row2[date]"; ?></font></div></td>

<td style="border-style: solid; border-width: 1" bgcolor="#CCFFFF" align="left" width="165"><b><div align="center"><font size="4" face="Arial, Helvetica, sans-serif"><? echo "$median"; ?></font></div></td>

<td style="border-style: solid; border-width: 1" bgcolor="#CCFFFF" align="left" width="165"><b><div align="center"><font size="4" face="Arial, Helvetica, sans-serif"><? echo "$row2[count]"; ?></font></div></td>
</tr>
</table>
<?php
}
?>

bpat1434

You calculate the median for all the rows, not just the rows in that specific year. Your query needs to be limited by the year that you're currently looking at. So as you loop through $result2 to show the results, you need to execute query 1 with the addition of another part of the WHERE clause stating: year='$row[year]';

Then calculate the median.

Also, you're median calculation is backwards. If it's an even number of units (10) then the median is not 5, but rather 5+6/2. Proof:

NOTE: I did not use 1 - 10, but rather 0-9. So your projected median is 4.

0 1 2 3 4 5 6 7 8 9
You say the number in bold is the median, when in fact it's not. The number in bold has 4 on the left, and 5 on the right. So you need to get the mean of 4 & 5.
0 1 2 3 4 4.5 5 6 7 8 9

0 1 2 3 4 5 6 7 8
With an odd number of units, you just floor the number /2 (floor($num/2)). So 9 = 4, 15 = 7 etc.

transfield

Thanks for your reply, bpat. I'm not sure what to do. I've edited $query1 to limit it to year 2005 like this:-

$query1=("SELECT price FROM condo03 WHERE (full_add like '%$Text_Box_1%' OR full_add like '%$Text_Box_2%' OR full_add like '%$Text_Box_3%' OR full_add like '%$Text_Box_4%') AND (status = '$status') AND (size >= '$Text_Box_15' AND size <= '$Text_Box_16') AND (date = '2005')");

I've not edited any other part of the code. When I ran the code this time, it calculated the median for year 2005 and displayed it in all the rows. It's supposed to display the median for year 2005 only in the 2005 row.

So how do I correct this?

I've attached a screen shot to show you how it's behaving now.

Thanks.

bpat1434

This is an outline of what is supposed to happen:

1.) User reaches page.
2.) User submits search form (I guess)
3.) Query 2 is executed with the form submission
4.) Loop through Query 2 results (years and number of rows)
4a.) During this loop, execute Query1 replacing the year for the current year as returned by Query 2
4b.) Loop through Query 1 results adding up the prices
5.) Display as needed

Can you now see where your issue is? You need to execute Query1 inside of your Query2 loop...