Do Some Calculations on Query Results

@transfieldMar 04.2007

Hello,
I have got 4 tables in the same database. The name of the tables are ~~[B]~~table03[/B], ~~[B]~~table04[/B], ~~[B]~~table05 [/B]& ~~[B]~~table06[/B]. The structure of the tables are exactly the same. I want to calculate the median price of the ~~[B]~~price[/B] column in each table individually based on the keywords I keyed in. The keywords will query the ~~[B]~~full_add[/B] 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:-
~~[B]~~Median Price from table03[/B]
Toyota – $3883
Honda – $356
Mercedes – $7865
BMW – $1347

~~[B]~~Median Price from table04[/B]
Toyota – $7688
Honda – $4312
Mercedes – $8709
BMW – $1254

~~[B]~~Median Price from table05[/B]
Toyota – $8776
Honda – $3445
Mercedes – $8778
BMW – $1223

~~[B]~~Median Price from table06[/B]
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.

[code=php]<?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>[/code]

to post a comment

PHP

14 Comments(s) _↴

@michael879Mar 04.2007 — #whats your table structure and what exactly do you need help doing?

@transfieldauthorMar 04.2007 — #My table structure for all tables are as follows:-

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=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1

The help that I need is to query the tables & display the results as per my earlier example.

Please feel free to ask further questions.

@michael879Mar 04.2007 — #

[code=php]
 for($j = 3; $j <= 6; $j++)
 {
 echo '<b>Media Price from table0'.$j.'</b>';
 for($i = 0; $i < sizeof($array); $i++)
 {
 $query = 'SELECT price FROM table0'.$j.' WHERE full_add='.$array[$i];
 $result = mysql_query($query);
 $prices = array();
 while($row = mysql_fetch_row($result))
 {
 $prices[] = $row[0];
 }
 $prices = sort($prices);
 $middle = sizeof($prices)/2;
 if(floor($middle) != $middle)
 {
 $middle1 = floor($middle);
 $middle2 = ceil($middle);
 $median = ($prices[$middle1]+$prices[$middle2])/2;
 }
 else
 {
 $median = $prices[$middle];
 }
 echo $array[$i].' - $'.$median.'<br>;
 }
 }
 [/code]

where $array is an array of the keywords.

@transfieldauthorMar 04.2007 — #Thanks for your reply & your code, michael879. Could you please tell me how to incorporate your code into mine? Sorry, I'm new to php & I don't understand what I'm looking at here.

I appreciate your help on this matter.

@michael879Mar 04.2007 — #youd make an array for all the keywords you want (i.e.

$array = array('Toyota','Honda','Mercedes','BMW')?

Then youd paste my code below that, and it will print out what you want.

@transfieldauthorMar 04.2007 — #Thanks for your reply, Michael. Okay, I incorporated your code into mine. Only 2 things are not working now which is:-

1. The new median calculation. I have therefore reverted back to the old median calculation which works fine.

2. The median calculation is not looping through the search results. Therefore, instead of getting a result like

[B]Median Price from table03[/B]

Toyota - $3883

Honda - $356

Mercedes - $7865

BMW - $1347

I am getting a result like

[B]Median Price from table03[/B]

Toyota - $3883

Honda - $3883

Mercedes - $3883

BMW - $3883

My final code is below. I would be grateful if you could edit it for me. Thanks.

[code=php]<?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);
 //your code starts here
 for($j = 3; $j <= 6; $j++) 
 { 
 echo '<b>Median Price from table0'.$j.'</b>'; 
 for($i = 0; $i < sizeof($emails); $i++) 
 { 
 //I edited the query slightly 

 $query = "SELECT price FROM table0$j WHERE $email_str $limit"; 
 $result = mysql_query($query); 
 /*the old median calculation works fine but is not looping
 through the search results*/
 $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>[/code]

@michael879Mar 04.2007 — #dude just do what I said and it should work. Make the array, and paste in my code under it. Im not sure how u calculated the median but Im pretty sure my way will work.

@transfieldauthorMar 05.2007 — #Make the array, and paste in my code under it[/QUOTE]If you observe my code, I've already done that. The array is called $emails.

Im not sure how u calculated the median but Im pretty sure my way will work[/QUOTE]The fact is your way did not work(I was getting $0 for every keyword) which is why I reverted back to my old method.

Nevertheless, now the only outstanding issue is the median calculation is not looping through the search results.

I thank you for your patience & trust that you can help me.

@michael879Mar 05.2007 — #ah sorry, I didnt see the scroll bar I guess, the code looked identical to your other post.

The reason your getting this bug is because you left your query string outside of the nested for loops. Theres should be 1 query for every keyword in emails. In other words, $email_str should be inside the loop, and should be equal to:

'full_ad = '.emails[$j];

@transfieldauthorMar 05.2007 — #Thanks for your reply. I'm not sure I understood your instructions properly. I tried placing the $email_str inside the FOR loop but I'm getting another error mesage. Please look at my edited code below. What am I doing wrong?

[code=php]<?php
 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)."%'";
 }
 //The old $email_str is still here. Am I supposed to delete it?
 $email_str=implode(' OR ',$email_r);
 
 for($j = 3; $j <= 6; $j++) 
 { 
 //The new $email_str is here. I'm getting an error message on the line below.
 $email_str='full_ad = '.emails[$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); 
 
 $thearray=array(); 
 while ( $row=@mysql_fetch_array($result,MYSQL_NUM) ) { 
 $thearray[]=$row[0]; // Similar to array push 
 }
 $num0=count($thearray); 
 if ($num0 % 2) { 
 $median = $thearray[floor($num0+1)/2 - 1]; 
 } else { 
 $median = ($thearray[$num0/2] + $thearray[$num0/2 - 1]) / 2; 
 } 
 
 echo $emails[$i].' - $'.$median.'<br>';
 }
 } 
 }
 echo $query;
 ?>[/code]

@michael879Mar 05.2007 — #whats your error message? Try getting rid of the $limit in the query string.

@transfieldauthorMar 05.2007 — #Parse error: syntax error, unexpected '[' in /home/.../public_html/test_line_query1.php on line 34

Line 34 is the line that contains the $email_str='full_ad = '.emails[$j];

Removing the $limit did not help.

@michael879Mar 05.2007 — #lol you wrote emails[$j], it should be $emails[$j]

@transfieldauthorMar 06.2007 — #Okay, I fixed that. The error message has disappeared but the median calculation is showing $0.

Also in #PHP _↴

few database q's Internal communication system for website's members insert array into database

Success!

Help @transfield spread the word by sharing this article on Twitter...

Tweet This

Do Some Calculations on Query Results

14 Comments(s) _↴

Also in #PHP _↴

Success!

Social

Version

Do Some Calculations on Query Results

14 Comments(s) ↴

Also in #PHP ↴

Success!

The web is an endless sea of information. Don't miss the boat... Subscribe!

Social

Version

14 Comments(s) _↴

Also in #PHP _↴