Do Some Calculations on Query Results

transfield · 03-04-2007, 08:15 AM

Hello,
I have got 4 tables in the same database. The name of the tables are table03, table04, table05 & table06. The structure of the tables are exactly the same. I want to calculate the median price of the price column in each table individually based on the keywords I keyed in. The keywords will query the full_add column.

So assuming that my keywords were Toyota, Honda, Mercedes and BMW(keyed in as 1 keyword per line in the form), the end result should display something like this:-
Median Price from table03
Toyota - $3883
Honda - $356
Mercedes - $7865
BMW - $1347

Median Price from table04
Toyota - $7688
Honda - $4312
Mercedes - $8709
BMW - $1254

Median Price from table05
Toyota - $8776
Honda - $3445
Mercedes - $8778
BMW - $1223

Median Price from table06
Toyota - $6578
Honda - $5445
Mercedes - $545
BMW - $12548

What I've already done succesfully is to query the database based on 1 record per line. My code is below.

The part that I do not know how to code is as follows:-
1. A join query to query all tables at one go.
2. To calculate the median 4 times(once for each table) & display the results like the sample above.

Please show me how to write the code. Thank you for your help.

PHP Code:


			
<?php 

$limit = "LIMIT 0,100000"; 

mysql_connect ("$host","$username","$password"); 

mysql_select_db($database) or die( "Where's the database man?"); 



if(isset($_POST['Submit'])){ 

$emails=explode("\n", str_replace("\r", "", $_POST['femail'])); 

$email_r=array(); 

foreach($emails as $e){ 

$email_r[]="full_add LIKE '%".mysql_escape_string($e)."%'"; 

} 

$email_str=implode(' OR ',$email_r); 



$query1="SELECT price FROM table WHERE ".$email_str . $limit; 

$result1=mysql_query($query1); 

} 



/* Median calculation for the price. */ 

$thearray=array(); 

while ( $row=@mysql_fetch_array($result1,MYSQL_NUM) ) { 

$thearray[]=$row[0]; 

} 

$num0=count($thearray); 

if ($num0 % 2) { 

$median1 = $thearray[floor($num0+1)/2 - 1]; 

} else { 

$median1 = ($thearray[$num0/2] + $thearray[$num0/2 - 1]) / 2; 

} 

?> 



<html> 

<head> 

<title>Test</title> 

</head> 

<body> 

<form name="ftest" action="<?= $_SERVER['PHP_SELF']; ?>" method="post"> 

<textarea name="femail"></textarea><br /> 

<input type="submit" name="Submit" value="Send" /> 

</form> 

<table width=700 border=1 height="0" style="border-collapse: collapse" bordercolor="#111111" cellpadding="0" cellspacing="0"> 

<tr> 

<td style="border-style: solid; border-width: 1" bgcolor="#99CCCC" align="left" width="350"><b> 

<div align="center"><font size="3" face="Arial, Helvetica, sans-serif"><? echo "Median Price - RM<font color='#FF33cc'> $median1 </font>"; ?></font></div></td> 

</tr> 

</table> 

</body> 

</html>

Additional Info:-
My table structure for all tables are as follows:-

Code:

`id` int(6) NOT NULL auto_increment,
`date` year(4) NOT NULL default '0000',
`full_add` varchar(255) NOT NULL default '',
`status` varchar(10) NOT NULL default '',
`size` int(6) default '0',
`price` int(8) default '0',
`price_psf` double NOT NULL default '0',
PRIMARY KEY (`id`),
UNIQUE KEY `full_add` (`full_add`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1

teknomage1 · 03-04-2007, 03:04 PM

What's the point of having 4 tables with the exact same structure? If they represent different things, wouldn't it be better to just add 1 additional field that desribed what differentiates each set of rows?

transfield · 03-04-2007, 06:09 PM

Quote:

What's the point of having 4 tables with the exact same structure?

Good question & thank you for asking. When I initially created everthing, the database sizes where so huge that I broke them down into 4 different tables representing 4 different years.

At this stage, I've got everything working except the median calculation is not looping through the search results. Therefore, instead of getting a result like
Median Price from table03
Toyota - $3883
Honda - $356
Mercedes - $7865
BMW - $1347

I am getting a result like

Median Price from table03
Toyota - $3883
Honda - $3883
Mercedes - $3883
BMW - $3883

My code is below & I trust that you can edit it for me. Thanks.

PHP Code:


			
<?php

$limit = "LIMIT 0,100000";

mysql_connect ("$host","$username","$password");

mysql_select_db($database) or die( "Where's the database man?");



if(isset($_POST['Submit'])){

$emails=explode("\n", str_replace("\r", "", $_POST['femail']));



$email_r=array();

foreach($emails as $e){

$email_r[]="full_add LIKE '%".mysql_escape_string($e)."%'";

 }

$email_str=implode(' OR ',$email_r);



for($j = 3; $j <= 6; $j++) 

{ 

     echo '<b>Median Price from table0'.$j.'</b><br>'; 

     for($i = 0; $i < sizeof($emails); $i++) 

     { 

          $query = "SELECT price FROM table0$j WHERE $email_str ORDER BY price DESC $limit"; 

          $result = mysql_query($query); 

/* The median calculation starts here. It's not looping through the 

search results & I don't know how to solve this. Please help me. */

$thearray=array(); 

while ( $row=@mysql_fetch_array($result,MYSQL_NUM) ) { 

$thearray[]=$row[0]; 

}

$num0=count($thearray); 

if ($num0 % 2) { 

$median = $thearray[floor($num0+1)/2 - 1]; 

} else { 

$median = ($thearray[$num0/2] + $thearray[$num0/2 - 1]) / 2; 

} 

/* The median calculation ends here. */    

   echo $emails[$i].' - $'.$median.'<br>';

       }

} 

}

?>

 <form name="ftest" action="<?= $_SERVER['PHP_SELF']; ?>" method="post">

  <textarea name="femail"></textarea><br />

  <input type="submit" name="Submit" value="Send" />

 </form>

teknomage1 · 03-04-2007, 08:06 PM

Are you sure that's necessary, I have a 2 million plus row table I work with daily that does fine. It might be easier just to add a year column and construct one big table.

Regardless, It looks like you need an additional for loop to iterate over the keyword types OR make a number of arrays for each type and select the price and type (although from your table and code I can't tell which field is supposed to contain the keyword).

transfield · 03-04-2007, 08:11 PM

Quote:

although from your table and code I can't tell which field is supposed to contain the keyword

It's the full_add field.

Quote:

It looks like you need an additional for loop to iterate over the keyword types OR make a number of arrays for each type and select the price and type

Could you show me how to write the code?

Quote:

It might be easier just to add a year column and construct one big table

I still would not know how to write the code anyway so it makes no difference to me.

Thanks for your reply.

DJMaze · 03-05-2007, 03:12 AM

Code:

SELECT SUM(price)/COUNT(*) FROM table1 WHERE full_add LIKE '%%' GROUP BY full_add
UNION SELECT SUM(price)/COUNT(*) FROM table2 WHERE full_add LIKE '%%' GROUP BY full_add

transfield · 03-05-2007, 03:22 AM

Hi DJMaze,
This is a formula for average & not median. Anyway, I'm not asking for a formula for median. I'm asking why the existing median calculation is not looping through the search results.

Thank you for your initiative.

teknomage1 · 03-05-2007, 10:25 AM

instead of that foreach loop where you add everthing in LIKE statements with OR separating them, place your query loop in the body of that foreach.

transfield · 03-06-2007, 02:02 AM

Quote:

Originally Posted by teknomage1

instead of that foreach loop where you add everthing in LIKE statements with OR separating them, place your query loop in the body of that foreach.

Could you show me how to write the code please?

teknomage1 · 03-06-2007, 12:07 PM

You might need to fill in some details, like define the myMedian function but this is the general idea.

PHP Code:


			
foreach($emails as $e){

     $email_str="full_add LIKE '%".mysql_escape_string($e)."%'";

     for($j = 3; $j <= 6; $j++) 

     { 

      echo '<b>Median Price from table0'.$j.'</b><br>'; 

      $query = "SELECT price FROM table0$j WHERE $email_str ORDER BY price DESC $limit"; 

           $result = mysql_query($query); 

           /* The median calculation starts here. */

           $resultArray=array(); 

           while ( $row=@mysql_fetch_array($result,MYSQL_NUM) ) { 

            $resultArray[]=$row[0]; 

           }

           $median = myMedian($resultArray);

           /* The median calculation ends here. */    

           echo $emails[$i].' - $'.$median.'<br>';

     }

}

Basically, your code was getting every price that matched any of your input words and then it couldn't differentiate between them after you calculated the median. This code fetches the price for each word and calculates their median individually.