help with getting max and min values

greedyisg00d

How can get the maximum and minimum from several records for example:

Product | Company | Amount
Chocolate | ComA | 15
Chocolate | ComB | 20
Chocolate | ComC | 25
Chocolate | ComD | 30

I want the result to be displayed like this:

Product | Company | MAX | MIN
Chocolate | Link | 30 | 15

Thus when I click the link it will display in a separate tab ComA, ComB, ComC, and ComD.
Any idea on how can this be done. Sample code is much appreciated. Thanks

BuzzPHP

I would run one SQL statement to get the products you want to display,

SELECT * FROM products

Then I'd iterate the results and use the following to get the min/max

SELECT max(amount), min(amount)
FROM products
WHERE product="$product"

Im unsure if its possible todo it with one SQL statement.

Note: Before anyone complains about "what if there are thousands of records", normally in the first SQL statement you would limit the number of records queried. This is done via LIMIT or WHERE. ie: use LIMIT to implement pagination, and WHERE to implement search filtering.

As far as a link showing a tab. thats a userinterface issue. It depends on if you are using a GUI Library or coding it yourself, and if you want a JavaScript style tab, seperate page tab or some kinda AJAX system.

BuzzPHP

To cover my bases, because you posted in "general" and not "database", you may be after some non SQL code.

Try this, although, im not sure what would happen with thousands of products.

<?php
$products = array(
  "Chocolate" => array("ComA" => 15, "ComB" => 20, "ComC"=> 25, "ComD" => 30),
  "Fruit" => array("ComA" => 100, "ComB" => 5, "ComC"=> 4),
  "Laptop" => array("IBM" => 2000, "Dell" => 1899, "Chuckycheese"=> 5)
);

echo "<table border=1>";
echo "<tr><td>Product<td>Company<td>MAX<td>MIN";
foreach ($products as $name => $value)
{
  echo "<tr><td>$name<td>" . implode(", ",array_keys($value)). 
  "<td>" . max($value) . "<td>" .  min($value);
}
echo "</table>";
?>

Weedpacket

BuzzPHP wrote:
Im unsure if its possible todo it with one SQL statement.

Select product, max(amount), min(amount) from products group by product