ABDELKARIM ELHALLOUMI OMAR SALIMI Table des matières (gras centré caractères 24 Times new roman) . . Partie 1. Contexte général du projet 1.1 Cahier des charges 1.2 Diagramme de classes Partie 2. Manipulation de la base de données SQLServer en PHP 2.1 Passage du Diagramme de classes au modèle relationnel . . . . 2.1.1 Présentation de l’outil utilisé . 2.1.2 Présentation du script de la base de données . 2.2 Connexion à une base de données SQL server à partir du PHP . 2.2.1 configuration de l’outil utilisé pour assurer la connexion . 2.2.2 Présentation du script de connexion . 2.3 Exemple d’opérations CRUD SQLServer/PHP . 2.3.1 Présentation de la table choisie . 2.3.2 Présentation des scripts CRUD . Références Annexes . . Partie 1. Contexte général du projet 1.1 Cahier des charges Notre Mission dans le cadre de ce projet est de créer une application permettant de gérer les consultation des patients et genere un oradanance par web il s'agit de définir les responsabilités de la gestion, mettre à jour les données, organiser des données collectées auprès du secrétariat afin de concevoir des fichiers de bases pour le Médecin , de renforcer le contrôle et la confrontation, assurer une meilleure gestion médicale et une cohérence de l'information et enfin faciliter le travail des responsables. Notre application aura comme principale fonctionnalités : v Gestion et Suivi du Dossier Médical (détaillé) v Gestion du Fiche Patients. 1.2 Diagramme de classes 1 Partie 2. Manipulation de la base de données SQLServer en PHP 2.1 Passage du Diagramme de classes au modèle relationnel 2.1.1 Génération du script de la base de données CREATE Database [DiagnosticLabDb]; GO USE [DiagnosticLabDb] /****** Object: Table [dbo].[cc_and_oe] Script Date: 6/29/2020 8:23:09 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[cc_and_oe]( [ccoe_id] [int] IDENTITY(1,1) NOT NULL, [cc] [varchar](999) NOT NULL, [oe] [varchar](999) NOT NULL, [patient_id] [int] NOT NULL, 2 [status] [int] NULL, CONSTRAINT [PK__cc_and_o__B878B649AB7FF6E5] PRIMARY KEY CLUSTERED ( [ccoe_id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Table [dbo].[diag_symptoms] Script Date: 6/29/2020 8:23:09 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[diag_symptoms]( [dg_id] [int] IDENTITY(1,1) NOT NULL, [patient_id] [int] NOT NULL, [symptom_id] [int] NOT NULL, [symptom_time] [varchar](10) NOT NULL, CONSTRAINT [PK__diag_sym__4553E013C0A93B92] PRIMARY KEY CLUSTERED ( [dg_id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Table [dbo].[diagnosis_diseases] Script Date: 6/29/2020 8:23:09 AM ******/ SET ANSI_NULLS ON GO 3 SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[diagnosis_diseases]( [id] [int] IDENTITY(1,1) NOT NULL, [patient_id] [int] NOT NULL, [disease_name] [varchar](50) NOT NULL, [disease_id] [int] NOT NULL, CONSTRAINT [PK__diagnosi__3213E83F14DAD400] PRIMARY KEY CLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Table [dbo].[disease] Script Date: 6/29/2020 8:23:09 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[disease]( [disease_id] [int] IDENTITY(1,1) NOT NULL, [name] [varchar](255) NOT NULL, [description] [varchar](max) NOT NULL, CONSTRAINT [PK__disease__15627065BC935059] PRIMARY KEY CLUSTERED ( [disease_id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] 4 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO /****** Object: Table [dbo].[doctor] Script Date: 6/29/2020 8:23:09 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[doctor]( [id] [int] IDENTITY(1,1) NOT NULL, [username] [varchar](255) NOT NULL, [password] [varchar](200) NOT NULL, [email] [varchar](255) NOT NULL, [create_at] [datetime2](0) NOT NULL, [lock_user] [int] NULL, CONSTRAINT [PK__doctor__3213E83F88A5DD57] PRIMARY KEY CLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Table [dbo].[doctor_infos] Script Date: 6/29/2020 8:23:09 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[doctor_infos]( 5 [doctor_id] [int] NOT NULL, [doctor_name] [varchar](255) NOT NULL, [adress] [varchar](255) NOT NULL, [expertise] [varchar](255) NOT NULL, [chamber_name] [varchar](255) NOT NULL) CREATE UNIQUE INDEX index_doctor_id ON [dbo].[doctor_infos] (doctor_id); GO /****** Object: Table [dbo].[medicines] Script Date: 6/29/2020 8:23:09 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[medicines]( [medicine_id] [int] IDENTITY(1,1) NOT NULL, [medicine_name] [varchar](255) NOT NULL, [medicine_group] [varchar](255) NOT NULL, [power] [int] NOT NULL, [medicine_type] [varchar](255) NOT NULL, [company_name] [varchar](255) NOT NULL, CONSTRAINT [PK__medicine__E7148EBB1646909B] PRIMARY KEY CLUSTERED ( [medicine_id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO 6 /****** Object: Table [dbo].[note] Script Date: 6/29/2020 8:23:09 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[note]( [patient_id] [int] NOT NULL, [note] [varchar](max) NOT NULL ) CREATE UNIQUE INDEX index_patient_id ON [dbo].[note] (patient_id); GO /****** Object: Table [dbo].[prescribed_medicine] Script Date: 6/29/2020 8:23:09 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[prescribed_medicine]( [pm_id] [int] IDENTITY(1,1) NOT NULL, [patient_id] [int] NOT NULL, [medicine_id] [int] NOT NULL, [medicine_time] [varchar](20) NOT NULL, CONSTRAINT [PK__prescrib__26B10336955AC899] PRIMARY KEY CLUSTERED ( [pm_id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] 7 GO /****** Object: Table [dbo].[prescriptions] Script Date: 6/29/2020 8:23:09 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[prescriptions]( [prescription_id] [int] IDENTITY(1,1) NOT NULL, [medicine_id] [int] NOT NULL, [symptom_id] [int] NOT NULL, [patient_id] [int] NOT NULL, [medicine_time] [varchar](20) NOT NULL, [disease_id] [int] NOT NULL, [note] [varchar](max) NOT NULL, [prescription_date] [datetime2](0) NOT NULL, CONSTRAINT [PK__prescrip__3EE444F8B3372199] PRIMARY KEY CLUSTERED ( [prescription_id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO /****** Object: Table [dbo].[symptoms] Script Date: 6/29/2020 8:23:09 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO 8 CREATE TABLE [dbo].[symptoms]( [symptom_id] [int] IDENTITY(1,1) NOT NULL, [symptom_name] [varchar](255) NOT NULL, [symptom_type] [varchar](255) NOT NULL, [description] [varchar](255) NOT NULL, CONSTRAINT [PK__symptoms__7A85ADB834E5B77E] PRIMARY KEY CLUSTERED ( [symptom_id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Table [dbo].[users] Script Date: 6/29/2020 8:23:09 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[users]( [user_id] [int] IDENTITY(1,1) NOT NULL, [firstName] [varchar](255) NOT NULL, [lastName] [varchar](200) NOT NULL, [age] [int] NOT NULL, [gender] [varchar](20) NOT NULL, [email] [varchar](255) NOT NULL, [contact] [varchar](20) NOT NULL, [password] [varchar](255) NOT NULL, [blood_group] [varchar](20) NOT NULL, [address] [varchar](999) NOT NULL, [user_type] [varchar](20) NOT NULL, 9 [lock_user] [int] NULL, CONSTRAINT [PK__users__B9BE370F138A85B7] PRIMARY KEY CLUSTERED ( [user_id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO INSERT [dbo].[cc_and_oe] ( [cc], [oe], [patient_id], [status]) VALUES ( N'ABC', N'DEF', 3, 0) INSERT [dbo].[cc_and_oe] ( [cc], [oe], [patient_id], [status]) VALUES ( N'Foods', N'Medicines', 1, 1) INSERT [dbo].[cc_and_oe] ( [cc], [oe], [patient_id], [status]) VALUES ( N'Foods', N'Foods', 2, 0) INSERT [dbo].[cc_and_oe] ( [cc], [oe], [patient_id], [status]) VALUES ( N'Food 2', N'Medicine 2', 3, 0) INSERT [dbo].[cc_and_oe] ( [cc], [oe], [patient_id], [status]) VALUES ( N'A', N'A', 2, 0) INSERT [dbo].[cc_and_oe] ( [cc], [oe], [patient_id], [status]) VALUES ( N'A', N'A', 2, 0) INSERT [dbo].[cc_and_oe] ( [cc], [oe], [patient_id], [status]) VALUES ( N'A', N'A', 2, 1) INSERT [dbo].[cc_and_oe] ( [cc], [oe], [patient_id], [status]) VALUES ( N'Abc', N'Def', 3, 0) INSERT [dbo].[cc_and_oe] ( [cc], [oe], [patient_id], [status]) VALUES ( N'New', N'New', 3, 1) INSERT [dbo].[cc_and_oe] ( [cc], [oe], [patient_id], [status]) VALUES ( N'FeverrnCold', N'Total Bed Rest', 11, 1) INSERT [dbo].[cc_and_oe] ( [cc], [oe], [patient_id], [status]) VALUES ( N'ddd', N'dddd', 12, 1) INSERT [dbo].[cc_and_oe] ( [cc], [oe], [patient_id], [status]) VALUES ( N'dddd', N'dddd', 18, 0) INSERT [dbo].[cc_and_oe] ( [cc], [oe], [patient_id], [status]) VALUES ( N'ddd', N'ddd', 18, 0) 10 INSERT [dbo].[cc_and_oe] ( [cc], [oe], [patient_id], [status]) VALUES ( N'ddd', N'dddd', 18, 0) INSERT [dbo].[cc_and_oe] ( [cc], [oe], [patient_id], [status]) VALUES ( N'ddd', N'ddd', 18, 0) INSERT [dbo].[cc_and_oe] ( [cc], [oe], [patient_id], [status]) VALUES ( N'ddd', N'ddd', 18, 0) INSERT [dbo].[cc_and_oe] ( [cc], [oe], [patient_id], [status]) VALUES ( N'ddd', N'dddd', 17, 1) INSERT [dbo].[cc_and_oe] ( [cc], [oe], [patient_id], [status]) VALUES ( N'ddd', N'dddd', 18, 0) INSERT [dbo].[cc_and_oe] ( [cc], [oe], [patient_id], [status]) VALUES ( N'dddddddddddd', N'dddddddddddd', 18, 0) INSERT [dbo].[cc_and_oe] ( [cc], [oe], [patient_id], [status]) VALUES ( N'dddddddddddddddddddddddddddddddddddddddddddddddddd', N'ddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd', 18, 0) INSERT [dbo].[cc_and_oe] ( [cc], [oe], [patient_id], [status]) VALUES ( N'dddddddddddddd', N'ddddddddddddddddd', 18, 0) INSERT [dbo].[cc_and_oe] ( [cc], [oe], [patient_id], [status]) VALUES ( N'dddddddddd', N'ddddddddddddddd', 18, 0) INSERT [dbo].[cc_and_oe] ( [cc], [oe], [patient_id], [status]) VALUES ( N'dddddddddd', N'dddddddddddd', 18, 0) INSERT [dbo].[cc_and_oe] ( [cc], [oe], [patient_id], [status]) VALUES ( N'dddddddd', N'ddddddddd', 18, 0) INSERT [dbo].[cc_and_oe] ( [cc], [oe], [patient_id], [status]) VALUES ( N'kkkkkkkkkkkkkk', N'kkkkkkkkkkkkkkkk', 18, 1) Go INSERT [dbo].[diag_symptoms] ( [patient_id], [symptom_id], [symptom_time]) VALUES ( 1, 1, N'10:26:38') INSERT [dbo].[diag_symptoms] ( [patient_id], [symptom_id], [symptom_time]) VALUES ( 1, 20, N'09:48:04') INSERT [dbo].[diag_symptoms] ( [patient_id], [symptom_id], [symptom_time]) VALUES ( 1, 21, N'07:45:35') 11 Go INSERT [dbo].[diagnosis_diseases] ( [patient_id], [disease_name], [disease_id]) VALUES ( 1, N'Skin Elergy', 0) INSERT [dbo].[diagnosis_diseases] ( [patient_id], [disease_name], [disease_id]) VALUES ( 1, N'Diabtiease', 0) INSERT [dbo].[diagnosis_diseases] ( [patient_id], [disease_name], [disease_id]) VALUES ( 1, N'Abdominal Aortic Aneurysm', 0) INSERT [dbo].[diagnosis_diseases] ( [patient_id], [disease_name], [disease_id]) VALUES ( 1, N'Cancer', 0) INSERT [dbo].[diagnosis_diseases] ( [patient_id], [disease_name], [disease_id]) VALUES ( 1, N'Blood Presure', 0) INSERT [dbo].[diagnosis_diseases] ( [patient_id], [disease_name], [disease_id]) VALUES ( 1, N'Head Pain', 12) Go INSERT [dbo].[disease] ( [name], [description]) VALUES (N'Skin Elergy', N'asde') INSERT [dbo].[disease] ( [name], [description]) VALUES (N'Skin Elergy', N'') INSERT [dbo].[disease] ( [name], [description]) VALUES (N'Diabtiease', N'') INSERT [dbo].[disease] ( [name], [description]) VALUES (N'Blood Presure', N'') INSERT [dbo].[disease] ( [name], [description]) VALUES (N'Abdominal Aortic Aneurysm', N'Abdominal Aortic Aneurysm') INSERT [dbo].[disease] ( [name], [description]) VALUES (N'Abdominal Cramps (Heat Cramps)', N'Abdominal Cramps (Heat Cramps)') INSERT [dbo].[disease] ( [name], [description]) VALUES (N'Back Pain (Low Back Pain)', N'Back Pain (Low Back Pain)') INSERT [dbo].[disease] ( [name], [description]) VALUES ( N'Barium Enema', N'Barium Enema') INSERT [dbo].[disease] ( [name], [description]) VALUES ( N'COld', N'') INSERT [dbo].[disease] ( [name], [description]) VALUES ( N'Head Pain', N'') INSERT [dbo].[disease] ( [name], [description]) VALUES ( N'Fever', N'dfsdf') INSERT [dbo].[disease] ( [name], [description]) VALUES ( N'cancer', N'') INSERT [dbo].[disease] ( [name], [description]) VALUES ( N'swine flu h1 n1', N'') INSERT [dbo].[disease] ( [name], [description]) VALUES ( N'Diabities ss', N'') 12 INSERT [dbo].[disease] ( [name], [description]) VALUES ( N'aaaaaaaaaaaaaaaaaaa', N'18') INSERT [dbo].[disease] ( [name], [description]) VALUES ( N'fffffffffffffffff', N'18') INSERT [dbo].[disease] ( [name], [description]) VALUES ( N'df', N'18') INSERT [dbo].[disease] ( [name], [description]) VALUES ( N'Cancer', N' Cancer new') INSERT [dbo].[disease] ( [name], [description]) VALUES ( N'Cancer', N' Cancer new') INSERT [dbo].[disease] ( [name], [description]) VALUES ( N'Cancer1', N' Cancer1') INSERT [dbo].[disease] ( [name], [description]) VALUES ( N'Cancer1', N' Cancer1') INSERT [dbo].[disease] ( [name], [description]) VALUES ( N'Diabities New', N' Diabities New') Go INSERT [dbo].[doctor] ( [username], [password], [email], [create_at], [lock_user]) VALUES ( N'root', N'dd', N'[email protected]', CAST(N'2020-0621T23:50:18.0000000' AS DateTime2), NULL) INSERT [dbo].[doctor] ( [username], [password], [email], [create_at], [lock_user]) VALUES ( N'dd', N'$2y$10$ShY.QyAUMcAE2MiyJ.GvQO2SapvPoI6fYnm6yzy0k.0I1qzx7yoJS', N'[email protected]', CAST(N'2020-06-21T23:59:12.0000000' AS DateTime2), NULL) INSERT [dbo].[doctor] ( [username], [password], [email], [create_at], [lock_user]) VALUES ( N'dd', N'$2y$10$xvkh1p5WVg548eU9v3THXOdfA4KS4XrHy8TQvwYp4eqT5C/l6rmk.', N'[email protected]', CAST(N'2020-06-22T00:03:58.0000000' AS DateTime2), NULL) INSERT [dbo].[doctor] ( [username], [password], [email], [create_at], [lock_user]) VALUES ( N'dd', N'$2y$10$a2jtN0Fz4mFxv6Bo5rbnfu0Ke87XWAFwxKLoAHlo2QtqcvL9r6e9a', N'[email protected]', CAST(N'2020-06-22T00:04:07.0000000' AS DateTime2), NULL) INSERT [dbo].[doctor] ( [username], [password], [email], [create_at], [lock_user]) VALUES ( N'aa', N'$2y$10$bMrZd7DarE.XBNOCaPrrNuYMz6nyh23RKaEncXQtc2KKSg85BQeD.', N'[email protected]', CAST(N'2020-06-22T00:54:37.0000000' AS DateTime2), NULL) INSERT [dbo].[doctor] ( [username], [password], [email], [create_at], [lock_user]) VALUES ( N'adi', N'$2y$10$zfddj9XUO1fBqFlHxewx9OD.dltSNSYTcgZScqwW3mrYvoedGv9/i', N'[email protected]', CAST(N'2020-06-23T19:08:41.0000000' AS DateTime2), NULL) 13 Go INSERT [dbo].[doctor_infos] ([doctor_id], [doctor_name], [adress], [expertise], [chamber_name]) VALUES (1, N'A.Elhalloumi', N'Gueliz,residence rihana app 222 marrakech', N'Pediatric - Child', N'Central Hospital Limited') Go INSERT [dbo].[medicines] ( [medicine_name], [medicine_group], [power], [medicine_type], [company_name]) VALUES (N'Momento', N'Pain', 500, N'Syrup', N'Square') INSERT [dbo].[medicines] ( [medicine_name], [medicine_group], [power], [medicine_type], [company_name]) VALUES (N'Contain', N'Cold', 500, N'Tablet', N'Square') INSERT [dbo].[medicines] ( [medicine_name], [medicine_group], [power], [medicine_type], [company_name]) VALUES (N'Fixal', N'Cold', 180, N'Tablet', N'Square') INSERT [dbo].[medicines] ( [medicine_name], [medicine_group], [power], [medicine_type], [company_name]) VALUES (N'Panadol', N'paracitamol bp and Caffein', 500, N'Tablet', N'Panadol') INSERT [dbo].[medicines] ( [medicine_name], [medicine_group], [power], [medicine_type], [company_name]) VALUES (N'ddddddd', N'ddddd', 500, N'', N'dd') INSERT [dbo].[medicines] ( [medicine_name], [medicine_group], [power], [medicine_type], [company_name]) VALUES (N'ddddddd', N'd', 400, N'Tablet', N'd') INSERT [dbo].[medicines] ( [medicine_name], [medicine_group], [power], [medicine_type], [company_name]) VALUES (N'dddddkkkkkk', N'dddddddkkkkkk', 400, N'Syrup', N'dddd') INSERT [dbo].[medicines] ( [medicine_name], [medicine_group], [power], [medicine_type], [company_name]) VALUES (N'ff', N'ff', 500, N'Tablet', N'ff') INSERT [dbo].[medicines] ( [medicine_name], [medicine_group], [power], [medicine_type], [company_name]) VALUES (N'ddddddd', N'ddd', 500, N'', N'd') INSERT [dbo].[medicines] ( [medicine_name], [medicine_group], [power], [medicine_type], [company_name]) VALUES ( N'ddddddd', N'ddd', 500, N'', N'd') INSERT [dbo].[medicines] ( [medicine_name], [medicine_group], [power], [medicine_type], [company_name]) VALUES ( N'ddd', N'ddd', 500, N'Tablet', N'd') 14 INSERT [dbo].[medicines] ( [medicine_name], [medicine_group], [power], [medicine_type], [company_name]) VALUES ( N'ddd', N'ddd', 500, N'Tablet', N'd') INSERT [dbo].[medicines] ( [medicine_name], [medicine_group], [power], [medicine_type], [company_name]) VALUES ( N'ka', N'ka', 20, N'Drop', N'ka') INSERT [dbo].[medicines] ( [medicine_name], [medicine_group], [power], [medicine_type], [company_name]) VALUES ( N'ka', N'ka', 20, N'Drop', N'ka') INSERT [dbo].[medicines] ( [medicine_name], [medicine_group], [power], [medicine_type], [company_name]) VALUES ( N'Disprine', N'Disprine', 0, N'Tablet', N'Disprine') INSERT [dbo].[medicines] ( [medicine_name], [medicine_group], [power], [medicine_type], [company_name]) VALUES ( N'Disprine', N'Disprine', 0, N'Tablet', N'Disprine') Go INSERT [dbo].[note] ([patient_id], [note]) VALUES (1, N'Walk Every Morning') Go INSERT [dbo].[prescribed_medicine] ( [patient_id], [medicine_id], [medicine_time]) VALUES (11, 1, N'1-1-1') INSERT [dbo].[prescribed_medicine] ( [patient_id], [medicine_id], [medicine_time]) VALUES (18, 1, N'1-1-1') INSERT [dbo].[prescribed_medicine] ( [patient_id], [medicine_id], [medicine_time]) VALUES (18, 2, N'1-1-1') INSERT [dbo].[prescribed_medicine] ( [patient_id], [medicine_id], [medicine_time]) VALUES ( 1, 1, N'1-1-1') INSERT [dbo].[prescribed_medicine] ( [patient_id], [medicine_id], [medicine_time]) VALUES ( 1, 13, N'1-1-1') Go INSERT [dbo].[prescriptions] ( [medicine_id], [symptom_id], [patient_id], [medicine_time], [disease_id], [note], [prescription_date]) VALUES (2, 1, 1, N'0-10', 0, N'', CAST(N'2016-08-21T08:02:27.0000000' AS DateTime2)) 15 INSERT [dbo].[prescriptions] ( [medicine_id], [symptom_id], [patient_id], [medicine_time], [disease_id], [note], [prescription_date]) VALUES (1, 2, 1, N'0-01', 0, N'', CAST(N'2016-08-21T08:05:30.0000000' AS DateTime2)) INSERT [dbo].[prescriptions] ( [medicine_id], [symptom_id], [patient_id], [medicine_time], [disease_id], [note], [prescription_date]) VALUES (3, 3, 3, N'1-01', 0, N'', CAST(N'2016-08-21T08:39:54.0000000' AS DateTime2)) INSERT [dbo].[prescriptions] ( [medicine_id], [symptom_id], [patient_id], [medicine_time], [disease_id], [note], [prescription_date]) VALUES (2, 0, 3, N'1-11', 0, N'', CAST(N'2016-08-21T08:44:48.0000000' AS DateTime2)) INSERT [dbo].[prescriptions] ( [medicine_id], [symptom_id], [patient_id], [medicine_time], [disease_id], [note], [prescription_date]) VALUES (4, 0, 1, N'0-10', 0, N'', CAST(N'2016-08-24T11:18:38.0000000' AS DateTime2)) INSERT [dbo].[prescriptions] ( [medicine_id], [symptom_id], [patient_id], [medicine_time], [disease_id], [note], [prescription_date]) VALUES (3, 0, 1, N'0-01', 0, N'', CAST(N'2016-08-24T11:19:04.0000000' AS DateTime2)) INSERT [dbo].[prescriptions] ( [medicine_id], [symptom_id], [patient_id], [medicine_time], [disease_id], [note], [prescription_date]) VALUES ( 1, 0, 1, N'1-11', 0, N'', CAST(N'2016-08-24T11:19:26.0000000' AS DateTime2)) INSERT [dbo].[prescriptions] ( [medicine_id], [symptom_id], [patient_id], [medicine_time], [disease_id], [note], [prescription_date]) VALUES ( 2, 0, 3, N'1-01', 0, N'', CAST(N'2016-08-24T11:48:39.0000000' AS DateTime2)) INSERT [dbo].[prescriptions] ( [medicine_id], [symptom_id], [patient_id], [medicine_time], [disease_id], [note], [prescription_date]) VALUES ( 2, 0, 3, N'1-01', 0, N'', CAST(N'2016-08-24T11:50:09.0000000' AS DateTime2)) INSERT [dbo].[prescriptions] ( [medicine_id], [symptom_id], [patient_id], [medicine_time], [disease_id], [note], [prescription_date]) VALUES ( 4, 0, 6, N'1-01', 0, N'', CAST(N'2016-08-24T11:53:51.0000000' AS DateTime2)) INSERT [dbo].[prescriptions] ( [medicine_id], [symptom_id], [patient_id], [medicine_time], [disease_id], [note], [prescription_date]) VALUES ( 2, 0, 1, N'0-10', 0, N'', CAST(N'2016-09-02T07:00:26.0000000' AS DateTime2)) INSERT [dbo].[prescriptions] ( [medicine_id], [symptom_id], [patient_id], [medicine_time], [disease_id], [note], [prescription_date]) VALUES ( 3, 0, 1, N'1-01', 0, N'', CAST(N'2016-09-02T07:01:15.0000000' AS DateTime2)) INSERT [dbo].[prescriptions] ( [medicine_id], [symptom_id], [patient_id], [medicine_time], [disease_id], [note], [prescription_date]) VALUES ( 2, 0, 1, N'1-11', 0, N'', CAST(N'2016-09-02T07:03:26.0000000' AS DateTime2)) 16 INSERT [dbo].[prescriptions] ( [medicine_id], [symptom_id], [patient_id], [medicine_time], [disease_id], [note], [prescription_date]) VALUES ( 2, 0, 1, N'1-00', 0, N'', CAST(N'2016-09-02T07:09:00.0000000' AS DateTime2)) INSERT [dbo].[prescriptions] ( [medicine_id], [symptom_id], [patient_id], [medicine_time], [disease_id], [note], [prescription_date]) VALUES ( 4, 0, 2, N'1-00', 0, N'', CAST(N'2016-09-02T07:09:00.0000000' AS DateTime2)) INSERT [dbo].[prescriptions] ( [medicine_id], [symptom_id], [patient_id], [medicine_time], [disease_id], [note], [prescription_date]) VALUES ( 1, 0, 2, N'1-00', 0, N'', CAST(N'2016-09-02T07:09:00.0000000' AS DateTime2)) INSERT [dbo].[prescriptions] ( [medicine_id], [symptom_id], [patient_id], [medicine_time], [disease_id], [note], [prescription_date]) VALUES ( 2, 0, 3, N'1-01', 0, N'', CAST(N'2016-09-02T09:55:58.0000000' AS DateTime2)) INSERT [dbo].[prescriptions] ( [medicine_id], [symptom_id], [patient_id], [medicine_time], [disease_id], [note], [prescription_date]) VALUES ( 3, 0, 3, N'1-11', 0, N'', CAST(N'2016-09-02T09:55:58.0000000' AS DateTime2)) INSERT [dbo].[prescriptions] ( [medicine_id], [symptom_id], [patient_id], [medicine_time], [disease_id], [note], [prescription_date]) VALUES ( 4, 0, 11, N'11-1', 0, N'', CAST(N'2017-04-09T10:29:41.0000000' AS DateTime2)) INSERT [dbo].[prescriptions] ( [medicine_id], [symptom_id], [patient_id], [medicine_time], [disease_id], [note], [prescription_date]) VALUES ( 1, 0, 11, N'10-1', 0, N'', CAST(N'2017-04-09T10:29:41.0000000' AS DateTime2)) INSERT [dbo].[prescriptions] ( [medicine_id], [symptom_id], [patient_id], [medicine_time], [disease_id], [note], [prescription_date]) VALUES ( 2, 0, 11, N'01-0', 0, N'', CAST(N'2017-04-09T10:29:41.0000000' AS DateTime2)) INSERT [dbo].[prescriptions] ( [medicine_id], [symptom_id], [patient_id], [medicine_time], [disease_id], [note], [prescription_date]) VALUES ( 1, 1, 1, N'1-11', 0, N'', CAST(N'2020-06-27T21:35:27.0000000' AS DateTime2)) INSERT [dbo].[prescriptions] ( [medicine_id], [symptom_id], [patient_id], [medicine_time], [disease_id], [note], [prescription_date]) VALUES ( 13, 21, 1, N'11-1', 0, N'', CAST(N'2020-06-27T21:35:27.0000000' AS DateTime2)) INSERT [dbo].[prescriptions] ( [medicine_id], [symptom_id], [patient_id], [medicine_time], [disease_id], [note], [prescription_date]) VALUES ( 1, 0, 1, N'1-11', 0, N'', CAST(N'2020-06-27T21:37:15.0000000' AS DateTime2)) INSERT [dbo].[prescriptions] ( [medicine_id], [symptom_id], [patient_id], [medicine_time], [disease_id], [note], [prescription_date]) VALUES ( 13, 0, 1, N'11-1', 0, N'', CAST(N'2020-06-27T21:37:15.0000000' AS DateTime2)) 17 INSERT [dbo].[prescriptions] ( [medicine_id], [symptom_id], [patient_id], [medicine_time], [disease_id], [note], [prescription_date]) VALUES ( 0, 1, 1, N'1-11', 0, N'', CAST(N'2020-06-27T21:37:15.0000000' AS DateTime2)) INSERT [dbo].[prescriptions] ( [medicine_id], [symptom_id], [patient_id], [medicine_time], [disease_id], [note], [prescription_date]) VALUES ( 0, 21, 1, N'11-1', 0, N'', CAST(N'2020-06-27T21:37:15.0000000' AS DateTime2)) INSERT [dbo].[prescriptions] ( [medicine_id], [symptom_id], [patient_id], [medicine_time], [disease_id], [note], [prescription_date]) VALUES ( 0, 20, 1, N'11-1', 0, N'', CAST(N'2020-06-27T21:37:15.0000000' AS DateTime2)) INSERT [dbo].[prescriptions] ( [medicine_id], [symptom_id], [patient_id], [medicine_time], [disease_id], [note], [prescription_date]) VALUES ( 1, 1, 1, N'1-11', 0, N'Walk Every Morning', CAST(N'2020-06-28T16:13:01.0000000' AS DateTime2)) INSERT [dbo].[prescriptions] ( [medicine_id], [symptom_id], [patient_id], [medicine_time], [disease_id], [note], [prescription_date]) VALUES ( 1, 1, 1, N'1-11', 0, N'Walk Every Morning', CAST(N'2020-06-28T16:16:07.0000000' AS DateTime2)) INSERT [dbo].[prescriptions] ( [medicine_id], [symptom_id], [patient_id], [medicine_time], [disease_id], [note], [prescription_date]) VALUES ( 1, 1, 1, N'1-11', 12, N'Walk Every Morning', CAST(N'2020-06-28T16:18:30.0000000' AS DateTime2)) Go INSERT [dbo].[symptoms] ( [symptom_name], [symptom_type], [description]) VALUES (N'Cancer aaa', N'danger', N'dssskh') INSERT [dbo].[symptoms] ( [symptom_name], [symptom_type], [description]) VALUES (N'Swine Flu', N'Meduim', N'dd') INSERT [dbo].[symptoms] ( [symptom_name], [symptom_type], [description]) VALUES (N'Skin Elergy', N'3', N'') INSERT [dbo].[symptoms] ( [symptom_name], [symptom_type], [description]) VALUES (N'Diabtiease', N'0', N'') INSERT [dbo].[symptoms] ( [symptom_name], [symptom_type], [description]) VALUES (N'Blood Presure', N'0', N'') INSERT [dbo].[symptoms] ( [symptom_name], [symptom_type], [description]) VALUES (N'Abdominal Aortic Aneurysm', N'0', N'Abdominal Aortic Aneurysm') 18 INSERT [dbo].[symptoms] ( [symptom_name], [symptom_type], [description]) VALUES (N'Abdominal Cramps (Heat Cramps)', N'0', N'Abdominal Cramps (Heat Cramps)') INSERT [dbo].[symptoms] ( [symptom_name], [symptom_type], [description]) VALUES (N'Back Pain (Low Back Pain)', N'0', N'Back Pain (Low Back Pain)') INSERT [dbo].[symptoms] ( [symptom_name], [symptom_type], [description]) VALUES ( N'Barium Enema', N'0', N'Barium Enema') INSERT [dbo].[symptoms] ( [symptom_name], [symptom_type], [description]) VALUES ( N'COld', N'0', N'') INSERT [dbo].[symptoms] ( [symptom_name], [symptom_type], [description]) VALUES ( N'Head Pain', N'0', N'') INSERT [dbo].[symptoms] ( [symptom_name], [symptom_type], [description]) VALUES ( N'Fever', N'0', N'dfsdf') INSERT [dbo].[symptoms] ( [symptom_name], [symptom_type], [description]) VALUES ( N'cancer', N'0', N'') INSERT [dbo].[symptoms] ( [symptom_name], [symptom_type], [description]) VALUES ( N'swine flu h1 n1', N'0', N'') INSERT [dbo].[symptoms] ( [symptom_name], [symptom_type], [description]) VALUES ( N'Diabities ss', N'0', N'') INSERT [dbo].[symptoms] ( [symptom_name], [symptom_type], [description]) VALUES ( N'ffffffffff', N'0', N'18') INSERT [dbo].[symptoms] ( [symptom_name], [symptom_type], [description]) VALUES ( N'fd', N'0', N'18') INSERT [dbo].[symptoms] ( [symptom_name], [symptom_type], [description]) VALUES ( N'Tyfide', N'Meduim', N'Tyfide') INSERT [dbo].[symptoms] ( [symptom_name], [symptom_type], [description]) VALUES ( N'Tyfide', N'Meduim', N'Tyfide') INSERT [dbo].[symptoms] ( [symptom_name], [symptom_type], [description]) VALUES ( N'Tyfide new', N'Common', N'Tyfide new') INSERT [dbo].[symptoms] ( [symptom_name], [symptom_type], [description]) VALUES ( N'Tyfide new', N'Common', N'Tyfide new') INSERT [dbo].[symptoms] ( [symptom_name], [symptom_type], [description]) VALUES ( N'asde', N'Common ', N' asde') INSERT [dbo].[symptoms] ( [symptom_name], [symptom_type], [description]) VALUES ( N'asde', N'Common ', N' asde') 19 Go INSERT [dbo].[users] ( [firstName], [lastName], [age], [gender], [email], [contact], [password], [blood_group], [address], [user_type], [lock_user]) VALUES ( N'karim', N'elhalloumi', 23, N'male', N'[email protected]', N'+8801516782104', N'admin', N'AB+', N'marrakech', N'patient', NULL) INSERT [dbo].[users] ( [firstName], [lastName], [age], [gender], [email], [contact], [password], [blood_group], [address], [user_type], [lock_user]) VALUES ( N'dd', N'dd', 15, N'male', N'[email protected]', N'9849849884984', N'dd', N'A+', N'sss', N'patient', NULL) ALTER TABLE [dbo].[cc_and_oe] ADD CONSTRAINT [DF__cc_and_oe__statu__36B12243] DEFAULT ((0)) FOR [status] GO ALTER TABLE [dbo].[doctor] ADD CONSTRAINT [DF__doctor__create_a__3C69FB99] DEFAULT (getdate()) FOR [create_at] GO ALTER TABLE [dbo].[doctor] ADD CONSTRAINT [DF__doctor__lock_use__3D5E1FD2] DEFAULT (NULL) FOR [lock_user] GO ALTER TABLE [dbo].[prescriptions] ADD CONSTRAINT [DF__prescript__presc__4316F928] DEFAULT (getdate()) FOR [prescription_date] GO ALTER TABLE [dbo].[users] ADD CONSTRAINT [DF__users__lock_user__46E78A0C] DEFAULT (NULL) FOR [lock_user] GO ALTER TABLE [dbo].[disease] WITH CHECK ADD CONSTRAINT [CK__disease__disease__3A81B327] CHECK (([disease_id]>(0))) GO ALTER TABLE [dbo].[disease] CHECK CONSTRAINT [CK__disease__disease__3A81B327] GO ALTER TABLE [dbo].[symptoms] WITH CHECK ADD CONSTRAINT [CK__symptoms__sympto__44FF419A] CHECK (([symptom_id]>(0))) 20 GO ALTER TABLE [dbo].[symptoms] CHECK CONSTRAINT [CK__symptoms__sympto__44FF419A] GO 2.1.2 Présentation du script de la base de données -Microsoft SQL Server Management (pour les traitment et generation de script) 2.2 Connexion à une base de données SQL server à partir du PHP 2.2.1 configuration de l’outil utilisé pour assurer la connexion 21 -Microsoft visual sudio : 2.2.2 Présentation du script de connexion Connection.php <?php $serverName = "DESKTOP-SHOHAG"; $connectionInfo = array( "Database"=>"DiagnosticLabDb", "UID"=>"sa", "PWD"=>"XXXXX"); $conn = sqlsrv_connect( $serverName, $connectionInfo); if( $conn echo }else{ echo die( } ?> ) { "Connection established.<br />"; "Connection could not be established.<br />"; print_r( sqlsrv_errors(), true)); 22 2.3 Exemple d’opérations CRUD SQLServer/PHP INSERT INTO note (patient_id, note) VALUES (4, 'Their is no critical conditions') UPDATE note SET note='Updated Notes Here..' WHERE patient_id=4" "SELECT user_id, firstname, lastname, age FROM users" "DELETE note WHERE patient_id=4"; 2.3.1 Présentation de la table choisie 23 2.3.2 Présentation des scripts CRUD Insert.php <?php $serverName = "DESKTOP-SHOHAG"; $connectionInfo = array( "Database"=>"DiagnosticLabDb", "UID"=>"sa", "PWD"=>"XXXX X"); $conn = sqlsrv_connect( $serverName, $connectionInfo); $tsql = "INSERT INTO note (patient_id, note) VALUES (101, 'Nothing to be serious' )"; /* Execute the query. */ $stmt = sqlsrv_query( $conn, $tsql); if ($stmt) { echo "Inserted Successfully.<br>\n"; } else { echo "Error in statement execution.\n"; die( print_r( sqlsrv_errors(), true)); } /* Free statement and connection resources. */ sqlsrv_free_stmt( $stmt); sqlsrv_close( $conn); ?> Select.php <?php $serverName = "DESKTOP-SHOHAG"; $connectionInfo = array( "Database"=>"DiagnosticLabDb", "UID"=>"sa", "PWD"=>"XXXX X"); $conn = sqlsrv_connect( $serverName, $connectionInfo); $tsql = "SELECT user_id, firstname, lastname, age FROM users"; /* Execute the query. */ $stmt = sqlsrv_query( $conn, $tsql); 24 if ( $stmt ) { echo "Statement executed.<br>\n"; } else { echo "Error in statement execution.\n"; die( print_r( sqlsrv_errors(), true)); } /* Iterate through the result set printing a row of data upon each iteration.*/ while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_NUMERIC)) { echo "Col1: ".$row[0]."\n"; echo "Col2: ".$row[1]."\n"; echo "Col3: ".$row[2]."<br>\n"; echo "-----------------<br>\n"; } /* Free statement and connection resources. */ sqlsrv_free_stmt( $stmt); sqlsrv_close( $conn); ?> Update.php <?php $serverName = "DESKTOP-SHOHAG"; $connectionInfo = array( "Database"=>"DiagnosticLabDb", "UID"=>"sa", "PWD"=>"XXXX X"); $conn = sqlsrv_connect( $serverName, $connectionInfo); $tsql = "UPDATE note SET note='Updated Notes Here..' WHERE patient_id=101"; /* Execute the query. */ $stmt = sqlsrv_query( $conn, $tsql); 25 if ($stmt) { echo "Successfully Updated.<br>\n"; } else { echo "Error in statement execution.\n"; die( print_r( sqlsrv_errors(), true)); } /*connection closed. */ sqlsrv_free_stmt( $stmt); sqlsrv_close( $conn); ?> Delete.php <?php $serverName = "DESKTOP-SHOHAG"; $connectionInfo = array( "Database"=>"DiagnosticLabDb", "UID"=>"sa", "PWD"=>"XXXX X"); $conn = sqlsrv_connect( $serverName, $connectionInfo); $tsql = "DELETE note WHERE patient_id=101"; /* Execute the query. */ $stmt = sqlsrv_query( $conn, $tsql); 26 if ($stmt) { echo "Successfully DELETED.<br>\n"; } else { echo "Error in statement execution.\n"; die( print_r( sqlsrv_errors(), true)); } /*connection closed. */ sqlsrv_free_stmt( $stmt); sqlsrv_close( $conn); ?> Références https://stackoverflow.com/ https://github.com/ https://techcommunity.microsoft.com/t5/sql-server/bd-p/SQL_Server Annexes Projet complet encadré par Mr.Issam Qaffou : https://we.tl/t-ihrwi5MKgo Partie 2: La Notion index: Dans la vie courante, nous sommes entourés d'index : un code postal, le n° d'un immeuble dans une rue, les numéros de téléphone sont des index. Autrefois, lorsque le téléphone fit son apparition (Clémenceau aurait dit du téléphone « Quoi ? On vous sonne comme un laquais ??? ») il n'y avait pas de numéro. On agitait une manivelle qui avait pour effet de réveiller l'opératrice du standard auquel on était physiquement relié. Puis on demandait à cette personne de nous connecter avec Monsieur le Marquis de Carabas à Toledo. Il s'ensuivait un échange de bons procédés entre les opératrices des différents relais afin d'établir une liaison physique entre ces deux clients. Puis vint une première numérotation… Elle était faite ville par ville. On vit alors Fernand Raynaud dans son célèbre sketch demander désespérément le 22 à Asnières et n'obtenir que New York ! Dans les années 50, l'automatique fit son apparition et l'on pouvait appeler en interurbain une personne en composant directement son numéro. Ainsi le commissaire Maigret fut-il joignable à PELLEPORT 38 52. Puis l'automatique fut étendu à toutes les régions de France. Depuis quelques années seulement on peut joindre n'importe qui sur terre avec un simple numéro. Mais les numéros se sont allongés.Le mien, complet, est 00 33 6 11 86 40 66. 27 Remarquez la structure de ce numéro. Il commence par un double zéro qui indique que l'on va s'intéresser à l'international. Puis le second groupe de chiffres indique le pays. Enfin le suivant indique la nature du réseau (ici le réseau de téléphones cellulaires). Chacun des groupes de chiffres précise un peu plus les informations. Une chose importante des index est leur structuration. On convient à l'évidence que si les numéros des immeubles étaient distribués au hasard sur chaque bâtiment[1], il serait difficile (mais pas impossible) de trouver le 68 avenue des Champs Élysées. En fait, il suffirait – au pire – de parcourir toute l'avenue pour trouver le bon bâtiment. On comprend donc que l'organisation des données dans un index est primordiale pour accélérer les recherches. En fait, les données d'un index sont systématiquement triées et les données stockées dans une structure particulière favorisant les recherches : liste ordonnée ou arbre, le plus souvent. Exemple: d'index fait que l'information y est vectorisée. En effet, chaque colonne supplémentaire précise la colonne précédente. De ce fait, la recherche dans un index multicolonne n'est accélérée que si les données cherchées sont un sous-ensemble ordonné du vecteur. Voyons cela en termes pratiques à l'aide d'un exemple… Exemple pratique : Sélectionnez CLI_NOM CLI_PRENOM CLI_DATE_NAISSANCE --------- ----------- -----------------DUPONT Alain 21/01/1930 DUPONT Marcel 21/01/1930 DUPONT Marcel 01/06/1971 DUPONT Paul 21/01/1930 MARTIN Alain 11/02/1987 MARTIN Marcel 21/01/1930 ... Dans cet index composé d'un nom d'un prénom et d'une date de naissance, la recherche sera efficace pour les informations suivantes : • • • CLI_NOM CLI_NOM + CLI_PRENOM CLI_NOM + CLI_PRENOM + CLI_DATE_NAISSANCE En revanche, la recherche sur une seule colonne CLI_PRENOM ou CLI_DATE_NAISANCE et a fortiori sur ces deux colonnes n'aura aucune efficacité du fait du tri relatif des colonnes entre elles (vectorisation)… En effet chercher « Paul » revient à parcourir tout l'index (balayage ou scan) alors que chercher « MARTIN », revient à se placer très rapidement par dichotomie au bon endroit (recherche ou seek). 28 29