Getting different results when using a MySQL query array, compared to a PHP array

Question

I'm writing a script that works out the mode, median and mean of a set of data. I have ten records which are stored in a MySQL database and in a PHP array. When the script is finished, the script will only use the data stored in the database and not the PHP array, the PHP array is there to test the code.

What I have found though is that the results are different when using the PHP array compared to the MySQL array. In the PHP array, I have the following data:

$arr = array(60, 70, 71, 76, 144, 151, 197, 229, 233, 233);

In my MySQL database, I have this code:

$data = mysql_query("SELECT amount FROM example") or die(mysql_error()); 
$arr = mysql_fetch_array($data);

The data is the same, the exception being that the MySQL data contains .00 after each record. During the time spent trying to find out why it was giving the wrong results, I removed the .00 from each record in the database. The data is still wrong though.

The results, when using the PHP array gives me the following (correct) results:

Average (mean) value of payments £146.4
Payments values that occur most often £233
Median value of payments £151

The results, when using the MySQL array gives me the following (wrong) results:

Average (mean) value of payments £144
Payments values that occur most often £144
Median value of payments £144

I'm at a loss to explain why this is happening. Below is the PHP code I'm using to generate these results:

    <?php
        function arithmetic($array, $output = 'mean'){ 
            switch($output){ 
                // This case works out the mean
                case 'mean': 
                    $count = count($array); 
                    $sum = array_sum($array); 
                    $total = $sum / $count; 
                break; 
                // This case works out the median
                case 'median': 
                    rsort($array); 
                    $middle = round(count($array) / 2); 
                    $total = $array[$middle-1]; 
                break; 
                // This case works out the mode
                case 'mode': 
                    $v = array_count_values($array); 
                    arsort($v); 
                    foreach($v as $k => $v){$total = $k; break;} 
                break; 
            } 
            return $total; 
        } 

        // PHP Array with data
        //$arr = array(60, 70, 71, 76, 144, 151, 197, 229, 233, 233);

        // MySQL Connection & Retrieval of data
        $data = mysql_query("SELECT amount FROM example") or die(mysql_error()); 
        $arr = mysql_fetch_array($data);

    ?>

Anyone have any ideas?

UPDATE:
I've been playing the MySQL query, and using the following code:

$data = mysql_query("SELECT * FROM example") or die(mysql_error()); 
$arr = mysql_fetch_assoc($data);
print_r ($arr);

It displays the following, only result:

Array ( [id] => 1 [amount] => 144 )

So really, the query is bringing all of the records and not just the one.

are you sure that the value in your array php and in the db are the same ? — MimiEAM
– MimiEAM, Commented Aug 12, 2012 at 11:39
100% sure. I populated the MySQL database with the values used in the PHP array, as I made the PHP array first. — mickburkejnr
– mickburkejnr, Commented Aug 12, 2012 at 11:40
Hate to be a bugger, but the median is calculated in a different way: If the number of values is even, like this, you need to take the average of the two middle numbers (in this case 144 and 151), so 295/2 = 147.5 — Marty McVry
– Marty McVry, Commented Aug 12, 2012 at 12:18
@MartyMcVry you know that was the next thing I was going to look in to lol. When I get my head around why this isn't giving the correct data I will edit the code to work out the Median properly. — mickburkejnr
– mickburkejnr, Commented Aug 12, 2012 at 12:23
Already a tip: $middle = (count($array) + 1) / 2; if (is_int($middle)) { $total = array[$middle - 1]; } else { $total = ($array[round($middle)] + $array[round($middle - 1)]) / 2; } — Marty McVry
– Marty McVry, Commented Aug 12, 2012 at 12:40

Marty McVry · Accepted Answer · 2012-08-12 12:31:20Z

1

Well, the reason is because you're only fetching one row, and thus one number...

$arr = array();

while ($result_array = mysql_fetch_row($data)) {
    $arr[] = $result_array[0];
}

After this code, you should be able to calculate the correct values.

answered Aug 12, 2012 at 12:31

Marty McVry

3,0711 gold badge22 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mickburkejnr Over a year ago

You sir are a legend. I realised I was getting only one record just as you posted this. Thank you thank you thank you!

MimiEAM · Accepted Answer · 2012-08-12 11:51:18Z

0

I don't know if that helps but you might want to dig in mysql-fetch array() and follow there recommendations there, http://php.net/manual/en/function.mysql-fetch-array.php

especially checking if the data that you are fetching is set correctly.

function db_result_single($result) { 
    return ($row = mysql_fetch_row($result)) && isset($row[0]) ? $row[0] : false; 
}

answered Aug 12, 2012 at 11:51

MimiEAM

2,6401 gold badge27 silver badges29 bronze badges

1 Comment

mickburkejnr Over a year ago

I've read through it and amended it, but the results are still the same.

Collectives™ on Stack Overflow

Getting different results when using a MySQL query array, compared to a PHP array

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related