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.
Şimdiye kadar, ive bu gibi veritabanını oluşturmak için yönetmek:
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?
Bu test ettik budur;
//$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
Ama ive birden fazla görüntü eklendiğinde, tüm görüntüler tüm kataloglarında tüm kullanıcılar için gösterilir.
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
;";
Bu diyelim bana çıktı kullanıcı şu anda ziyaret katalogda ait doğru görüntüler. Şimdi sadece kullanıcı erişimi olmayan tüm görüntüleri filtrelemek için bu sorguyu düzenlemek gerekir. Eğer sağlayabilir herhangi bir yardım için çok minnettarım!
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, 'User1@test.com', 'ff02dd5s33taa2ba5ff7c2c4d3327e444'),
(10, 'User2@test.com', 'ff02dd5s33taa2ba5ff7c2c4d3327e444'),
(11, 'User3@test.com', 'ff02dd5s33taa2ba5ff7c2c4d3327e444'),
(12, 'User4@test.com', 'ff02dd5s33taa2ba5ff7c2c4d3327e444');
Ben özel i just "Imagebank" diyoruz "katalog" için tabloları, abit garip görünebilir biliyorum Yani P: WOW, şimdi bir sürü şey bu. Ama benim nedenleri ve thats gerçekten sorun :) daha büyük bir resmin onun parçası var. Bu bana yardımcı olur umarım. Tekrar teşekkürler.
