[RESOLVED] FUNCTION pp.MIN does not exist

[yahyac]

When I try to run the following query:

	$strSQL = mysql_query("SELECT *, MIN (price)
	                          FROM products, products_pricing
	                          WHERE catID='$catID' AND
	                                subcatID='$subcatID' AND
	                                products.item_num = products_pricing.item_num
                              GROUP BY products_pricing.item_num
                              ORDER BY price", $conn) or die(mysql_error());

I get this reply in my browser:

FUNCTION pp.MIN does not exist
(pp refers to my table)

When I run the same query (of course, without variables) in the MYSQL command line client, it works fine.

Why is this happening?

[TheDefender]

If I read correctly, if you hard code values in for $subcatID and $catID, it works fine?

If that's the case, then change to:

$strSQL = mysql_query("SELECT *, MIN (price)
	                          FROM products, products_pricing
	                          WHERE catID='".$catID."' AND
	                                subcatID='".$subcatID."' AND
	                                products.item_num = products_pricing.item_num
                              GROUP BY products_pricing.item_num
                              ORDER BY price", $conn) or die(mysql_error());

[yahyac]

Well, now it is working. The only thing i changed was the method of making the join. Instead of using the "old style", I simply used the NATURAL JOIN.

Here's the query:

$strSQL = mysql_query("SELECT *, MIN(price)
	                          FROM products NATURAL JOIN products_pricing
	                          WHERE catID='$catID' AND
	                                subcatID='$subcatID'
	                          GROUP BY products_pricing.item_num
	                          ORDER BY price", $conn) or die(mysql_error());

Wish i knew why it wasn't working. But hey, as long as it works.

[yahyac]

Well apparently, and again, i don't know why, the culprit seemed to be the manner in which i was joining the two tables. I used the WHERE statement to "join" the two similar columns products.item_num = products_pricing.item_num. But php just wasn't happy with that.

Or maybe i just made some syntactical mishap that i'm overlooking. As mentioned, it is working now, and realy, NATURAL JOIN is more efficient anyway.

Thanks.

[Tibler]

I just had the same problem, and found the real answer.

The difference that changes everything between the working and the non-working queries is not the NATURAL JOIN, but the little space after the MIN...

MIN(price)  //works

MIN (price) //doesn't work...

And yes, by entering "MIN (price)" directly in phpMyAdmin, syntax is correct :glare:

Bye