Utilisateur:Edouard-lopez/sql
Forum (manage)[modifier | modifier le code]
voir table: message
CREATE TABLE `forum2` (
`id` INT NOT NULL AUTO_INCREMENT ,
`category` INT NOT NULL ,
`topic` INT NOT NULL ,
`thread` INT NOT NULL ,
`message` INT NOT NULL ,
`user` INT NOT NULL ,
`date` DATE NOT NULL ,
`view` INT NOT NULL ,
PRIMARY KEY ( `id` ) ,
INDEX ( `category` , `topic` , `thread` , `message` ) ,
UNIQUE (
`user`
)
) TYPE = MYISAM ;
Category[modifier | modifier le code]
CREATE TABLE `category` ( `id` INT NOT NULL AUTO_INCREMENT , `label` CHAR( 200 ) NOT NULL , PRIMARY KEY ( `id` ) ) TYPE = MYISAM ;
Topic[modifier | modifier le code]
CREATE TABLE `topic` ( `id` INT NOT NULL AUTO_INCREMENT , `label` VARCHAR( 200 ) NOT NULL , `category` INT NOT NULL , PRIMARY KEY ( `id` ) ) TYPE = MYISAM ;
Thread[modifier | modifier le code]
CREATE TABLE `thread` ( `id` INT NOT NULL AUTO_INCREMENT , `label` CHAR( 200 ) NOT NULL , `category` INT NOT NULL , `topic` INT NOT NULL , `view` INT NOT NULL , PRIMARY KEY ( `id` ) ) TYPE = MYISAM ;
Message[modifier | modifier le code]
CREATE TABLE `message` ( `id` INT NOT NULL AUTO_INCREMENT , `label` TEXT NOT NULL , `category` INT NOT NULL , `topic` INT NOT NULL , `thread` INT NOT NULL , `answer` INT NOT NULL , `user` INT NOT NULL , `date` DATETIME NOT NULL DEFAULT , PRIMARY KEY ( `id` ) ) TYPE = MYISAM ;
Users[modifier | modifier le code]
CREATE TABLE `users` ( `id` INT NOT NULL AUTO_INCREMENT , `label` CHAR( 100 ) NOT NULL , `pass` INT NOT NULL , `email` CHAR( 75 ) NOT NULL , `url` CHAR( 100 ) NOT NULL , `contact` CHAR( 150 ) NOT NULL , `signature` CHAR( 300 ) NOT NULL , `location` CHAR( 75 ) NOT NULL , `avatar` INT NOT NULL , `mood` INT NOT NULL , `register` DATETIME NOT NULL , PRIMARY KEY ( `id` ) , UNIQUE ( `label` ) ) TYPE = MYISAM ;
Query[modifier | modifier le code]
SELECT category.label AS category, topic.label AS topic, thread.label AS thread, thread.view, message.answer, message.date AS msg_date, users.label AS 'by', users.email, users.signature, users.location, users.avatar, users.register AS member_since FROM `category` , `topic` , `thread` , `message` , `users` WHERE message.thread = thread.id AND message.topic = topic.id AND message.user = users.id AND message.date <2000 -02 -26 LIMIT 0 , 30
Pass[modifier | modifier le code]
CREATE TABLE `pass` ( `id` INT NOT NULL AUTO_INCREMENT , `label` VARCHAR( 40 ) NOT NULL , PRIMARY KEY ( `id` ) ) TYPE = MYISAM ;
Referent[modifier | modifier le code]
CREATE TABLE `referer` ( `id` INT NOT NULL AUTO_INCREMENT , `label` VARCHAR( 200 ) NOT NULL , `date` DATE NOT NULL , `nb` INT NOT NULL , PRIMARY KEY ( `id` ) , UNIQUE ( `label` ) ) TYPE = MYISAM ;
Browser[modifier | modifier le code]
CREATE TABLE `browser` ( `id` INT NOT NULL AUTO_INCREMENT , `label` VARCHAR( 25 ) NOT NULL , `date` DATE NOT NULL , `nb` INT NOT NULL , PRIMARY KEY ( `id` ) , UNIQUE ( `label` ) ) TYPE = MYISAM ;
OS[modifier | modifier le code]
CREATE TABLE `os` ( `id` INT NOT NULL AUTO_INCREMENT , `label` VARCHAR( 25 ) NOT NULL , `date` DATE NOT NULL , `nb` INT NOT NULL , PRIMARY KEY ( `id` ) , UNIQUE ( `label` ) ) TYPE = MYISAM ;
Submit[modifier | modifier le code]
CREATE TABLE `submit` ( `id` INT NOT NULL AUTO_INCREMENT , `label` VARCHAR( 100 ) NOT NULL, `date` DATE NOT NULL , PRIMARY KEY ( `id` ) ) TYPE = MYISAM ;
SELECT intervalle de 30 jours[modifier | modifier le code]
SELECT * FROM `visit` WHERE DATE_SUB( CURDATE( ) , INTERVAL 30 DAY ) <= `date` LIMIT 0 , 50
LINK[modifier | modifier le code]
CREATE TABLE `link` ( `id` INT NOT NULL AUTO_INCREMENT , `label` VARCHAR( 200 ) NOT NULL , `banner` VARCHAR( 50 ) NOT NULL , `lang` VARCHAR( 3 ) NOT NULL , `type` VARCHAR( 15 ) NOT NULL , PRIMARY KEY ( `id` ) ) TYPE = MYISAM ;