Page 2 of 2
Posted: Thu Mar 08, 2007 2:56 pm
by transfield
No, this is not helpful. As I mentioned earlier,
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. This is a looping problem & not a median problem.
Posted: Thu Mar 08, 2007 3:43 pm
by RobertGonzalez
It sounds like you want an average, which the MySQL AVG() function will give you.
Posted: Thu Mar 08, 2007 3:58 pm
by transfield
Everah wrote:It sounds like you want an average, which the MySQL AVG() function will give you.
No, I don't want average. I want median. There is no formula / code for median in mysql so I created it myself.
As I mentioned earlier, 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. This is a looping problem & not a median problem.
Posted: Thu Mar 08, 2007 4:13 pm
by nickvd
Show us the code you are using to calculate the median... Just to confirm it is correct.
Posted: Thu Mar 08, 2007 4:55 pm
by transfield
nickvd wrote:Show us the code you are using to calculate the median... Just to confirm it is correct.
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>
This is not a median calculation problem. This is a looping problem. There is no need to dispute the method I used to calculate the median because I've already tested the results against the median calculation formula in MS Excel. The results were exactly the same. Average & median are 2 different methods of calculation altogether.
Posted: Thu Mar 08, 2007 5:00 pm
by RobertGonzalez
Ok, I think I see what is happening. Give me a second... I am looking at the original code.
Posted: Thu Mar 08, 2007 5:42 pm
by RobertGonzalez
You problem is in your query that is executing inside the $i loop. The query never changes in the inner loop so you are forever getting the same median value.
Code: Select all
<?php
// Query limiter
$limit = "LIMIT 0,100000";
// Connect to the server
mysql_connect ($host, $username, $password) or die('Cannot connect: ' . mysql_error());
// Select our database
mysql_select_db($database) or die('Where\'s the database man?');
// Is the Submit button set?
if (isset($_POST['Submit'])) {
// Create an array of femail data sent by the form, using explode
$vehicle_models = explode("\n", str_replace("\r", "", $_POST['femail']));
$vehicle_models_count = count($vehicle_models);
// Create an array of SQL commands
$vehicle_models_temp = array();
// Lets loop the femails array, using the values of each member as part of the query
foreach ($vehicle_models as $v) {
$vehicle_models_temp[] = "full_add LIKE '%" . mysql_escape_string($v) . "%'";
}
// Turn the previously created array into a single string
$vehicle_models_query = implode(' OR ', $vehicle_models_temp);
// Loop something, though I am not sure what?
// Ok, just figured out it is tables for the years 2003 to 2006
for ($j = 3; $j <= 6; $j++)
{
// Quickly spit out to the screen what table we are in
echo '<b>Handling data for table0'.$j.'</b><br>';
// Loop the vehicle_model array
for ($i = 0; $i < $vehicle_models_count; $i++)
{
// What are we working on?
echo '<b>Handling routines for model ' . $vehicle_models[$i] . '</b><br />';
// Run a query using the presets we have put together so far.
$query = "SELECT price FROM table0$j WHERE $vehicle_models_query ORDER BY price DESC $limit";
// And for good measure, spit out the query
/*
I THINK THIS IS WHERE THINGS ARE GOING WRONG BECUASE FOR EACH
ITERATION OF THIS LOOP YOUR QUERY IS NOT CHANGING
*/
echo '<p>About to run this: ' . $query . ' ...</p>';
// There really should be a die here, but whatever...
$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 */
// Create an array to hold row information
$price_array = array();
// Loop the result of the most recent query
while ( $row = mysql_fetch_array($result, MYSQL_NUM) ) {
// append the row information array with the 0th field of the result for this query
$price_array[] = $row[0];
}
// How big is our row array?
$num0=count($thearray);
// If the count of data is not divisible by two
if ($num0 % 2) {
$median = $thearray[floor($num0+1)/2 - 1];
} else {
// Else the count is divisible by 2
$median = ($thearray[$num0/2] + $thearray[$num0/2 - 1]) / 2;
}
/* The median calculation ended above */
echo $vehicle_models[$i].' - $'.$median.'<br>';
/*
IN THIS LOOP THERE IS NEVER ANYTHING IN THE CALCULATION THAT RELATES TO $vehicle_models[$i]...
THE WAY THIS LOOP IS RUN THEN MEANS THAT EVERY LOOP IN THE $i LOOP DOES NOTHING
TO INCREMENT THE MEDIAN CALCULATION
*/
}
}
}
?>
Posted: Thu Mar 08, 2007 9:31 pm
by transfield
You problem is in your query that is executing inside the $i loop. The query never changes in the inner loop so you are forever getting the same median value.
You are re-stating the obvious without providing a solution. Your code looks similar to mine but it performs worse than mine. Using your code, the median calculation is $0 all the time.
Could you show me the code that solves the problem?
Posted: Fri Mar 09, 2007 3:19 am
by mikeq
transfield wrote:You problem is in your query that is executing inside the $i loop. The query never changes in the inner loop so you are forever getting the same median value.
You are re-stating the obvious without providing a solution. Your code looks similar to mine but it performs worse than mine. Using your code, the median calculation is $0 all the time.
Could you show me the code that solves the problem?
Really not the response that encourages others to give up their time to help you.
Posted: Fri Mar 09, 2007 7:38 am
by Begby
transfield wrote: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.
My bad. Then use this.
Code: Select all
SELECT avg(price), full_add
FROM (
SELECT t.price, t.full_add
FROM table0$j t, table0$j x
WHERE t.full_add = x.full_add
AND $email_str
GROUP BY t.price
HAVING sum(case when t.price = x.price then 1 else 0 end) >= abs(sum(sign(t.price - x.price))
) prices
GROUP BY full_add
If it doesn't work let me know politely and constructively and if you are lucky I'll help you solve it.
(edit: adjusted the query to allow for getting the average between two medians when there are an even number of subjects)
Posted: Fri Mar 09, 2007 10:25 am
by RobertGonzalez
transfield wrote:You problem is in your query that is executing inside the $i loop. The query never changes in the inner loop so you are forever getting the same median value.
You are re-stating the obvious without providing a solution. Your code looks similar to mine but it performs worse than mine. Using your code, the median calculation is $0 all the time.
Could you show me the code that solves the problem?
While I appreciate your frustration in not being able to solve your own problems, I am not that appreciative of the tone you express in your reply. Yes, I can show you code that will solve your problem. But quite frankly, I am more of a mindset that I would rather help you solve your problems rather than solve them for you. If you want someone to fix your issue and you want to approach the community that you are seeking help from in the manner that you are approaching them, I'd suggest you retain one of us under contract so it makes it a little easier to deal with your attitude while fixing your problem.
With that, I feel I have given you significant direction in which to pursue your solution. It is not my place to continue to frustrate you, and as such, I believe that my input into this thread is no longer needed. Good luck in your pursuit of a solution to your problem.
Posted: Fri Mar 09, 2007 10:35 am
by John Cartwright
Everah wrote:transfield wrote:You problem is in your query that is executing inside the $i loop. The query never changes in the inner loop so you are forever getting the same median value.
You are re-stating the obvious without providing a solution. Your code looks similar to mine but it performs worse than mine. Using your code, the median calculation is $0 all the time.
Could you show me the code that solves the problem?
While I appreciate your frustration in not being able to solve your own problems, I am not that appreciative of the tone you express in your reply. Yes, I can show you code that will solve your problem. But quite frankly, I am more of a mindset that I would rather help you solve your problems rather than solve them for you. If you want someone to fix your issue and you want to approach the community that you are seeking help from in the manner that you are approaching them, I'd suggest you retain one of us under contract so it makes it a little easier to deal with your attitude while fixing your problem.
With that, I feel I have given you significant direction in which to pursue your solution. It is not my place to continue to frustrate you, and as such, I believe that my input into this thread is no longer needed. Good luck in your pursuit of a solution to your problem.
Pretty much summarized
the spirit of which the forum was founded. transfield, I suggest you give it a read.