Utilisateur:Edouard-lopez/sql

Forum (manage)

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

CREATE TABLE `category` ( `id` INT NOT NULL AUTO_INCREMENT , `label` CHAR( 200 ) NOT NULL , PRIMARY KEY ( `id` ) ) TYPE = MYISAM ;

Topic

CREATE TABLE `topic` ( `id` INT NOT NULL AUTO_INCREMENT , `label` VARCHAR( 200 ) NOT NULL , `category` INT NOT NULL , PRIMARY KEY ( `id` ) ) TYPE = MYISAM ;

Thread

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

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

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

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

CREATE TABLE `pass` ( `id` INT NOT NULL AUTO_INCREMENT , `label` VARCHAR( 40 ) NOT NULL , PRIMARY KEY ( `id` ) ) TYPE = MYISAM ;

Referent

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

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

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

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

SELECT * FROM `visit` WHERE DATE_SUB( CURDATE( ) , INTERVAL 30 DAY ) <= `date` LIMIT 0 , 50

LINK

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 ;

PHP/javascript

javascript/php