Discussion :

[olinet]

Bonjour/Bonsoir à tous & toutes,

Je développe pour mon besoin personnel, une BDD pour gérer une collection de bouquin divers qu'il m'arrive de prêter fréquemment à des amis.
J'ai créé une interface Web pour essayer de gérer plus facilement les chose.

J'ai 3 tables dans ma BDD MySQL users, livres et reservations.
Mon but : l'utilisateur entre une date et je lui retourne la liste des bouquins disponibles pour cette date.

La table users :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
 
 
CREATE TABLE IF NOT EXISTS `user` (
  `user_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_nom` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `user_prenom` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `user_adresse` varchar(256) COLLATE utf8_unicode_ci NOT NULL,
  `user_cp` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
  `user_ville` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `user_telFixe` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `user_telMob` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `user_mail` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `user_pass` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `user_groupeID` int(3) NOT NULL,
  `date_inscription` datetime NOT NULL,
  `last_connected` datetime DEFAULT NULL COMMENT 'Date de la derniere connexion au site',
  `user_status` int(3) NOT NULL COMMENT '0 ou 1, selon que l''on soit en ligne ou pas',
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `user_mail` (`user_mail`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=3 ;
 
--
-- Contenu de la table `user`
--
 
INSERT INTO `user` (`user_id`, `user_nom`, `user_prenom`, `user_adresse`, `user_cp`, `user_ville`, `user_telFixe`, `user_telMob`, `user_mail`, `user_pass`, `user_groupeID`, `date_inscription`, `last_connected`, `user_status`) VALUES
(1, 'ANIME', 'Dora', 'Cartoon club', '75000', 'PARIS', '0123456789', '0612345789', 'dora@dora.com', 'dora', 2, '2014-10-01 00:00:00', '2014-11-04 00:00:00', 0),
(2, 'ANIME', 'Totor le taureau', 'Dans le Pré', '75000', 'PARIS', '0123456789', '0612345789', 'totor@dora.com', 'totorpassword', 2, '2014-10-02 00:00:00', '2014-11-03 00:00:00', 0);

La table reservations :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
CREATE TABLE IF NOT EXISTS `reservations` (
  `resa_ID` int(6) unsigned NOT NULL AUTO_INCREMENT,
  `resa_dateDebut` date NOT NULL COMMENT 'Date de debut de location',
  `resa_dateFin` date NOT NULL COMMENT 'Date de fin de location',
  `resa_numPret` varchar(50) NOT NULL COMMENT 'Numero du pret',
  `resa_SIDAbonne` int(11) unsigned NOT NULL COMMENT 'Num ID de l_abonne',
  `resa_mailAbonne` varchar(50) NOT NULL COMMENT 'Mail de l_abonne',
  `resa_nomAbonne` varchar(50) NOT NULL COMMENT 'Nom de l_abonne',
  `resa_prenomAbonne` varchar(50) NOT NULL COMMENT 'Prenom de l_abonne',
  `resa_numMobile` varchar(21) NOT NULL COMMENT 'Telephone mobile de l_abonne',
  `resa_numFixe` varchar(21) NOT NULL COMMENT 'Telephone fixe de l_abonne',
  `resa_adressePostale` varchar(60) NOT NULL COMMENT 'Adresse postale de l_abonne',
  `resa_CP` varchar(6) NOT NULL COMMENT 'Code postal ville de l_abonne',
  `resa_vile` varchar(50) NOT NULL COMMENT 'Ville de l_abonne',
  `resa_SIDLivre` int(3) unsigned NOT NULL COMMENT 'Num ID du livre',
  `resa_TitreLivre` varchar(254) NOT NULL COMMENT 'Titre du livre',
  `resa_SsTitreLivre` varchar(254) NOT NULL COMMENT 'Sous-Titre',
  `resa_isbnLivre` varchar(14) NOT NULL COMMENT 'ISBN-13 et le tiret',
  PRIMARY KEY (`resa_ID`),
  KEY `resa_SIDAbonne` (`resa_SIDAbonne`),
  KEY `resa_SIDLivre` (`resa_SIDLivre`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;
 
--
-- Contenu de la table `reservations`
--
 
INSERT INTO `reservations` (`resa_ID`, `resa_dateDebut`, `resa_dateFin`, `resa_numPret`, `resa_SIDAbonne`, `resa_mailAbonne`, `resa_nomAbonne`, `resa_prenomAbonne`, `resa_numMobile`, `resa_numFixe`, `resa_adressePostale`, `resa_CP`, `resa_vile`, `resa_SIDLivre`, `resa_TitreLivre`, `resa_SsTitreLivre`, `resa_isbnLivre`) VALUES
(1, '2014-11-01', '2014-11-02', '20141101120059', 1, 'dora@dora.com', 'ANIME', 'Dora', '0612345789', '0123456789', 'Cartoon Club', '75000', 'PARIS', 1, 'Dora et Moi', 'Un livre bidon', '012-9384756865'),
(2, '2014-11-05', '2014-11-05', '20141102120059', 1, 'dora@dora.com', 'ANIME', 'Dora', '0612345789', '0123456789', 'Cartoon Club', '75000', 'PARIS', 1, 'Dora et Moi', 'Un livre bidon', '012-9384756865'),
(3, '2014-11-05', '2014-11-05', '20141105170058', 2, 'totor@dora.com', 'ANIME', 'Totor', '0612345789', '0123456789', 'Dans le Pré', '75000', 'PARIS', 2, 'TOUS AVEC MOI', 'Un livre de moi', '932-234567890');

La table livres :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
 
 
CREATE TABLE IF NOT EXISTS `livres` (
  `livre_ID` int(3) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Numero ID Auto-gere',
  `livre_titre` varchar(256) NOT NULL COMMENT 'Titre du livre',
  `livre_ssTitre` varchar(256) NOT NULL COMMENT 'Sous-Titre du livre',
  `livre_auteur` varchar(256) NOT NULL COMMENT 'Auteur(s)',
  `livre_nbPage` int(4) unsigned NOT NULL COMMENT 'Nombre de page',
  `livre_dateSortie` date NOT NULL COMMENT 'Date de premiere sortie',
  `livre_isbn` varchar(14) NOT NULL COMMENT 'ISBN-13 et le tiret',
  PRIMARY KEY (`livre_ID`),
  UNIQUE KEY `livre_isbn` (`livre_isbn`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;
 
--
-- Contenu de la table `livres`
--
 
INSERT INTO `livres` (`livre_ID`, `livre_titre`, `livre_ssTitre`, `livre_auteur`, `livre_nbPage`, `livre_dateSortie`, `livre_isbn`) VALUES
(1, 'Dora et moi', 'Un livre bidon', 'Mister Cartoon', 10, '2014-09-01', '012-9384756865'),
(2, 'TOUS AVEC MOI', 'Un livre de moi', 'Livre collectif', 3, '2014-08-15', '932-234567890'),
(3, 'Bientot Noel', 'A Qu''il fait froid', 'Auteur Inconnu', 5, '2014-05-01', '092-134567825');

J'y ai mis trois bouquins pour faire un test. Deux sont réservés à la date du 05-11-2014 et le dernier n'est pas réservé.

Ma requête :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT livres.livre_ID, livres.livre_isbn, reservations.resa_dateDebut AS debut, reservations.resa_dateFin AS fin, reservations.resa_SIDLivre AS BookID FROM livres INNER JOIN reservations ON reservations.resa_SIDLivre = livres.livre_ID WHERE reservations.resa_dateDebut = '2014-11-05'

Elle me retourne comme disponible les deux bouquins non disponibles pour cette date du 05-11-2014.
J'ai cherché, mais mes maigres connaissances du SQL ne me permettent pas d'aller bien loin.
est-ce la requête qui foire, la conception des tables, les deux, ou alors je suis trop fatigué et je ne vois pas l'évidence...

Merci d'avance pour vos lumières. Des suggestions, des questions, n'hésitez pas...
Micky

[skuatamad]

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

WHERE reservations.resa_dateDebut = '2014-11-05'

Ca veut juste dire quelles sont les réservations commençant le 05/11.

Pour avoir les livres disponibles, il faut une jointure externe (cas de livres jamais prêtés), le filtre doit être remonté dans la clause ON et il faut qu'il exprime "pas réservé" donc "pas entre debut et fin" :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
SELECT l.livre_ID, l.livre_isbn, r.resa_dateDebut AS debut, r.resa_dateFin AS fin, r.resa_SIDLivre AS BookID
 fROM livres l
 left JOIN reservations r ON r.resa_SIDLivre = l.livre_ID 
                         and '2014-11-05' not between r.resa_dateDebut and r.resa_dateFin

Sinon la table RESERVATIONS est effectivement mal modélisée, il ne faut pas restocker toutes les informations relatives aux user et livre, seulement les ID.
Le nom user est également un mauvais choix, c'est un mot clé (create user...), je ne sais pas s'il est réservé, mais je ne choisirai pas user comme nom d'objet.

[olinet]

Bonjour skuatamad,

Je te remercie de ta réponse.
En effet, la table reservations est mal modélisée, je vais la refaire, c'était un premier jet.

Je m'en vais de suite essayer ta requête et te tiens informé.
Cordialement,

Micky

EDIT ::
Voici le résultat que la requête me retourne:
livre_ID livre_isbn debut fin BookID
1 012-9384756865 2014-11-01 2014-11-02 1
3 092-134567825 NULL NULL NULL
2 932-234567890 NULL NULL NULL

Elle me retourne bien le livre avec le numéro ID valant 3 et qui n'est pas en prêt, mais, il me retourne aussi les deux autres qui sont en prêt ce jour-là.
Dans mon exemple, les livres avec les numéros ID 1 et 2, sont en prêt le 05-11-2014, et celui avec le numéro ID 3 n'est pas en prêt.

J'ai au moins avancé sur le fait qu'elle me retourne dans les résultats le livre qui n'est pas encore prêté. Il reste à faire le tri ou modifier la requête pour qu'elle ne retourne que, les livres dispo. En effet, ce résultat, je le mets dans une liste déroulante, pour l'utilisateur, il faut donc absolument que je n'ai que les disponibles et non un historique, sinon, je vais au-devant de problèmes.

Je continu de chercher, et si tu as une autre idée, je suis à l'écoute.

Merci encore

[skuatamad]

Oui désolé, ma requête était un peu débile, c'est un test d'existence qu'il faut faire :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
SELECT l.livre_ID, l.livre_isbn, r.resa_dateDebut AS debut, r.resa_dateFin AS fin, r.resa_SIDLivre AS BookID
  fROM livres l
  left JOIN reservations r ON r.resa_SIDLivre = l.livre_ID 
                         and '2014-11-05' not between r.resa_dateDebut and r.resa_dateFin
 where r.resa_ID is not null

Ou alors s'il n'est pas nécessaire d'afficher les dates :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
select l.*
  from livres l
 where not exists (select 1
                     from reservations r
                    where r.resa_SIDLivre = l.livre_ID 
                      and '2014-11-05' between r.resa_dateDebut and r.resa_dateFin)

[olinet]

Merci skuatamad!

L'affichage de la date n'est pas utile en effet, donc la 2nde version de la requête SQL convient.
Je suis entrain de travailler sur le site du membre SQLPro (très bien fait d'ailleurs), histoire d'aller en profondeur dans la compréhension du SQL.

Je vais étudier ta requête pour bien comprendre le raisonnement.
Cordialement,
Micky