Getting maximum value inside a loop

Doesn't quite work

[Patrick Taylor]

I'm using something like this, to output a table of rows:

// Set max_val
$max_val = 0;
// Do the loop
while ($row = mysql_fetch_assoc($result)) {
$max_val = max($max_val, $row['integer']);
// etc etc
echo $max_val;
// etc etc
}

I want the maximum value of $row['integer'] to be available in every row. The above example does this, with the exception of the first (top) row of the table, where $max_val is then the value of $row['integer'] for that row - not the actual maximum for all rows.

I'd appreciate help with a solution, if there is one.

Patrick

[Habtom]

Try echoing $row['integer'] and see what you get.

$max_val = max($max_val, $row['integer']);
echo $row['integer'];

btw, integer is a reserved word, may be you should consider renaming it a bit.

Habtom

[Patrick Taylor]

I'm echoing both $max_val and $row['integer'] in every row - they are used to calculate the width of a bar image.

In the first (top) row they both show as the same number: the value of $row['integer'], which is wrong. In all other rows they are different, as they should be (except in the row that does contain the maximum value).

Thanks for the pointer on 'integer'. I haven't actually used that.

[edited by: Patrick_Taylor at 1:01 pm (utc) on Aug. 18, 2007]

[Habtom]

In the first (top) row they both show as the same number: the value of $row['integer'], which is wrong.

May be I didn't fully understand what the problem is, but if you assign max_val to the $_row['integer'], shouldn't both have the same value initially, as max_val is 0 (if $_row['integer'] has got the a postivive non zero value)

$max_val = max($max_val, $row['integer']);

Habtom

[Patrick Taylor]

Thanks. You're probably right. What I'm trying to do is:

-> calculate the length of an image bar (for a bar graph) that appears in every row of the table. But the way I've done it (above) shows $max_val and $row['integer'] both having the same value in the first row. So my first row (only) shows an incorrect bar length. The values should only be the same in a row where $max_val is actually the same as $row['integer'].

So my code is wrong, and I'm wondering what the solution is.

[pinterface]

Let's run through a hypothetical data set: [1, 6, 4, 3, 8, 20, 14, 12, 7]

current ¦ max of ¦ max
  -     ¦   --   ¦  0
  1     ¦  0,  1 ¦  1
  6     ¦  1,  6 ¦  6
  4     ¦  6,  4 ¦  6
  3     ¦  6,  3 ¦  6
  8     ¦  6,  8 ¦  8
 20     ¦  8, 20 ¦ 20
 14     ¦ 20, 14 ¦ 20
 12     ¦ 20, 12 ¦ 20
  7     ¦ 20,  7 ¦ 20

The solution is to calculate the maximum before you try to use it. Otherwise, you end up not with the maximum for all rows, but with the maximum of the rows you've been through so far.

Or you could just ask the database, through either a second query: 
  
select max(integer) from table;
 
or a subquery: 
  
select max_int, integer from table, (select max(integer) as max_int from table) as dtable;
 
  
select (select max(integer) from table) as max_int, integer from table;
 
. I don't think mysql lets you say "SELECT max(field), field ...", but it would be handy if it did.

[Patrick Taylor]

The solution is to calculate the maximum before you try to use it.

$max_val can only be calculated within the loop (I think). It doesn't exist in the database. My SQL query is something like:

SELECT COUNT(*) AS number, DATE_FORMAT(datetime, '%M %d %Y, %W') AS date_formatted FROM data GROUP BY date_formatted

It's the maximum value of $row['number'] I need for use in all rows including row one, for each date group (row).

[coopster]

You can do it in one query using subselects (subqueries), but it gets a little more complex.

SELECT 
 ( 
 SELECT 
 MAX(t1.nbr) 
 FROM 
 ( 
 SELECT 
 DATE_FORMAT(t2.datetime, '%M %d %Y, %W') AS dateFmt, 
 COUNT(*) AS nbr 
 FROM data AS t2 
 GROUP BY dateFmt 
 ) AS t1 
 ) AS number, 
 DATE_FORMAT(t3.datetime, '%M %d %Y, %W') AS date_formatted, 
 COUNT(*) AS number 
FROM data AS t3 
GROUP BY date_formatted 
;