SELECT Result linked to Multiple Records

[dsmitzner]

I'm currently using MySQL 5.1.53 and I'm trying to design my own image gallery using PHP 5. So to begin with images are contained in one table and the categories are listed in another, but what I'm trying to accomplish is have my record for the image contain the id's of my categories, hopefully not limited to using more table cells to contain catid1,catid2,catid3 or using commas to separate my ids which is what I have done so far. This has meant I must then use another query during output of the HTML to correctly display the names of the categories, and I imagine withh cause me to have to use LIKE statements in order to have my search form correctly fetch results. In any case, here is my table structure:

-- --------------------------
-- Table structure for `categories`
-- --------------------------
DROP TABLE IF EXISTS `categories`;
CREATE TABLE `categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(80) DEFAULT NULL,
  `parent_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of categories
-- ----------------------------
INSERT INTO `categories` VALUES ('1', 'Family', null);
INSERT INTO `categories` VALUES ('2', 'Friends', null);
INSERT INTO `categories` VALUES ('3', 'Places', null);

-- ----------------------------
-- Table structure for `images`
-- ----------------------------
DROP TABLE IF EXISTS `images`;
CREATE TABLE `images` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `cat_id` int(11) NOT NULL,
  `title` varchar(80) DEFAULT NULL,
  `filename` varchar(250) NOT NULL,
  `thumbnail` varchar(250) NOT NULL,
  `timestamp` int(11) NOT NULL,
  `width` int(11) DEFAULT '640',
  `height` int(11) DEFAULT '480',
  `comments` int(11) DEFAULT '0',
  `views` int(11) DEFAULT '0',
  `status` int(1) DEFAULT '1',
  `tags` longtext,
  PRIMARY KEY (`id`),
  UNIQUE KEY `utitle` (`title`),
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of images
-- ----------------------------
INSERT INTO `images`VALUES ('1', '1,2', 'X-Mas 2010', 'xmas2010.jpg', 'thumbs/xmas2010.jpg', '1312279886', '640', '480', '0', '0', '1', 'christmas,family,friends,party');

 

My first thoughts turned out something like this:

SELECT images.*,categories.* FROM images,categories WHERE images.cat_id=categories.id

 

Of course the problem is with that I have is when I'm querying the database, as my records show this doesn't work out because I've used commas to separate my cat_id's. Am I on the right track, or what am I missing here?

[Muddy_Funster]

I would suggest using a refference table.  While each image can have multiple catID's there will never be multiple images with the same imageID: so make a refference table that lists imageID and it's relevent catID with a record for each refference.  Then use JOINs to marry it all together.

 

You should not be using SELECT * on either table, never mind both of them since they both contain data that you will not be needing output.

[kickstart]

Hi

 

You appear to have a comma separated list of ids in an integer field. That won't work.

 

While it is technically possible to have a list of comma separated ids in a text field and do a table join based on that, it is a really bad idea (and the coding is quite nasty to read).

 

What you need is a 3rd table in the middle to link images to categories. Say ImageCategoryLink:-

 

DROP TABLE IF EXISTS `ImageCategoryLink`;

CREATE TABLE `ImageCategoryLink` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `imageId`  int(11) DEFAULT NULL,

  `categoryId` int(11) DEFAULT NULL,

  PRIMARY KEY (`id`),

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

Put indexes on imageId and categoryId as well.

 

Then use something like

 

SELECT images.*,categories.* 
FROM images
INNER JOIN ImageCategoryLink ON images.id = ImageCategoryLink.imageId
INNER JOIN categories ON imageId.categoryId = categories.id

 

Don't use * though for any live code.

 

All the best

 

Keith

[Muddy_Funster]

I'm sure one of those JOINS wants to be a side ways one...doesn't it?

[kickstart]

Hi

 

No, unless you want images that are not in any category.

 

All the best

 

Keith

[Muddy_Funster]

My brain is more than a little frazzled, it looks to me that it will only draw a single catagory per image doing it that way.  I trust you're right and I'm just seeing things wrong.

[kickstart]

Hi

 

The inner join will bring back all the matching possible combinations.

 

All the best

 

Keith

[dsmitzner]

@kickstart - It seems to work perfect, thanks a million!

[dsmitzner]

Actually I had one additional question about this particular subject, when it comes to the image.php page where I'm using a get variable to request the record from the database, is there a way for me to select all the categories an image is contained in while only showing one result?

 

I had tried:

SELECT images.*,categories.* 
FROM images
INNER JOIN relations ON images.id = relations.image_id
INNER JOIN categories ON relations.cat_id = categories.id
WHERE images.id=1

 

However it still returns two records.

[fenway]

You can GROUP_CONCAT() the category names, I suppose.

[kickstart]

Hi

 

As Fenway says, Group_Concat should do it. Do you want the category names or the category ids?

 

SELECT images.*,GROUP_CONCAT(categories.name)
FROM images
INNER JOIN relations ON images.id = relations.image_id
INNER JOIN categories ON relations.cat_id = categories.id
WHERE images.id=1 
GROUP BY images.id

 

All the best

 

Keith