Page 1 of 1
[Solved] Repeating values... arghhhh
Posted: Tue Oct 26, 2004 3:43 am
by Steveo31
Code: Select all
SELECT DISTINCT i.store_name, i.product_name, i.uploadDate, up.thumb_path
FROM inventory AS i, uploads AS up
ORDER BY i.uploadDate DESC
I don't think the database structure is relevant at this point, but let me know if you need it.
This is returning:
Code: Select all
store_name product_name uploadDate thumb_path
Steve's Knick-Knacks Toaster 2004-10-26 members/steveo31/toaster_thumb.jpg
Steve's Knick-Knacks Toaster 2004-10-26 members/steveo31/oil-filter_thumb.jpg
Steve's Knick-Knacks Oil Filter 2004-10-25 members/steveo31/toaster_thumb.jpg
Steve's Knick-Knacks Oil Filter 2004-10-25 members/steveo31/oil-filter_thumb.jpg
in phpMyAdmin. I thought the DISTINCT would work, but it doesn't. I just need to pull those fields out once... not twice.
Any ideas, or do you need more info?
Posted: Tue Oct 26, 2004 4:35 am
by Wayne
Your SQL is incorrect, you are going to have to be more specific about what you are trying to get out of the database, how are you joining the 2 tables etc.
Posted: Tue Oct 26, 2004 7:53 am
by CoderGoblin
If you look at the return results each row is distinct (product_name and thumb path vary). You may want to look up DISTINCT ON ... to select the columns you wish to be unique.
Posted: Tue Oct 26, 2004 3:59 pm
by Steveo31
Yeah, kinda figured that wayne
I'm sure there's a way to pull them out without DISTINCT...
What I'm attempting is to pull the store_name, product_name, and upload date out of a table called "inventory". In addition, pull out the thumbnail path from a table called "uploads". This script has been chopped up a lot, so a while ago I had a WHERE clause to pull only the info out WHERE store_name is the one owned by the user.
Code: Select all
CREATE TABLE `inventory` (
`id` int(10) NOT NULL auto_increment,
`product_id` varchar(100) NOT NULL default '',
`product_name` varchar(30) NOT NULL default '',
`uploadDate` date NOT NULL default '0000-00-00',
`store_name` varchar(30) NOT NULL default '',
`store_owner` varchar(30) NOT NULL default '',
`store_email` varchar(50) NOT NULL default '',
`search_hits` int(10) NOT NULL default '0',
`hits` int(10) NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `product_name` (`product_name`,`store_name`)
)
Code: Select all
CREATE TABLE `uploads` (
`id` int(10) NOT NULL auto_increment,
`username` varchar(30) NOT NULL default '',
`lastUploadDate` date NOT NULL default '0000-00-00',
`product_id` varchar(100) NOT NULL default '',
`product_name` varchar(20) NOT NULL default '',
`image_path` varchar(50) default 'members/nophoto_thumb.jpg',
`thumb_path` varchar(50) default 'members/nophoto_thumb.jpg',
PRIMARY KEY (`id`)
)
Posted: Tue Oct 26, 2004 4:12 pm
by timvw
Assuming product_name in uploads is a foreign-key to product_name in inventory...
Code: Select all
SELECT i.store_name, i.product_name, i.uploadDate, t.thumb_path
FROM inventory AS i
INNER JOIN uploads AS u ON i.product_name=u.product_name
Btw, be consistent when naming attributes. You seem to use a lot of _ and then you you uploadDate instead of upload_date
Posted: Tue Oct 26, 2004 6:53 pm
by Steveo31
Hah, funny you should mention that. I was thinking about changing them the other day.
Code works great, thanks!