Multiple results returning

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

Multiple results returning

Post by Steveo31 »

Code: Select all

SELECT inventory.store_name, inventory.product_name, inventory.upload_date, uploads.thumb_path, products.search_hits, products.hits 
FROM inventory, uploads, products 
WHERE CAST(inventory.store_name AS CHAR CHARSET utf8) = 'My Store''s $tuff!' 
AND products.product_name = uploads.product_name
AND products.id = uploads.id 
ORDER BY inventory.upload_date DESC
I would assume this would bring back something like:

Code: Select all

store_name    product_name    upload_date          thumb_path           search_hits           hits

>My Store's $tuff! 	Car 	2004-12-25 12:07:42 	members/Steveo31/car_thumb.jpg 	2 	2
>My Store's $tuff!  	Tools  	2004-12-25 12:06:35  	members/Steveo31/tools_thumb.jpg  	0  	1
>My Store's $tuff!  	Bread  	2004-12-25 12:05:28  	members/Steveo31/toaster_thumb.jpg  	4  	10
And not

Code: Select all

>My Store's $tuff!  	Car  	2004-12-25 12:07:42  	members/Steveo31/toaster_thumb.jpg  	0  	2
>My Store's $tuff! 	Car 	2004-12-25 12:07:42 	members/Steveo31/modelt_thumb.jpg 	0 	1
>My Store's $tuff! 	Car 	2004-12-25 12:07:42 	members/Steveo31/tools_thumb.jpg 	0 	1
>My Store's $tuff! 	Tools 	2004-12-25 12:06:35 	members/Steveo31/tools_thumb.jpg 	0 	1
>My Store's $tuff! 	Tools 	2004-12-25 12:06:35 	members/Steveo31/toaster_thumb.jpg 	0 	2
>My Store's $tuff! 	Tools 	2004-12-25 12:06:35 	members/Steveo31/modelt_thumb.jpg 	0 	1
>My Store's $tuff! 	Bread 	2004-12-25 12:05:28 	members/Steveo31/toaster_thumb.jpg 	0 	2
>My Store's $tuff! 	Bread 	2004-12-25 12:05:28 	members/Steveo31/modelt_thumb.jpg 	0 	1
>My Store's $tuff! 	Bread 	2004-12-25 12:05:28 	members/Steveo31/tools_thumb.jpg 	0 	1
Any ideas on this or the CAST() thing?
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

you should group by product_name and select sum(whatever hits) instead of hits...
Steveo31
Forum Contributor
Posts: 416
Joined: Sun Nov 23, 2003 9:05 pm
Location: San Jose CA

Post by Steveo31 »

Oh man...! So close!! But a step in the right direction nonetheless, thanks a ton man. But now the product_name and image_path don't correlate. Grouping by product_id gives the names correctly, but the image_paths are wrong. I'll check up on SQL stuff now....

Thanks again dude.
Post Reply