#### Archived

This topic is now archived and is closed to further replies.

# Calculation Query & Some Looping

## Recommended Posts

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

1. Query the [B]full_add[/B] field using a keyword that I specify. Then group all the dates together in the [B]date[/B] field. Then calculate the median price in the [B]price[/B] 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. The part that is not working is the calculation of the median price according to individual date groups. At present it is calculating the median in total regardless of the number of date groups.

Thanks a lot for your help.
[code=php:0]<?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)){
?>
<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
}
?>[/code]

##### Share on other sites
Can you not use MySQL's [b]MID()[/b] function for this?

Regards
Huggie