Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote -1 down vote favorite

I have the following code to create an array from a column in a MySQL table:

$BCM = array();
while($BCMrow = mysql_fetch_array($allanswers)){
$BCM[] = $BCMrow['BCM'];
}
$BCMrisk = max($BCM);

This looks in the recordset table and pulls the data from each row for the column titled BCM. Then it looks to see which is the highest number. This works fine, but I need to do this for another 10 columns and when I just repeat that code only the first one works.

Any ideas how to fix this?

Edit: Thanks everyone, i went with JBRTRND-DEV's suggestion. It is pretty similar to most of the other ones and works fine. I just needed a quick one to work now, but I will look into using MySQL itself to make it, and into PDO stuff. I'm just starting out with php/mysql stuff. cheers

share|improve this question
3  
If only thing you want to achieve is to get max value of a certain column, then why don't you let mysql do it for you? Just do something like SELECT MAX(BCM) FROM your_table. It will be much faster. – Ventus May 24 '12 at 7:58
Can you post the rest of your code? The one that does not work... – Uriel_SVK May 24 '12 at 7:58
Please stop writing new code with the ancient mysql_* functions. They are no longer maintained and community has begun the deprecation process . Instead you should learn about prepared statements and use either PDO or MySQLi. If you cannot decide, this article will help to choose. If you care to learn, here is a quite good PDO-related tutorial. – tereško May 24 '12 at 8:41

closed as too localized by casperOne♦ Jun 28 '12 at 11:03

This question is unlikely to help any future visitors; it is only relevant to a small geographic area, a specific moment in time, or an extraordinarily narrow situation that is not generally applicable to the worldwide audience of the internet. For help making this question more broadly applicable, see the FAQ.

6 Answers

up vote 0 down vote

I'm not sure I understand your problem correctly but you could just create several arrays to fill in your loop:

 $BCM = array();
$BCM2 = array();
    while($BCMrow = mysql_fetch_array($allanswers)){
    $BCM[] = $BCMrow['BCM'];
    $BCM2[] = $BCMrow['BCM2'];
    }
    $BCMrisk = max($BCM);
    $BCM2risk = max($BCM2);
share|improve this answer
up vote 0 down vote

Let the database do the job:

SELECT MAX(BCM) AS maxBCM, MAX(BCM2) AS maxBCM2 FROM ...
share|improve this answer
up vote 0 down vote

Use a loop which contains your columns names :

$cols = array('column_1','column_2','column_3','column_4',...);
$max = array();
while($BCMrow = mysql_fetch_array($allanswers)){
    foreach($cols as $col) {
        if( isset($max[$col]) ) {
            $max[$col] = max($max[$col],$BCMrow[$col]);
        } else {
            $max[$col] = $BCMrow[$col];
        }
    }
}

Then, in $max, you have all maximum value for each columns of $cols

share|improve this answer
Thanks guys. I went with this and it works. It probably would be better to just use MySQL itself to do it but I am in a bit of a rush so didn't want to venture into anything new just yet; I may come back to it. Thanks again – chris_huh May 24 '12 at 8:27
up vote 0 down vote

Are you calling mysql_free_result on the SQL query every time? Otherwise your array pointer will not be rewound.

And I agree with Ventus. If you just want the max from a column you should be computing it in SQL; SQL is designed to optimize those kinds of queries much faster than PHP can because it can evaluate result set sizes and change algorithms accordingly on the fly.

share|improve this answer
up vote 0 down vote

If it was me I would do it like this:

$columns = array('col1','col2','col3','etcetera');
foreach ($columns as $column) {
$query = "SELECT MAX($column) AS max_$column from $table";
$max_column = 'max_'.$column;
$result = mysql_fetch_array(mysql_query($query));
$results[$max_column] = $result[0];
}
share|improve this answer
up vote 0 down vote

Here's how I'd do it:

$columns = array();
while($row = mysql_fetch_array($allanswers)){
    foreach($row as $k=>$v) {
        $columns[$k][] = $v;
    }
}
$columns_max = array_map('max', $columns);

This is about the sixth answer proposed so far, but I think it's the most general (so far), since it doesn't need to know what column names to expect.

The above code is based on this function that I wrote a while back that I wish I had more excuses to use:

function flipKeys($array) {
    $result = array();
    foreach($array as $a=>$sub) {
        foreach($sub as $b=>$v) {
            $result[$b][$a] = $v;
        }
    }
    return $result;
}
share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.