DISTINCT help

[rayzun]

Hi Im looking and searching the internet for any idea why this problem occurs:

Below query works (but missing an important field "id")
SELECT DISTINCT ITEM , NAME , PRICE , IMGFROM iflorist WHERE PRICE >= 30 and PRICE <= 39.99 ORDER BY NAME DESC LIMIT 0 , 999

"id" // int(11) - auto_increment - index

SELECT DISTINCT ITEM , NAME , PRICE , IMG , id FROM iflorist WHERE PRICE >= 30 and PRICE <= 39.99 ORDER BY NAME DESC LIMIT 0 , 999

Im just trying to get distinct records from between a set price range ??

[bpat1434]

Try this:

SELECT DISTINCT(ITEM), NAME, PRICE, IMG, id
FROM `iflorist`
WHERE PRICE>=30 
AND PRICe<=39.99
ORDER BY NAME DESC
LIMIT 0, 999

~Brett

[rayzun]

Hello bpat1434

Distinct records not working, I still get duplicate's

$query = "SELECT DISTINCT(ITEM), NAME , PRICE , IMG, id FROM iflorist WHERE PRICE >= 0 and PRICE <= 29.99 ORDER BY NAME DESC LIMIT 0 , 999";

If I take out the "id" field no duplicates will output, But I need "id" included.
"id" is setup currently with : INT, INDEX, and auto_increment ??

[bpat1434]

Not really sure what's going on. Have you tried:

SELECT DISTINCT(ITEM), *
FROM `iflorist`
WHERE PRICE >=0
AND PRICE <= 29.99
ORDER BY NAME DESC
LIMIT 0, 999

Also, mySQL documentation mentions that a temp table is needed when using Distinct with ORDER BY. If this query doesn't work, try removing the ORDER BY clause and see if that works. If it does, and you get the info you need, then just fetch all the info to an array, and use [man]asort/man to sort it alphabetically.

~Brett

[rayzun]

Hello bpat1434

Distinct records not working, I still get duplicate's

SELECT DISTINCT (ITEM), NAME , PRICE , IMG , SHIP , id FROM iflorist WHERE PRICE >= 0 and PRICE <= 29.99 LIMIT 0 , 999

Yet this
SELECT DISTINCT(ITEM), *
MySQL said:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* FROM iflorist WHERE PRICE >= 0 and PRICE <= 29.99 LIMIT 0 , 999' at line 1

Two queries together maybe ??

[Lars_Berg]

Distinct is not a function and can not be applied to individual columns.

SELECT `ITEM`, `NAME` , `PRICE` , `IMG` , `SHIP` , min(`id`)
 FROM `iflorist` 
WHERE `PRICE` >= 0 
    and `PRICE` <= 29.99 
group by `ITEM`, `NAME` , `PRICE` , `IMG` , `SHIP`
order by name
LIMIT 0 , 999

[bpat1434]

Umm... then why is it in the mySQL manual? Doh!!
How mySQL optimizes DISTINCT

It's an alternative. Either way should work.

~Brett

[Lars_Berg]

What are you talking about?

There is no semantic difference between

select distinct (a), b from t

and

select distinct a, b from t

[rayzun]

Hi everyone and thankyou for the great help
Ive tried my different approaches, but Im not able to figure on how to output the :
min(id)

echo "min(id)";
echo "$min(id)";
echo "$id";
echo "min($id)";
echo "min(id)"; //etc...

please help, the query is 100% what I was asking help with and being this close is just awsome. Thankyou Lars!

[Lars_Berg]

You can either use an numerical index or give the expression a name in the select list, e.g.

SELECT `ITEM`, `NAME` , `PRICE` , `IMG` , `SHIP` , min(`id`) as smallestId

and use that name in PHP.

[rayzun]

$query = "SELECT `ITEM`, `NAME` , `PRICE` , `IMG` , `SHIP` ,  min(`id`) as `placeid` 
FROM `iflorist` 
WHERE `PRICE` >= 0 and `PRICE` <= 29.99 
group by `ITEM`, `NAME` , `PRICE` , `IMG` , `SHIP` 
order by name LIMIT 0 , 999";

$result = mysql_query($query) or die(mysql_error());

......

echo "$placeid"; //this is not a correct query,

[bpat1434]

$row = mysql_fetch_array($result);
echo $row['placeid'];

~Brett

[rayzun]

THANKS Lars, I think I got it now !!!!! Ya

echo "<table width=\"660\" border=\"0\" cellpadding=\"0\" cellspacing=\"0\">"; 
$query = "SELECT `ITEM`, `NAME` , `PRICE` , `IMG` , `SHIP` , 
min(`id`) as `placeid` 
FROM `iflorist` WHERE `PRICE` >= 0 and `PRICE` <= 29.99 
group by `ITEM`, `NAME` , `PRICE` , `IMG` , `SHIP` 
order by `PRICE` ASC LIMIT 0 , 4";
$result = mysql_query($query) or die(mysql_error());
$counter = 0; $cells_per_row = 4;
while($row=mysql_fetch_array($result))
{ 
$counter++; 
if(($counter % $cells_per_row) == 1)  { echo '<tr>'; }

echo "\n<td>$row['placeid']</td>\n";

if(($counter %  $cells_per_row) == 0) { echo '</tr>'; } 
} 
if(($counter % $cells_per_row) != 0) { echo '</tr>'; } 
echo "</table>\n\n";

[bpat1434]

Ummm... you're welcome too.

Glad to hear you got it working though.

~Brett

[rayzun]

um thankyou too !!!!!! Ya