Memory Jogger: SQL Query for minimum and maximum data values.

In the past when I needed to find the minimum and maximum value of a field returned in a data set I would loop through the query result in Flash Actionscript like this:

var minValue:Number = Number.MAX_VALUE;
var maxValue:Number = Number.MIN_VALUE;

for(var i:Number = 0; i < resultLength; i++) {
minValue = Math.min(minValue, result[i]);
maxValue = Math.max(maxValue, result[i]);
}

However I have recently began to try and move more of this sort of functionality from the front end to the PHP class functions in the AMFphp class services on the back end. The obvious advantage being that querying an indexed value directly from a MySQL table is going to be a lot faster than looping through large data sets to find the same values.

So this is the PHP function I use to do the same thing now.

function getRangeData ($ID, $tData, $tColumn) {

$query_str = “SELECT A.* FROM (SELECT * FROM ” . $tData;
$query_str .= ” WHERE ID = ” . $ID . ” ORDER BY ” . $tColumn . ” ASC limit 1)A “;
$query_str .= “UNION “;
$query_str .= “SELECT B.* FROM (SELECT * FROM ” . $tData;
$query_str .= ” WHERE ID = ” . $ID . ” ORDER BY ” . $tColumn . ” DESC limit 1)B “;

$data = mysql_query($query_str);

$result_array = array();
$resultCount = 0;

while($resultRow = mysql_fetch_array($data, MYSQL_ASSOC)) {

$result = $resultRow[$tColumn];
$result_array[$resultCount][$tColumn] = $result;
$resultCount ++;

}

return $result_array;

}

In this case you can pass the name of the table, the required data column and the “ID” you want the data filtered against however you could modify this easily enough if you didn’t require a WHERE class.

Related Posts:

This entry was posted in Memory Joggers and tagged , , , , , , , , . Bookmark the permalink. Post a comment or leave a trackback: Trackback URL.

Post a Comment

Your email is never published nor shared. Required fields are marked *

*
*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>