SQL in PHP

[iffoiffy]

Hi,

Do you see any thing wrong with the following.
I was suggested to use the follwing SQL.
No matter what values I have for the variables all the records get picked.. instead of picking no record

****************
$search_image= "junk";
$search_brand="junk";

$result1= mysql_query("SELECT product_id, model,retail_price,product_image_small FROM products WHERE brand = COALESCE(@search_brand, brand) AND product_image_small = COALESCE(@search_image, product_image_small) " )
or die("Couldn't execute query: " . mysql_error());

****************************
I tried following too, that produced the same results

$search_image= "junk";
$search_brand="junk";

$result1= mysql_query("SELECT product_id, model,retail_price,product_image_small FROM products WHERE brand = COALESCE(@'$search_brand', brand) AND product_image_small = COALESCE(@'$search_image', product_image_small) " )
or die("Couldn't execute query: " . mysql_error());

Thanks

 

[r937]

what is that @ supposed to be doing?

assuming that the php is correct (i have no idea -- not everybody using mysql knows php, and this is the mysql forum), the sql is fine

however, i do know that it would be a lot more efficient to use some IF/ELSE logic in your script to build up the appropriate sql string before sending it to mysql, so that you don't have any clauses testing a column value against itself if one of those php parameters is null


rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[iffoiffy]

actually this SQL is for advanced search page with 10-15 drop downdown boxes. With first value of drop down box always "NULL" for "does not matter" . I was trying to avoid if/else logic that's why someone suggested me this based on the following article

http://www.sqlteam.com/item.asp?ItemID=2077

about "COALESCE Function"

 

[r937]

but you should use IF/ELSE logic in your script

it is more efficient to do IF/ELSE logic once in your script than ask the database to do it on every row!!

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[iffoiffy]

ok i will do if/else. i was under the impressiion that this approch will be more efficient, but probably not.....