Page 1 of 2

Do Some Calculations on Query Results

Posted: Mon Mar 05, 2007 11:07 am
by transfield
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

The part that I do not know how to code & require your help is 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

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

Code: Select all

<?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 below. While it is working fine,
it is not looping through the search results. This is where i need help */
$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 ended above */	
   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>

Posted: Mon Mar 05, 2007 11:19 am
by Jenk
Place each value into an array, then:

Code: Select all

$meanAverage = (array_sum($array) / count($array));
sort($array);
if (count($array) % 2 = 1)
{
    $medianAverage = $array[(count($array) + 1) / 2];
}
else
{
    $valA = floor(count($array) / 2);
    $valB = ceil(count($array) / 2);
    $medianAverage = ($array[$valA] + $array[$valB]) / 2;
}

Posted: Tue Mar 06, 2007 4:58 am
by transfield
Hi Jenk,
How do I place each value into an array & where do I insert your code?

Thanks.

Posted: Tue Mar 06, 2007 8:51 am
by feyd
This seems to beg the question why are there multiple tables with the exact same structure?

Posted: Tue Mar 06, 2007 6:34 pm
by transfield
feyd wrote:This seems to beg the question why are there multiple tables with the exact same structure?
Good question you asked. Thank you. The reason is because the database size is very big. It is approximately 35 Mb x 4 tables = 140 Mb. I was having difficulty editing it offline & uploading it into my server so I decided to break it up into 4 tables. Each table now represents a different year. table03 - represents year 2003, table04 - represents year 2004, table05 - represents year 2005 & table06 - represents year 2006.

Posted: Wed Mar 07, 2007 11:26 am
by feyd
Those are pretty small table sizes. It shouldn't be complicated to merge them back together.

Posted: Wed Mar 07, 2007 7:11 pm
by RobertGonzalez
transfield wrote:
feyd wrote:This seems to beg the question why are there multiple tables with the exact same structure?
Good question you asked. Thank you. The reason is because the database size is very big. It is approximately 35 Mb x 4 tables = 140 Mb. I was having difficulty editing it offline & uploading it into my server so I decided to break it up into 4 tables. Each table now represents a different year. table03 - represents year 2003, table04 - represents year 2004, table05 - represents year 2005 & table06 - represents year 2006.
Instead of editing them offline you could always work on them locally then synchronize the two databases when finished.

Posted: Wed Mar 07, 2007 10:14 pm
by transfield
Instead of editing them offline you could always work on them locally then synchronize the two databases when finished.
I suppose that would be possible but it does not solve my problem anyway. I still would not know how to write the code. My question is, how do I write the code?

Posted: Thu Mar 08, 2007 10:58 am
by RobertGonzalez
All of the data you are calculating from is coming from the DB, so do your calculations there. Post back in plain simple English what you want the result to be and which fields you are wanting to use to achieve it and I'd bet one of us comes up with the query to help you (if you don't come up with it yourself when writing out your question).

Posted: Thu Mar 08, 2007 11:13 am
by transfield
Post back in plain simple English what you want the result to be
The part that I do not know how to code & require your help is 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
and which fields you are wanting to use to achieve it
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.

Posted: Thu Mar 08, 2007 11:37 am
by RobertGonzalez
So you want the median (the middle value of all values in ascending order). Is that right?

Posted: Thu Mar 08, 2007 12:29 pm
by transfield
Everah wrote:So you want the median (the middle value of all values in ascending order). Is that right?
No. I want the calculate the median price of each keyword individually. Let me explain the big picture here.

I'm analysing the price trends of various cars on a yearly basis. Each table(table03, table06, etc) represents a particular year. The keywords that I'm keying into the form are the names of the cars(BMW, Toyota, etc). By tracking the median price of a particular car on a yearly basis, I will know whether the market value of the car is appreciating or depreciating.

The method of calculating the median in my code is working correctly. All I need is to calculate the median of each keyword(each car) individually.

Posted: Thu Mar 08, 2007 2:22 pm
by Begby

Code: Select all

SELECT median(price) as priceMedian, full_add
FROM table0$j 
WHERE $email_str
GROUP BY full_add
tada!

Posted: Thu Mar 08, 2007 2:37 pm
by RobertGonzalez
Median is not average, mean is average. Median is the middle value in a sequence. Sorry for my confusion with your question. Here are some helpful MySQL manual pages that could help you out...

MySQL Reference Index
MySQL Group By (Aggregate) Functions

Posted: Thu Mar 08, 2007 2:50 pm
by transfield
Begby wrote:

Code: Select all

SELECT median(price) as priceMedian, full_add
FROM table0$j 
WHERE $email_str
GROUP BY full_add
tada!
There is no such thing as median(price) in Mysql.