[RESOLVED] Trying to display photos of other items in a category as a list with title/photo name

[mnewbegin]

I'm selecting from the products, and images tables. (By the way images are not stored in mysql, just the name of them so I can reference them)

In the images table there is an imageID which is the primary key
In the products table there is ID which is the primary key.

So ID exists in the images table multiple times, 1 time per image that is stored

as in the example showing the images table

http://www.webpixhut.com/?v=CKBt3.png

here is the products table
http://www.webpixhut.com/?v=8eUE.png

And when I try to query it with both tables: I get this as an output instead of the ones just with images.primary='1'

I was using this query

SELECT *
FROM products, images
WHERE products.categoryID = '5'AND images.primary = '1'
ORDER BY products.ID ASC
LIMIT 0 , 30

I get this as an output: the same ID over and over repeating through the number of times that ID has a picture:

http://www.webpixhut.com/?v=CP3kl.png

I want to select ONLY the photo names from the images table where the value primary='1'and the product name from products, and the categoryID number they are in so I can display a list of the product names and show the pictures of those products in the same category

I hope this has not been confusing. here are my tables.

products

CREATE TABLE IF NOT EXISTS `products` (
  `ID` int(255) NOT NULL AUTO_INCREMENT,
  `username` int(255) NOT NULL,
  `title` varchar(256) COLLATE utf8_unicode_ci NOT NULL,
  `content` longtext COLLATE utf8_unicode_ci NOT NULL,
  `date` int(255) NOT NULL,
  `content2` longtext COLLATE utf8_unicode_ci NOT NULL,
  `stickerID` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `categoryID` varchar(256) COLLATE utf8_unicode_ci NOT NULL,
  `featured` int(1) NOT NULL,
  `dimensions` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
  `model` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=52 ;

images

CREATE TABLE IF NOT EXISTS `images` (
  `imageID` int(25) NOT NULL AUTO_INCREMENT,
  `ID` int(25) NOT NULL,
  `dateAdded` date NOT NULL,
  `photo` varchar(50) COLLATE latin1_german2_ci NOT NULL,
  `caption` varchar(150) COLLATE latin1_german2_ci NOT NULL,
  `primary` int(1) NOT NULL,
  PRIMARY KEY (`imageID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci AUTO_INCREMENT=44 ;

[mnewbegin]

ok, after an hitting my forehead saying WTH, I think I got it.

SELECT images.photo, products.title, images.primary, products.categoryID
FROM products, images
WHERE products.ID = images.ID
AND products.categoryID = '5'
AND images.primary = '1'
LIMIT 0 , 30

gives me all my requirements.

I believe what I was doing wrong using this: Was I really didn't need to select all I just needed the parts I required for my table, so it was trying to sort the data that was joined. By bypassing outputting the ID, the duplication does not happen, and I just use it in the WHERE statement but not actually output the data

LOL, hope this helps someone if they have the same issue.

SELECT *
FROM products, images
WHERE products.categoryID = '5'AND images.primary = '1'
ORDER BY products.ID ASC
LIMIT 0 , 30