Page 1 of 1

SQL query has me baffled

Posted: Fri Dec 03, 2004 9:21 pm
by Steveo31
Now this is weird. The query:

Code: Select all

SELECT products.product_name, products.description, products.price, uploads.thumb_path FROM products, uploads WHERE 1 AND products.product_name LIKE '%toaster%'
The outcome:

Code: Select all

product_name   	  description   	  price   	  thumb_path
Toaster 	Great toaster, only 300 pieces run through.  Mint ... 	30 	members/steveo31/tools_thumb.jpg
Toaster 	Great toaster, only 300 pieces run through.  Mint ... 	30 	members/CanadaMan102/toaster_thumb.jpg
Now, the way it's set up is the products table, with a buncha stuff, doesn't really matter, but the query should only pull out this product_name, description, price, and thumb_path from the products and uploads table where the product_name LIKE '%toaster%', right?!

I'm using WHERE 1 just to get the WHERE out of the way as I am using loops to construct the query.

Thanks :)

Posted: Fri Dec 03, 2004 9:26 pm
by kettle_drum
And the problem is?

Posted: Sun Dec 05, 2004 10:16 pm
by fractalvibes
My question as well - you are getting 2 hits on name like %toaster%....
do you think that you should be getting more? less? We don't the data nor the nature of the data, so kettle_drums reply is all that can be said.

Get rid of the where 1 - it has no use here...

fv

Posted: Tue Dec 07, 2004 9:21 am
by Maugrim_The_Reaper
If you're looping through the result set and get two hits per item - you probably should be fetching associative index only. By default, the common commands usually return both a key, and incremented index - i.e. 2 sets of results.

mysql_fetch_assoc() will solve that - it won't fetch the second incremented index into your arrays...

"WHERE 1 AND"

What's the point of the 1? Your where statement is the section following the AND...

Posted: Tue Dec 07, 2004 12:38 pm
by lostboy
The point of the 1 is that you get around the coding needed to see if you need the AND. If any of the search terms are not required / filled out, then the query is fine since one is alsways true....then if 1 or more number of items are filled out in the form you only need to code the 'AND item like...' instead having to check to see if this is the first item which would not require the AND or an additional item which does require the AND

Posted: Tue Dec 07, 2004 3:55 pm
by timvw
lostboy wrote:The point of the 1 is that you get around the coding needed to see if you need the AND. If any of the search terms are not required / filled out, then the query is fine since one is alsways true...
Although i understand what you are trying to do, i've only seen WHERE 1=1 before, because i'm not sure how the standard says that 1 should be evaluated.