Do Some Calculations on Query Results

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

transfield
Forum Commoner
Posts: 34
Joined: Wed Feb 22, 2006 6:00 am

Do Some Calculations on Query Results

Post 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>
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post 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;
}
Last edited by Jenk on Tue Mar 06, 2007 5:00 am, edited 1 time in total.
transfield
Forum Commoner
Posts: 34
Joined: Wed Feb 22, 2006 6:00 am

Post by transfield »

Hi Jenk,
How do I place each value into an array & where do I insert your code?

Thanks.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

This seems to beg the question why are there multiple tables with the exact same structure?
transfield
Forum Commoner
Posts: 34
Joined: Wed Feb 22, 2006 6:00 am

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Those are pretty small table sizes. It shouldn't be complicated to merge them back together.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
transfield
Forum Commoner
Posts: 34
Joined: Wed Feb 22, 2006 6:00 am

Post 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?
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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).
transfield
Forum Commoner
Posts: 34
Joined: Wed Feb 22, 2006 6:00 am

Post 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.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

So you want the median (the middle value of all values in ascending order). Is that right?
transfield
Forum Commoner
Posts: 34
Joined: Wed Feb 22, 2006 6:00 am

Post 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.
Begby
Forum Regular
Posts: 575
Joined: Wed Dec 13, 2006 10:28 am

Post by Begby »

Code: Select all

SELECT median(price) as priceMedian, full_add
FROM table0$j 
WHERE $email_str
GROUP BY full_add
tada!
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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
transfield
Forum Commoner
Posts: 34
Joined: Wed Feb 22, 2006 6:00 am

Post 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.
Post Reply