SQL query has me baffled

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Steveo31
Forum Contributor
Posts: 416
Joined: Sun Nov 23, 2003 9:05 pm
Location: San Jose CA

SQL query has me baffled

Post 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 :)
kettle_drum
DevNet Resident
Posts: 1150
Joined: Sun Jul 20, 2003 9:25 pm
Location: West Yorkshire, England

Post by kettle_drum »

And the problem is?
fractalvibes
Forum Contributor
Posts: 335
Joined: Thu Sep 26, 2002 6:14 pm
Location: Waco, Texas

Post 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
User avatar
Maugrim_The_Reaper
DevNet Master
Posts: 2704
Joined: Tue Nov 02, 2004 5:43 am
Location: Ireland

Post 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...
lostboy
Forum Contributor
Posts: 329
Joined: Mon Dec 30, 2002 8:12 pm
Location: toronto,canada

Post 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
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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.
Post Reply