PHP - Best price?

[nikomou]

Hi, I have many products which are the same in my database, but want to show only the *lowest* priced... at the moment, only the 1st result in the database is shown..

$sql = "SELECT * FROM $table WHERE make='$getmake' GROUP BY handset";

Cheers..

[mwinter]

Hi, I have many products which are the same in my database, but want to show only the *lowest* priced... at the moment, only the 1st result in the database is shown..

$sql = "SELECT * FROM $table WHERE make='$getmake' GROUP BY handset";

When the GROUP BY clause is used, only expressions within that clause can appear in SELECT expression list unless an aggregate function is used. That is, only handset can be used by itself; including * should raise an error.

Luckly, there is quite an appropriate aggregate function:

  SELECT handset, MIN(price) WHERE make='...' GROUP BY handset

The result set should contain one instance of each handset value with the appropriate make, and the lowest price within each group.

Mike

[djr33]

ORDER BY is also helpful.
If you took the first result of the list ordered by price, then that would be the lowest price. (ASC [ascending] is the default. For descending, use DESC)
Ex. of desc:
....ORDER BY `price` DESC

[nikomou]

came up with an error.. there any other ways to do this?

[djr33]

Error means fix, not give up.
Post the code that gave the error.

[mwinter]

Post the code that gave the error.

Also, please provide more information about the database structure, including what data you're actually looking for in each result.

Mike

[djr33]

Yeah, that too.

[nikomou]

code used:

$sql = "SELECT handset, MIN(price) WHERE make='$getmake' GROUP BY handset"; 

error msg:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/xxxx/public_html/test.php on line 18

database example:

ID | Handset | Make | Network | Tariff | Total Cost| Line Rental | Mins | Texts | URL

There are on average 20 records with the same handset name.

I want to show every handset determaned by its make, but only show the ones with the min "Total Cost"

e.g. of code

<?php
$getmake = $_GET['make'];
$address = "localhost";
$username = "xxxx";
$password = "xxxx";
$db = "xxxx";
$table = "xxxx";
$conn = mysql_connect($address, $username, $password);
$rs = mysql_select_db($db, $conn);
$sql = "SELECT handset, MIN(price) WHERE make='$getmake' GROUP BY handset";
$rs = mysql_query($sql, $conn);
$j = 0;
echo("<table width='100%' border='0' cellpadding='0' cellspacing='0' id='$make'><tr valign='top'>"); 
while($row = mysql_fetch_array($rs)) { 

$handset = $row[handset];
$make = $row[make];
$tariff = $row[tariff];
$tariffid = $row[tariffid];
$id = $row[handsetid];
$price = $row[price];
$url = $row[url];
$gift = $row[gift];
$texts = $row[texts];
$xnet = $row[xnet];
$offpeak = $row[offpeak];
$merchant = $row[merchant];
$contractlength = $row[contractlength];
$montlycost = $row[monthlycost];
$cashback = $row[cashback];
$freerental = $row[freerental];
$halfrental = $row[halfrental];
$total = $row[total];

if ($xnet == "0") $xnetmin = ("");
if ($xnet != "0") $xnetmin = ("$xnet Anytime Minutes");

if ($offpeak == "0") $offpeakmin = ("");
if ($offpeak != "0") $offpeakmin = ("$offpeak Off Peak Minutes");

if ($texts == "0") $textsmin = ("Text Bundles Available");
if ($texts != "0") $textsmin = ("$texts <b>FREE</b> Texts");

echo("<th class='menusmall' align='center' width='16%'><p align='center'><font face='Tahoma' style='font-size: 9pt'><b><br />");
  echo("<table border='0' width='200' id='$handset' cellspacing='5' cellpadding='0'><tr><td colspan='2' width='0'><p align='left'><font style='FONT-SIZE: 9pt' face='Tahoma'><b><span style='TEXT-DECORATION: none'><font color='#2d75d7'><a href='http://www.xxxx.com/handsets.php?hs=$id.'><font color='#2D75D7'><span style='text-decoration: none'>$handset</span></font></a></font></span></b></font></td><td width='9%'><font face='Tahoma' size='1'><a class='tditem' href='http://www.xxxx.com/handsets.php?hs=$id'><img src='http://www.xxxx.com/images/network$network.gif' border='0' align='right'></a></font></td></tr><tr><td width='10%' valign='top'><font style='FONT-SIZE: 9pt' face='Tahoma'><b><span style='TEXT-DECORATION: none'><font color='#2d75d7'><a href='http://www.xxxx.com/handsets.php?hs=$id'><img alt='$handset' src='http://www.xxxx.com/images/handset_$id.jpg' border='0'></a></font></span></b></font></td><td width='83%' colspan='2'><font color='#2D75D7' face='Tahoma'><span style='font-size: 9pt'>$gift</span></font><p><font color='#2D75D7' face='Tahoma'><span style='font-size: 9pt'>$tariff<br><b>$xnetmin$offpeakmin</b><br>$textsmin</span></font></td></tr></table>");
  echo("<br /><br /></th>"); 
  $j++; 
  if(($j % 4) == 0) echo("</tr>\n<tr>"); 
} 
echo("</tr></table>"); 
mysql_close();
?>

[djr33]

Hmm.
It all looks fine to me.
Are you sure that the $getmake variable is set? Did you have (eg) ?make=xxxxxx on the end of the url?

[nikomou]

yeah, used ?make=nokia

also tried a few others, but still get the error