Eviter que "user" supprime ses services,

  1. Accueil
  2. Forum
  3. Programmation
  4. SQL
  5. Eviter que "user" supprime ses services,
Suivre ce topic
Ce topic est suivi par : assakoxav
assakoxav

assakoxav Le 17 septembre 2017 à 20:18 (Édité le 25 janvier 2019 à 17:54)

Bonjour, je développe une application web de gestion d'hôtel et j'ai besoin d'aide pour désactiver la fonction supprimer chez les User de façon à ce que seul l'admin puisse supprimer un service. Voici ma BD que j'aimerais qu'on m'aide à modifier : 
--
-- Base de données: `room_manager`
--
--
-- Structure de la table `customers`
--
CREATE TABLE IF NOT EXISTS `customers` (
  `ID_CUSTOMER` bigint(4) NOT NULL AUTO_INCREMENT,
  `NAME` varchar(255) DEFAULT NULL,
  `SURNAME` varchar(255) DEFAULT NULL,
  `EMAIL` varchar(255) DEFAULT NULL,
  `PHONE` char(32) DEFAULT NULL,
  `CARD_TYPE` varchar(255) DEFAULT NULL,
  `CARD_NUMBER` varchar(255) DEFAULT NULL,
  `SEX` char(32) DEFAULT NULL,
  `DATE_POST` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`ID_CUSTOMER`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COMMENT='managing users' AUTO_INCREMENT=3 ;
--
-- Contenu de la table `customers`
--
INSERT INTO `customers` (`ID_CUSTOMER`, `NAME`, `SURNAME`, `EMAIL`, `PHONE`, `CARD_TYPE`, `CARD_NUMBER`, `SEX`, `DATE_POST`) VALUES
(1, 'baby', 'doe', 'bdoe@tyjau.com', '', 'cni', '123456', 'f', '1446554218'),
(2, 'MEBA', 'Adolphe', 'mebs@madmin.com', '69465873', 'CNI', '78976546', 'm', '1448548620');
-- --------------------------------------------------------
--
-- Structure de la table `invoices`
--
CREATE TABLE IF NOT EXISTS `invoices` (
  `ID_INVOICE` bigint(4) NOT NULL AUTO_INCREMENT,
  `ID_ROOM` bigint(4) NOT NULL,
  `ID_CUSTOMER` bigint(4) NOT NULL,
  `DATE_START` char(32) DEFAULT NULL,
  `DATE_END` char(32) DEFAULT NULL,
  `NUITIES_NUMB` int(11) NOT NULL,
  `DATE_POST` char(32) DEFAULT NULL,
  PRIMARY KEY (`ID_INVOICE`),
  KEY `I_FK_INVOICES_ROOMS` (`ID_ROOM`),
  KEY `I_FK_INVOICES_CUSTOMERS` (`ID_CUSTOMER`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=37 ;
--
-- Contenu de la table `invoices`
--
INSERT INTO `invoices` (`ID_INVOICE`, `ID_ROOM`, `ID_CUSTOMER`, `DATE_START`, `DATE_END`, `NUITIES_NUMB`, `DATE_POST`) VALUES
(36, 2, 2, '2015/11/26 15:37', '2015/12/03 15:37', 7, '1448548672');
-- --------------------------------------------------------
--
-- Structure de la table `managers`
--
CREATE TABLE IF NOT EXISTS `managers` (
  `ID_MANAGER` bigint(4) NOT NULL AUTO_INCREMENT,
  `ID_ROLE` int(11) NOT NULL,
  `NAME` varchar(255) DEFAULT NULL,
  `EMAIL` varchar(255) DEFAULT NULL,
  `PHONE` char(32) DEFAULT NULL,
  `PASS` varchar(255) DEFAULT NULL,
  `STATUE` char(32) DEFAULT NULL,
  `DATE_POST` char(32) DEFAULT NULL,
  PRIMARY KEY (`ID_MANAGER`),
  KEY `ID_ROLE` (`ID_ROLE`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
--
-- Contenu de la table `managers`
--
INSERT INTO `managers` (`ID_MANAGER`, `ID_ROLE`, `NAME`, `EMAIL`, `PHONE`, `PASS`, `STATUE`, `DATE_POST`) VALUES
(1, 2, 'john doe', 'john.doe@madmin.com', NULL, '527bd5b5d689e2c32ae974c6229ff785', 'on', '123464564'),
(2, 1, 'admin', 'admin@madmin.com', '', '21232f297a57a5a743894a0e4a801fc3', 'on', '1448548179');
-- --------------------------------------------------------
--
-- Structure de la table `roles`
--
CREATE TABLE IF NOT EXISTS `roles` (
  `ID_ROLE` int(11) NOT NULL AUTO_INCREMENT,
  `NAME` varchar(255) NOT NULL,
  PRIMARY KEY (`ID_ROLE`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COMMENT='managing users roles and rights' AUTO_INCREMENT=3 ;
--
-- Contenu de la table `roles`
--
INSERT INTO `roles` (`ID_ROLE`, `NAME`) VALUES
(1, 'admin'),
(2, 'user');
-- --------------------------------------------------------
--
-- Structure de la table `rooms`
--
CREATE TABLE IF NOT EXISTS `rooms` (
  `ID_ROOM` bigint(4) NOT NULL AUTO_INCREMENT,
  `ID_STANDING` bigint(4) NOT NULL,
  `NAME` char(32) DEFAULT NULL,
  `STATUE` char(32) DEFAULT NULL,
  `DATE_POST` char(32) DEFAULT NULL,
  PRIMARY KEY (`ID_ROOM`),
  KEY `I_FK_ROOMS_STANDINGS` (`ID_STANDING`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
--
-- Contenu de la table `rooms`
--
INSERT INTO `rooms` (`ID_ROOM`, `ID_STANDING`, `NAME`, `STATUE`, `DATE_POST`) VALUES
(1, 1, '112', 'on', '1446554140'),
(2, 2, '112 - MEBA', 'on', '1448548577');
-- --------------------------------------------------------
--
-- Structure de la table `standings`
--
CREATE TABLE IF NOT EXISTS `standings` (
  `ID_STANDING` bigint(4) NOT NULL AUTO_INCREMENT,
  `NAME` char(32) DEFAULT NULL,
  `PRICE` char(32) DEFAULT NULL,
  `DATE_POST` char(32) DEFAULT NULL,
  PRIMARY KEY (`ID_STANDING`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
--
-- Contenu de la table `standings`
--
INSERT INTO `standings` (`ID_STANDING`, `NAME`, `PRICE`, `DATE_POST`) VALUES
(1, 'junior', '15000', '1446554128'),
(2, 'junior - MEBA', '10000', '1448548537');
--
-- Contraintes pour les tables exportées
--
--
-- Contraintes pour la table `invoices`
--
ALTER TABLE `invoices`
  ADD CONSTRAINT `invoices_ibfk_1` FOREIGN KEY (`ID_ROOM`) REFERENCES `rooms` (`ID_ROOM`),
  ADD CONSTRAINT `invoices_ibfk_2` FOREIGN KEY (`ID_CUSTOMER`) REFERENCES `customers` (`ID_CUSTOMER`);
--
-- Contraintes pour la table `managers`
--
ALTER TABLE `managers`
  ADD CONSTRAINT `MANAGER_ROLE` FOREIGN KEY (`ID_ROLE`) REFERENCES `roles` (`ID_ROLE`);
--
-- Contraintes pour la table `rooms`
--
ALTER TABLE `rooms`
  ADD CONSTRAINT `rooms_ibfk_1` FOREIGN KEY (`ID_STANDING`) REFERENCES `standings` (`ID_STANDING`);
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Merci d'avance
assakoxav

assakoxav Le 25 septembre 2017 à 19:25 

je pensais qu'on pourrait tout simplement modifier : primary key et foreign key et quelques contraintes,

voici ce que j'ai pu faire comme amélioration avec un peu d'aide: 

-- phpMyAdmin SQL Dump

-- version 4.0.4

-- http://www.phpmyadmin.net

--

-- Client: localhost

-- Généré le: Jeu 26 Novembre 2015 à 14:39

-- Version du serveur: 5.6.12-log

-- Version de PHP: 5.4.12

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";

SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

/*!40101 SET NAMES utf8 */;

--

-- Base de données: `room_manager`

--

CREATE DATABASE IF NOT EXISTS `room_manager` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;

USE `room_manager`;

-- --------------------------------------------------------

--

-- Structure de la table `customers`

--

CREATE TABLE IF NOT EXISTS `customers` (

  `ID_CUSTOMER` bigint(4) NOT NULL AUTO_INCREMENT,

  `NAME` varchar(255) DEFAULT NULL,

  `SURNAME` varchar(255) DEFAULT NULL,

  `EMAIL` varchar(255) DEFAULT NULL,

  `PHONE` char(32) DEFAULT NULL,

  `CARD_TYPE` varchar(255) DEFAULT NULL,

  `CARD_NUMBER` varchar(255) DEFAULT NULL,

  `SEX` char(32) DEFAULT NULL,

  `DATE_POST` varchar(255) DEFAULT NULL,

  PRIMARY KEY (`ID_CUSTOMER`)

) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COMMENT='managing users' AUTO_INCREMENT=3 ;

--

-- Contenu de la table `customers`

--

INSERT INTO `customers` (`ID_CUSTOMER`, `NAME`, `SURNAME`, `EMAIL`, `PHONE`, `CARD_TYPE`, `CARD_NUMBER`, `SEX`, `DATE_POST`) VALUES

(1, 'baby', 'doe', 'bdoe@tyjau.com', '', 'cni', '123456', 'f', '1446554218'),

(2, 'MEBA', 'Adolphe', 'mebs@madmin.com', '69465873', 'CNI', '78976546', 'm', '1448548620');

-- --------------------------------------------------------

--

-- Structure de la table `invoices`

--

CREATE TABLE IF NOT EXISTS `invoices` (

  `ID_INVOICE` bigint(4) NOT NULL AUTO_INCREMENT,

  `ID_ROOM` bigint(4) NOT NULL,

  `ID_CUSTOMER` bigint(4) NOT NULL,

  `ID_EMPLOYEES` bigint(4) NOT NULL,

  `DATE_START` char(32) DEFAULT NULL,

  `DATE_END` char(32) DEFAULT NULL,

  `NUITIES_NUMB` int(11) NOT NULL,

  `DATE_POST` char(32) DEFAULT NULL,

  PRIMARY KEY (`ID_INVOICE`),

  KEY `I_FK_INVOICES_ROOMS` (`ID_ROOM`),

  KEY `I_FK_INVOICES_CUSTOMERS` (`ID_CUSTOMER`)

) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=37 ;

--

-- Contenu de la table `invoices`

--

INSERT INTO invoices ( ID_INVOICE, ID_ROOM, ID_CUSTOMER, ID_EMPLOYEES, DATE_START, DATE_END, NUITIES_NUMB, DATE_POST) VALUES 

(2, 2, 3, ’2015/11/26 15:37’, ’2015/12/03 15:37’, 7, ’1448548672’),

(36, 2, 2, ’2015/11/26 15:37’, ’2015/12/03 15:37’, 7, ’1448548672’);

-- --------------------------------------------------------

--

-- Structure de la table `employees`

--

CREATE TABLE IF NOT EXISTS `employees` (

  `ID_EMPLOYEES` bigint(4) NOT NULL AUTO_INCREMENT,

  `ID_ROLE` int(11) NOT NULL,

  `NAME` varchar(255) DEFAULT NULL,

  `EMAIL` varchar(255) DEFAULT NULL,

  `PHONE` char(32) DEFAULT NULL,

  `PASS` varchar(255) DEFAULT NULL,

  `STATUE` char(32) DEFAULT NULL,

  `DATE_POST` char(32) DEFAULT NULL,

  PRIMARY KEY (`ID_EMPLOYEES`),

  KEY `ID_ROLE` (`ID_ROLE`)

) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--

-- Contenu de la table `employees`

--

INSERT INTO `employees` (`ID_EMPLOYEES`, `ID_ROLE`, `NAME`, `EMAIL`, `PHONE`, `PASS`, `STATUE`, `DATE_POST`) VALUES

(1, 3, 'john doe', 'john.doe@madmin.com', NULL, '527bd5b5d689e2c32ae974c6229ff785', 'on', '123464564'),

(2, 3, 'admin', 'admin@madmin.com', '', '21232f297a57a5a743894a0e4a801fc3', 'on', '1448548179');

(3, 3, 'clem antine', 'clem@antine.com', '', '21232f297a57a5a743894a0e4a801fc3', 'on', '1448548179');

-- --------------------------------------------------------

--

-- Structure de la table `roles`

--

CREATE TABLE IF NOT EXISTS `roles` (

  `ID_ROLE` int(11) NOT NULL AUTO_INCREMENT,

  `NAME` varchar(255) NOT NULL,

  PRIMARY KEY (`ID_ROLE`)

) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COMMENT='managing users roles and rights' AUTO_INCREMENT=3 ;

--

-- Contenu de la table `roles`

--

INSERT INTO `roles` (`ID_ROLE`, `NAME`) VALUES

(1, 'admin'),

(2, 'user');

(3, 'receptionist');

-- --------------------------------------------------------

--

-- Structure de la table `rooms`

--

CREATE TABLE IF NOT EXISTS `rooms` (

  `ID_ROOM` bigint(4) NOT NULL AUTO_INCREMENT,

  `ID_STANDING` bigint(4) NOT NULL,

  `NAME` char(32) DEFAULT NULL,

  `STATUE` char(32) DEFAULT NULL,

  `DATE_POST` char(32) DEFAULT NULL,

  PRIMARY KEY (`ID_ROOM`),

  KEY `I_FK_ROOMS_STANDINGS` (`ID_STANDING`)

) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--

-- Contenu de la table `rooms`

--

INSERT INTO `rooms` (`ID_ROOM`, `ID_STANDING`, `NAME`, `STATUE`, `DATE_POST`) VALUES

(1, 1, '112', 'on', '1446554140'),

(2, 2, '112 - MEBA', 'on', '1448548577');

-- --------------------------------------------------------

--

-- Structure de la table `standings`

--

CREATE TABLE IF NOT EXISTS `standings` (

  `ID_STANDING` bigint(4) NOT NULL AUTO_INCREMENT,

  `NAME` char(32) DEFAULT NULL,

  `PRICE` char(32) DEFAULT NULL,

  `DATE_POST` char(32) DEFAULT NULL,

  PRIMARY KEY (`ID_STANDING`)

) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--

-- Contenu de la table `standings`

--

INSERT INTO `standings` (`ID_STANDING`, `NAME`, `PRICE`, `DATE_POST`) VALUES

(1, 'junior', '15000', '1446554128'),

(2, 'junior - MEBA', '10000', '1448548537');

SELECT r.name as room_name, e.name as employees_name, i.DATE_START, i.DATE_END, i.NUITIES_NUMB 

FROM rooms r

INNER JOIN invoices i USING(ID_ROOM)

INNER JOIN employees e USING(ID_EMPLOYEES)

--

-- Contraintes pour les tables exportées

--

--

-- Contraintes pour la table `invoices`

--

ALTER TABLE `invoices`

  ADD CONSTRAINT `invoices_ibfk_1` FOREIGN KEY (`ID_ROOM`) REFERENCES `rooms` (`ID_ROOM`),

  ADD CONSTRAINT `invoices_ibfk_2` FOREIGN KEY (`ID_CUSTOMER`) REFERENCES `customers` (`ID_CUSTOMER`);

--

-- Contraintes pour la table `employees`

--

ALTER TABLE `employees`

  ADD CONSTRAINT `EMPLOYEES_ROLE` FOREIGN KEY (`ID_ROLE`) REFERENCES `roles` (`ID_ROLE`);

--

-- Contraintes pour la table `rooms`

--

ALTER TABLE `rooms`

  ADD CONSTRAINT `rooms_ibfk_1` FOREIGN KEY (`ID_STANDING`) REFERENCES `standings` (`ID_STANDING`);

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;

/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Nous avons changé
Mise en application?:

Dans la table roles, nous avons ajouté un rôle "receptionist"
1
2
INSERT INTO `roles` (`ID_ROLE`, `NAME`) VALUES
(3, 'receptionist');
Ensuite, nous avons inséré un employée qui a un id 3
1
2
INSERT INTO `employees` (`ID_EMPLOYEES`, `ID_ROLE`, `NAME`, `EMAIL`, `PHONE`, `PASS`, `STATUE`, `DATE_POST`) VALUES
(3, 3, 'clem antine', 'clem@antine.com', '', '21232f297a57a5a743894a0e4a801fc3', 'on', '1448548179');
nous avons ajouté une nouvelle réservation (et j’ai modifié les lignes existantes pour mettre l’id de l’employé) 
Le code d’insertion
1
2
INSERT INTO `invoices` (`ID_ROOM`, `ID_CUSTOMER`, `ID_EMPLOYEES`, `DATE_START`, `DATE_END`, `NUITIES_NUMB`, `DATE_POST`) VALUES
(2, 2, 3, '2015/11/26 15:37', '2015/12/03 15:37', 7, '1448548672')
On peut ainsi faire une requête avec les informations de la réservation

1
2
3
4
SELECT r.name as room_name, e.name as employees_name, i.DATE_START, i.DATE_END, i.NUITIES_NUMB 
FROM rooms r
INNER JOIN invoices i USING(ID_ROOM)
INNER JOIN employees e USING(ID_EMPLOYEES)
room_name	employees_name	DATE_START	DATE_END	NUITIES_NUMB
112 - MEBA	clem antine	2015/11/26 15:37	2015/12/03 15:37	7
Vous devez être connecté pour poster une réponse. Se connecter ou Créer un compte

Programmation, développement web, high-tech, etc. Je partage ma passion pour l'informatique à travers des tutos vidéos et articles en espérant qu'ils vous soient utiles ;-)

Échange
Articles / Vidéos
Ressources

© 2024 PrimFX.com. Tous droits réservés. Propulsé par Single Quote.