find max and min in php

[vBR]

This is a portion of a some php script I am writing to fetch the company's tour dates from a database.

All is working except for a section where I need to find the max and min values of a column in an array.

I would like to be able to use php to find these values from the existing array rather that querying the database again.


// ################################ start query database ################################

// create sql statement
$sql = "SELECT tour.startdate, tour.enddate, production.production, production.html, venue.venue, venue.addressc FROM (tour LEFT JOIN production USING (productionid)) LEFT JOIN venue ON tour.venueid = venue.venueid WHERE startdate $operator UNIX_TIMESTAMP(CURRENT_DATE) ORDER BY tour.startdate $order";

//execute sql query
$sql_result = mysql_query($sql, $connection) or die ("Could not execute query.");

// ################################ start navigation ################################

$getvar=MYSQL_FETCH_ARRAY($sql_result);
$dates=$getvar["startdate"];

$yearmin = min($dates);
$yearmax = max($dates);

for ($i=&yearmin;$i=&yearmax;$i++) {
echo "<a href=\"#$i\"> ";
}

[vBR]

I have gotten the effect I wanted by adding another MySQL query.
Still, can it be done in php?


// ################################ start query database ################################

// create sql statement
$sql = "SELECT tour.startdate, tour.enddate, production.production, production.html, venue.venue, venue.addressc FROM (tour LEFT JOIN production USING (productionid)) LEFT JOIN venue ON tour.venueid = venue.venueid WHERE startdate $operator UNIX_TIMESTAMP(CURRENT_DATE) ORDER BY tour.startdate $order";

//execute sql query
$sql_result = mysql_query($sql, $connection) or die ("Could not execute query.");

$sql2 = "SELECT max(startdate) AS max, min(startdate) AS min FROM tour WHERE startdate $operator UNIX_TIMESTAMP(CURRENT_DATE)";

$sql_result2 = mysql_query($sql2, $connection) or die ("Could not execute query.");

// ################################ start navigation ################################

$dates=MYSQL_FETCH_ARRAY($sql_result2);

$yearmin = date("Y",$dates["min"]);
$yearmax = date("Y",$dates["max"]);

for ($i=$yearmin;$i<=$yearmax;$i++) {
echo "<a href=\"#$i\">$i</a> ";
}

[Chen]

It can't be done in PHP because you are not looping through the results from the database - in doing this:
$getvar=MYSQL_FETCH_ARRAY($sql_result);You are just putting the first record in $getvar, but you're never going through the other records.

To get the min() and max() values you have to while() through the records, put all dates in the $dates array and then using min() max().

[seanf]

Do it in your query. See min() and max()

http://www.mysql.com/doc/en/Group_by_functions.html

Sean :)

[Chen]

Yes he's done that in the last post but wanted to know if there's a way to do without another query.