Page 1 of 1

Calculation Query & Some Looping

Posted: Tue Oct 24, 2006 7:02 am
by 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:-

1. 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. 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: Select all

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

Hmmm...

Posted: Tue Oct 24, 2006 1:17 pm
by churt
How does "$median" get updated for each date group?

Your median calculation is based off of query1 which will give a total.

While looping through the query2 results you never recalc the median.

Posted: Tue Oct 24, 2006 1:23 pm
by transfield
How does "$median" get updated for each date group?
I don't understand your question.
Your median calculation is based off of query1 which will give a total.
Yes I agree.
While looping through the query2 results you never recalc the median.
Can you show me how to do it?

Thanks a lot.

Maybe

Posted: Wed Oct 25, 2006 7:27 am
by churt
There might be a better way but this should work.

Code: Select all

<?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)){
    //Median Calculation For Each Date Group
    $query3=("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='".$row2["date"]."' order by price ASC");
    $result3=mysql_query($query3);
    $numdt=mysql_num_rows($result3);
    $ta2=array();
    while ($row3 = mysql_fetch_array($result3)){ $ta2[]=$row3[0]; }
    if (($numdt % 2)==0) {
        $mediandt = $ta2[floor($numdt/2)];
    } else {
        $mediandt = ($ta2[$numdt/2] + $ta2[$numdt/2 - 1]) / 2;
    }
    //End Median Calculation For Each Date Group
?>
<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 "$mediandt"; ?></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
}
?>

Posted: Wed Oct 25, 2006 7:33 am
by transfield
Thank you very much for your effort & guidance, Churt. I appreciate it. God bless you man :-)