Re: working on my new blog and...

[Claude Martin]

i didn't like your long names so i did something like this: using unsigned integers would be better - i forgot about that.

CREATE TABLE `user` (
`id` INT NOT NULL AUTO_INCREMENT ,
`name` VARCHAR( 20 ) NOT NULL ,
`pass` VARCHAR( 30 ) NOT NULL ,
PRIMARY KEY ( `id` )
);
CREATE TABLE `blog` (
`id` INT NOT NULL AUTO_INCREMENT ,
`title` VARCHAR( 100 ) NOT NULL ,
`hidden` TINYINT NOT NULL ,
PRIMARY KEY ( `id` )
);
CREATE TABLE `post` (
`id` INT NOT NULL AUTO_INCREMENT ,
`blog` INT NOT NULL ,
`user` INT NOT NULL ,
`time` DATETIME NOT NULL ,
`post` TEXT NOT NULL ,
`hidden` TINYINT NOT NULL ,
PRIMARY KEY ( `id` )
);
CREATE TABLE `auth` (
`id` INT NOT NULL AUTO_INCREMENT ,
`blog` INT NOT NULL ,
`user` INT NOT NULL ,
`permissons` ENUM( 'read', 'write', 'admin' ) NOT NULL ,
PRIMARY KEY ( `id` )
);

SELECT *
FROM post LEFT JOIN blog ON (post.blog = blog.id) LEFT JOIN user ON (user.id = post.user) LEFT JOIN auth ON (auth.blog = post.blog AND auth.user = post.user) WHERE post.blog = 123 AND blog.hidden = 0 AND post.hidden = 0
-> returns all posts from blog 123 that are not hidden, with all infos about the author of the post and such.

SELECT post.*, blog.title, author.name AS author, IF(author.name=user.name,'author',auth.permissons) AS permissons
FROM
post
LEFT JOIN blog ON (post.blog = blog.id)
LEFT JOIN user AS author ON (author.id = post.user)
LEFT JOIN user ON (user.id = 321)
LEFT JOIN auth ON (auth.blog = post.blog AND auth.user = 321)
WHERE post.blog = 123 AND blog.hidden = 0 AND post.hidden = 0
-> returns all info about the post, the name of the blog, the name of the author (writer) of the post, the name of the current user (here it'd be 321). the variable "permissions" is "author" if the user wrote that post or else the permissions set in the auth table or NULL if there is no entry in the auth table.

hope that helps.

http://animalliberation.tk http://veganismus.ch
http://maqi.de http://tierrechtskochbuch.de