CODEWITHSUNDEEP
phpmysqlselectmany-to-many
lejahmie
lejahmie

Reputation: 18253

Advanced many2many query for MYSQL

I im trying to build a imagegallery where people have access to different groups and the groups decide what catalogues and images they are allowed to see.
I though many2many structure would be best for this.

So far, ive manage to build the database like this:

image (image_name, image_file, image_id)
catalog (catalog_id, catalog_name)
supplier (supplier_id, supplier_name)
user (name, user_id)
image2cataloge (image_id, catalog_id)
image2supplier (image_id, supplier_id)
catalog2supplier (catalog_id, supplier_id)
user2supplier (user_id, supplier_id)

So... that been said, saving images and making supplier (or group if you want), adding users to supplier and linking images to supplier and catalogues is no problem. Inserting is no problem.
But selecting the right images based upon the users supplier setting and catalog they are in is harder.

For example, I have a user with user_id 1, which have access to supplier_id 1. supplier_id 1 have access to view catalogue 1, which holds images with image_id 1 and 2.
But supplier_id 1 only have access to image_id 2.
All this settings are stored in the database. How do I do the select query?

This is what i've tested;

//$catalog_id is the catalog_id we are in
//$user_id is the current users user_id

$sql = "SELECT i.*
FROM image i, user u, catalog cat, supplier s, supplier2user s2u, supplier2catalog s2c, image2catalog i2c, image2supplier i2s
WHERE u.id = '".$user_id."'
AND s2u.user_id = '".$user_id."'
AND s2u.supplier_id = s.id

AND s2c.catalog_id = '".$catalog_id."' 
AND i2c.catalog_id = '".$catalog_id."'

AND i2s.supplier_id = s.id
AND s2c.supplier_id = s.id

GROUP BY i.id
ORDER BY i.name ASC

But when ive added more than one image, all images are shown for all users in all catalogues.

EDIT (2010/02/05):
Okey, so I've figured out how to at least show correct images in correct catalog. I do this by doing following:

$sql = "SELECT i.*
    FROM 
        image i
    INNER JOIN image2catalog i2c

    ON i.id = i2c.image_id
    AND i2c.catalog_id = '".$pages_level_0[$i]['id']."'

    GROUP BY i.id
    ;";

This let's me output the correct images that belongs in the catalog the user is visiting at the moment. Now I just need to edit this query to filter out all images the user doesn't have access to. I very grateful for any help you can provide!

EDIT 2010/02/09:
---
CREATION SCHEME 

CREATE TABLE `user` (
  `id` int(11) NOT NULL auto_increment,
  `email` varchar(250) NOT NULL,
  `password` varchar(50) NOT NULL
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

CREATE TABLE `imagebank` (
      `id` int(11) NOT NULL auto_increment,
      `name` varchar(250) character set utf8 NOT NULL default '',
      `url` varchar(250) character set utf8 NOT NULL default '',
      `childof` varchar(250) character set utf8 NOT NULL default '',
      `hide` tinyint(4) NOT NULL,
      `publishdate` varchar(14) NOT NULL,
      `expiredate` varchar(14) NOT NULL,
      `editdate` varchar(14) NOT NULL,
      `editbygroup` varchar(250) NOT NULL,
      `openby` varchar(250) NOT NULL,
      `opendate` varchar(250) NOT NULL,
      PRIMARY KEY  (`id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci;

    CREATE TABLE `imagebank_image` (
      `id` int(11) NOT NULL auto_increment,
      `name` varchar(250) NOT NULL,
      `img` varchar(250) NOT NULL,
      PRIMARY KEY  (`id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

    CREATE TABLE `imagebank_image2catalog` (
      `image_id` int(11) NOT NULL,
      `catalog_id` int(11) NOT NULL,
      PRIMARY KEY  (`image_id`,`catalog_id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

    CREATE TABLE `imagebank_image2supplier` (
      `image_id` int(11) NOT NULL,
      `supplier_id` int(11) NOT NULL,
      PRIMARY KEY  (`image_id`,`supplier_id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

    CREATE TABLE `imagebank_supplier` (
      `id` int(11) NOT NULL auto_increment,
      `name` varchar(250) NOT NULL,
      PRIMARY KEY  (`id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

    CREATE TABLE `imagebank_supplier2catalog` (
      `supplier_id` int(11) NOT NULL,
      `catalog_id` int(11) NOT NULL,
      PRIMARY KEY  (`supplier_id`,`catalog_id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

    CREATE TABLE `imagebank_supplier2user` (
      `supplier_id` int(11) NOT NULL,
      `user_id` int(11) NOT NULL,
      PRIMARY KEY  (`supplier_id`,`user_id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

SOME DATA: 

INSERT INTO `imagebank` (`id`, `name`, `url`, `childof`, `hide`, `publishdate`, `expiredate`, `editdate`, `editbygroup`, `openby`, `opendate`) VALUES
(1, 'Test 1', 'test-1', '', 0, '20100204230233', '', '', '', '', ''),
(2, 'Test 2', 'test-2', '', 0, '20100204230244', '', '', '', '', '');

INSERT INTO `imagebank_image` (`id`, `name`, `img`) VALUES
(1, 'Test img 1', 'labb_9noq80bik5.jpeg'),
(2, 'Test img 2', 'labb_53626114dz.jpeg');

INSERT INTO `imagebank_image2catalog` (`image_id`, `catalog_id`) VALUES
(1, 1),
(2, 2);

INSERT INTO `imagebank_image2supplier` (`image_id`, `supplier_id`) VALUES
(1, 2),
(2, 1);

INSERT INTO `imagebank_supplier` (`id`, `name`) VALUES
(1, 'Supplier1'),
(2, 'Supplier2'),
(3, 'Supplier3');

INSERT INTO `imagebank_supplier2catalog` (`supplier_id`, `catalog_id`) VALUES
(1, 2),
(2, 1);

INSERT INTO `imagebank_supplier2user` (`supplier_id`, `user_id`) VALUES
(1, 1),
(1, 11),
(1, 12),
(2, 1),
(2, 10),
(3, 1);

INSERT INTO `user` (`id`, `email`, `password`) VALUES
(1, '[email protected]', 'ff02dd5s33taa2ba5ff7c2c4d3327e444'),
(10, '[email protected]', 'ff02dd5s33taa2ba5ff7c2c4d3327e444'),
(11, '[email protected]', 'ff02dd5s33taa2ba5ff7c2c4d3327e444'),
(12, '[email protected]', 'ff02dd5s33taa2ba5ff7c2c4d3327e444');

WOW, now thats alot of stuff :P So I know that the tables, specially for "catalogs" which i call just "imagebank" might look abit strange. But I do have my reasons and thats not really the issue :) Its part of an even bigger picture. Hope this helps you to help me. Thanks again.

Upvotes: 2

Views: 531

Answers (1)

mlennox
mlennox

Reputation: 122

It looks like if you are passing in the user and catalogue id's then the supplier doesn't matter.

If you required the supplier information in the result, that would be a different matter.

It feels like you shouldn't be involving the user in this query at all as you seem to be looking for the images in a catalogue that are owned by a particular supplier.

If that is the case, then I would drop the requirement for the user id in the query and use the supplier id instead.

I am assuming that the user would have done the following to get to the point where they would be initiating this query:

  • login - obviously :)
  • click on 'list suppliers'
  • click on a supplier
  • click on a catalog

Either way you are going to have to do a lot of INNER JOIN's. For instance the query to retrieve the list of suppliers for a given user would be something like

SELECT 
    s.supplier_id, 
    s.Supplier_name
FROM
    supplier s
INNER JOIN
    user u
INNER JOIN
    user2supplier u2s
ON
    u.user_id = u2s.user_id
ON
    u2s.supplier_id = s.supplier_id
WHERE
    u.user_id = 3 -- for example...

(now, I haven't tested the SQL, but I think that is right...)

Let me know if I'm on the right track - if I have helped, I'd be happy to help some more if I can

Upvotes: 1

Related Questions