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.Everah wrote: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
Do Some Calculations on Query Results
Moderator: General Moderators
-
transfield
- Forum Commoner
- Posts: 34
- Joined: Wed Feb 22, 2006 6:00 am
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
-
transfield
- Forum Commoner
- Posts: 34
- Joined: Wed Feb 22, 2006 6:00 am
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.Everah wrote:It sounds like you want an average, which the MySQL AVG() function will give you.
-
transfield
- Forum Commoner
- Posts: 34
- Joined: Wed Feb 22, 2006 6:00 am
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>- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
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
*/
}
}
}
?>-
transfield
- Forum Commoner
- Posts: 34
- Joined: Wed Feb 22, 2006 6:00 am
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?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.
Really not the response that encourages others to give up their time to help you.transfield wrote: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?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.
My bad. Then use this.transfield wrote:There is no such thing as median(price) in Mysql.Begby wrote:tada!Code: Select all
SELECT median(price) as priceMedian, full_add FROM table0$j WHERE $email_str GROUP BY full_add
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
(edit: adjusted the query to allow for getting the average between two medians when there are an even number of subjects)
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
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.transfield wrote: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?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.
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.
- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
Pretty much summarized the spirit of which the forum was founded. transfield, I suggest you give it a read.Everah wrote: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.transfield wrote: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?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.
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.