Debian
Debian-France
Debian-Facile
Debian-fr.org
Forum-Debian.fr
Debian ?
Communautés
Vous n'êtes pas identifié(e).
L'icône rouge permet de télécharger chaque page du wiki visitée au format
PDF et la grise au format ODT →
Ci-dessous, les différences entre deux révisions de la page.
| Les deux révisions précédentes Révision précédente Prochaine révision | Révision précédente Dernière révision Les deux révisions suivantes | ||
|
utilisateurs:hypathie:tutos:php_connexion_mysql [08/07/2021 16:16] Hypathie [PHP connexion mysql] |
utilisateurs:hypathie:tutos:php_connexion_mysql [08/07/2021 17:22] Hypathie |
||
|---|---|---|---|
| Ligne 1: | Ligne 1: | ||
| - | ====== PHP connexion mysql ====== | + | ====== PHP singleton de connexion mysql ====== |
| * Objet : code php de connexion | * Objet : code php de connexion | ||
| Ligne 6: | Ligne 6: | ||
| * Débutant, à savoir : [[:doc:systeme:commandes:le_debianiste_qui_papillonne|Utiliser GNU/Linux en ligne de commande, tout commence là !.]] :-) | * Débutant, à savoir : [[:doc:systeme:commandes:le_debianiste_qui_papillonne|Utiliser GNU/Linux en ligne de commande, tout commence là !.]] :-) | ||
| - | ===== Objet PDO ===== | + | ===== BDD mysql/mariadb ===== |
| - | === créer une instance de l'objet === | + | ==== Créer une base ==== |
| + | |||
| + | === Script sql=== | ||
| + | <code sql> | ||
| + | //nath_test.sql | ||
| + | CREATE DATABASE IF NOT EXISTS `nath_test`; | ||
| + | |||
| + | USE `nath_test`; | ||
| + | |||
| + | CREATE TABLE IF NOT EXISTS `users` ( | ||
| + | `id` int(11) NOT NULL, | ||
| + | `name` varchar(250) NOT NULL, | ||
| + | `surname` varchar(250) NOT NULL | ||
| + | ) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=UTF8; | ||
| + | |||
| + | INSERT INTO `users` (`id`, `name`, `surname`) VALUES | ||
| + | (1, 'Jean-Marc', 'Joseph'), | ||
| + | (2, 'Nathalie', 'D urso'), | ||
| + | (3, 'Lionel', 'Joseph'), | ||
| + | (4, 'Samuel', 'D urso'), | ||
| + | (5, 'Helene-Fleur', 'D urso'); | ||
| + | |||
| + | ALTER TABLE `users` | ||
| + | ADD PRIMARY KEY (`id`); | ||
| + | |||
| + | ALTER TABLE `users` | ||
| + | MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=28; | ||
| + | |||
| + | CREATE TABLE IF NOT EXISTS `tree_country_state_city` ( | ||
| + | `id` int(11) NOT NULL, | ||
| + | `name` varchar(250) NOT NULL, | ||
| + | `parent_id` int(11) NOT NULL | ||
| + | ) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=UTF8; | ||
| + | |||
| + | |||
| + | INSERT INTO `tree_country_state_city` (`id`, `name`, `parent_id`) VALUES | ||
| + | (1, 'USA', 0), | ||
| + | (2, 'Canada', 0), | ||
| + | (3, 'Australia', 0), | ||
| + | (4, 'New York', 1), | ||
| + | (5, 'Alabama', 1), | ||
| + | (6, 'California', 1), | ||
| + | (7, 'Ontario', 2), | ||
| + | (8, 'British Columbia', 2), | ||
| + | (9, 'New South Wales', 3), | ||
| + | (10, 'Queensland', 3), | ||
| + | (11, 'New York city', 4), | ||
| + | (12, 'Buffalo', 4), | ||
| + | (13, 'Albany', 4), | ||
| + | (14, 'Birmingham', 5), | ||
| + | (15, 'Montgomery', 5), | ||
| + | (16, 'Huntsville', 5), | ||
| + | (17, 'Los Angeles', 6), | ||
| + | (18, 'San Francisco', 6), | ||
| + | (19, 'San Diego', 6), | ||
| + | (20, 'Toronto', 7), | ||
| + | (21, 'Ottawa', 7), | ||
| + | (22, 'Vancouver', 8), | ||
| + | (23, 'Victoria', 8), | ||
| + | (24, 'Sydney', 9), | ||
| + | (25, 'Newcastle', 9), | ||
| + | (26, 'City of Brisbane', 10), | ||
| + | (27, 'Gold Coast', 10); | ||
| + | |||
| + | |||
| + | ALTER TABLE `tree_country_state_city` | ||
| + | ADD PRIMARY KEY (`id`); | ||
| + | |||
| + | |||
| + | ALTER TABLE `tree_country_state_city` | ||
| + | MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=28; | ||
| + | |||
| + | </code> | ||
| + | |||
| + | === Executer le script=== | ||
| + | |||
| + | <code user> | ||
| + | mysql -u root -p < ~/user/Test/nath_test.sql | ||
| + | </code> | ||
| + | ===== Singleton php ===== | ||
| + | |||
| + | <code> | ||
| + | └───public | ||
| + | |_ MysqlSingleton.php | ||
| + | |_ gestionnairePDO.php | ||
| + | |_ data_users.php | ||
| + | |_ data_tree.php | ||
| + | |_ index.php | ||
| + | </code> | ||
| + | |||
| + | === MysqlSingleton.php === | ||
| <code php> | <code php> | ||
| - | // file name : Connexion.class.php | ||
| <?php | <?php | ||
| - | class Database | + | error_reporting(E_ALL); |
| + | ini_set('display_errors', '1'); | ||
| + | |||
| + | class MysqlSingleton | ||
| { | { | ||
| - | private static $cont = null; | + | const SQL_USER = 'toto'; |
| - | | + | const SQL_HOST = 'localhost'; |
| - | public function __construct() { | + | const SQL_PASS = '********'; |
| - | die('Init function is not allowed'); | + | const SQL_DTB = 'nath_test'; |
| + | private static $connect = null; | ||
| + | private $message = null; | ||
| + | |||
| + | private function __construct() | ||
| + | { | ||
| + | // A singleton should not be instanced ! | ||
| + | } | ||
| + | protected static function firstConnexion() | ||
| + | { | ||
| + | if(is_null(self::$connect )) | ||
| + | { | ||
| + | try | ||
| + | { | ||
| + | self::$connect = new PDO('mysql:dbname='.self::SQL_DTB.';host='.self::SQL_HOST,self::SQL_USER ,self::SQL_PASS); | ||
| + | echo "CREATION CONNEXION PDO MYSQL"; | ||
| + | } | ||
| + | catch(PDOException $e) | ||
| + | { | ||
| + | $this->message=$e->getMessage(); | ||
| + | die(); | ||
| + | } | ||
| + | } | ||
| + | return self::$connect; | ||
| } | } | ||
| - | + | ||
| - | public static function connect() | + | protected function getStatus() |
| { | { | ||
| - | + | return !is_null(self::$connect); | |
| - | if ( null == self::$cont ) | + | |
| - | { | + | |
| - | try | + | |
| - | { | + | |
| - | self::$cont = new PDO('mysql:host=localhost;dbname=comgocom;charset=utf8','root', 'arthuretmax2020'); | + | |
| - | + | ||
| - | // self::$cont = new PDO('mysql:host=localhost;dbname=essaiLogin;charset=utf8','essaiLogin', 'essaiLogin'); | + | |
| - | } | + | |
| - | catch(PDOException $e) | + | |
| - | { | + | |
| - | die($e->getMessage()); | + | |
| - | } | + | |
| - | } | + | |
| - | return self::$cont; | + | |
| } | } | ||
| - | + | ||
| - | public static function disconnect() | + | protected function getErreurMessage() |
| { | { | ||
| - | self::$cont = null; | + | return $this->message; |
| } | } | ||
| + | |||
| + | protected function disconnectMysql() | ||
| + | { | ||
| + | self::$connect = null; | ||
| + | } | ||
| } | } | ||
| </code> | </code> | ||
| - | === utiliser l'objet === | + | === gestionnairePDO.php === |
| <code php> | <code php> | ||
| - | // file name : RequetesIdentification.class.php | ||
| - | |||
| <?php | <?php | ||
| - | error_reporting(E_ALL); | + | |
| - | ini_set('display_errors', '1'); | + | require 'MysqlSingleton.php'; |
| - | + | ||
| - | require 'Connexion.class.php'; | + | |
| - | class RequetesIdentification | + | class GestionPDO extends MysqlSingleton |
| { | { | ||
| - | + | private $PDO_connexion = null; | |
| - | public function __construct() | + | private $callingBy = null; |
| + | | ||
| + | public function __construct($callingBy) | ||
| { | { | ||
| - | $this->pdo = Database :: connect(); | + | $this->PDO_connexion = parent::firstConnexion(); |
| + | $this->callingBy = $callingBy; | ||
| + | echo " <br/>Constructeur GestionPDO.php calling by " . $callingBy . ". "; | ||
| } | } | ||
| - | + | ||
| - | + | /*public function queryFetch($query) | |
| - | + | ||
| - | public function isPseudoExists($info) | + | |
| { | { | ||
| - | // On veut voir si tel pseudo ayant pour id $info existe. | + | return $this->securConnexion()->query($query)->fetch(); |
| - | if (is_int($info)) | + | }*/ |
| - | { | + | public function logSqlErreur() |
| - | $q = $this->pdo->query("SELECT COUNT(*) FROM personne WHERE id =$info;")->fetchColumn(); | + | { |
| - | } | + | // TODO |
| - | else | + | |
| - | { | + | |
| - | // Sinon, c'est qu'on veut vérifier que le "pseudo" existe ou pas. | + | |
| - | $clean_info= $this->pdo->quote($info); | + | |
| - | $q = $this->pdo->query("SELECT COUNT(*) FROM personne WHERE pseudo=$clean_info;")->fetchColumn(); | + | |
| - | } | + | |
| - | + | ||
| - | return (bool)$q; | + | |
| } | } | ||
| - | } | + | |
| - | + | public function securConnexion() | |
| - | </code> | + | { |
| + | if(is_null($this->PDO_connexion)) | ||
| + | $this->getConnexion(); | ||
| + | return $this->PDO_connexion; | ||
| + | } | ||
| + | private function getConnexion() | ||
| + | { | ||
| + | if(!$this->getStatus()) | ||
| + | $this->PDO_connexion = parent::firstConnexion(); | ||
| + | return $this->PDO_connexion; | ||
| + | } | ||
| + | public function getErreurMessage() | ||
| + | { | ||
| + | return parent::getErreurMessage(); | ||
| + | } | ||
| + | public function getStatus() | ||
| + | { | ||
| + | return parent::getStatus(); | ||
| + | } | ||
| + | public function getCallingBy() | ||
| + | { | ||
| + | return $this->createBy ; | ||
| + | } | ||
| + | public function disconnectMysql() | ||
| + | { | ||
| + | parent::disconnectMysql(); | ||
| + | } | ||
| + | public function __destruct(){ | ||
| + | echo " <br/> Destructeur de gestionnairePDO "; | ||
| + | } | ||
| + | } | ||
| - | === Traitements === | + | </code> |
| + | |||
| + | === data_tree.php === | ||
| <code php> | <code php> | ||
| - | //file name : ManagerIdentification.class.php | ||
| <?php | <?php | ||
| - | error_reporting(E_ALL); | ||
| - | ini_set('display_errors', '1'); | ||
| - | require '../../dto/RequetesIdentification.class.php'; | ||
| - | require('Validations.class.php'); | ||
| - | class ManagerIdentification | + | require_once 'gestionnairePDO.php'; |
| - | { | + | |
| - | private $requeteIdentification; | + | |
| - | private $msg1 = "<p style='color:purple'>Vous n'êtes pas identifié...</p>"; | + | |
| - | private $msg2 = "<p style='color:purple'>Vous n'êtes plus identifié; veuillez vous identifier à nouveau...</p>"; | + | |
| - | private $msg3 = "<p style='color:red'>Pseudo et/ou mot de passe incorrect(s)...</p>"; | + | |
| - | private $msg4 = "<p style='color:blue'>Bienvenue </p>"; | + | |
| - | //// Conctructor //// | + | class DAO_dataTree |
| + | { | ||
| + | const F_NAME = __FILE__; | ||
| + | private $gestionPDO; | ||
| + | |||
| public function __construct() | public function __construct() | ||
| { | { | ||
| - | $this->requeteIdentification = new RequetesIdentification(); | + | $this->gestionPDO = new GestionPDO(self::F_NAME); |
| + | echo " <br/>Constructeur DAO_dataTree.php "; | ||
| + | } | ||
| + | |||
| + | public function getDataTreeCountryStateCity() | ||
| + | { | ||
| + | $q = "SELECT name FROM tree_country_state_city where id=1;"; | ||
| + | $result = $this->gestionPDO->securConnexion()->query($q)->fetch(); | ||
| + | |||
| + | //ou | ||
| + | //$result = $this->gestionPDO->queryFetch($q); | ||
| + | return $result; | ||
| + | |||
| } | } | ||
| + | } | ||
| + | </code> | ||
| + | === data_users.php === | ||
| + | <code php> | ||
| + | <?php | ||
| - | //////////////////////////////////// méthodes pour la page identification.php /////////////////////////////// | + | require_once 'gestionnairePDO.php'; |
| - | private function creerTableauInfosIdentification($pseudo, $motpasse) | ||
| - | { | ||
| - | $infos = array ( | ||
| - | 'pseudo' => $pseudo, | ||
| - | 'motPasse' => $motpasse, | ||
| - | ); | ||
| - | return $infos; | ||
| - | } | ||
| - | private function infosAffichageSubmitIdentification() | + | class DAO_dataUsers |
| + | { | ||
| + | |||
| + | const F_NAME = __FILE__; | ||
| + | private $gestionPDO; | ||
| + | |||
| + | public function __construct() | ||
| { | { | ||
| - | if ( $_SERVER["REQUEST_METHOD"] == "POST" && !empty($_POST)) | + | $this->gestionPDO = new GestionPDO(self::F_NAME); |
| - | { | + | echo " <br/>Constructeur DAO_dataUsers.php "; |
| - | $pseudo = htmlspecialchars_decode((trim($_POST['pseudoInput']))); | + | } |
| - | $motPasse = ""; | + | |
| - | $infos = $this->creerTableauInfosIdentification($pseudo, $motPasse); | + | public function getNomfromId($id) |
| - | //var_dump($infos); | + | { |
| - | return $infos; | + | $q = "SELECT name FROM users where id=" . $id . ";"; |
| - | } | + | $result = $this->gestionPDO->securConnexion()->query($q)->fetch(); |
| + | |||
| + | //ou | ||
| + | //$result = $this->gestionPDO->queryFetch($q); | ||
| + | return $result; | ||
| + | |||
| } | } | ||
| } | } | ||
| </code> | </code> | ||
| - | === Front dossier public par exemple === | + | === index.php === |
| <code php> | <code php> | ||
| - | // fale name : identification.php | ||
| - | |||
| <?php | <?php | ||
| - | error_reporting(E_ALL); | + | |
| - | ini_set('display_errors', '1'); | + | require 'data_tree.php'; |
| - | require '../../outils/ManagerIdentification.class.php'; | + | require 'data_users.php'; |
| + | |||
| + | $dataTree = new DAO_dataTree('index.php'); | ||
| + | |||
| + | $state = $dataTree->getDataTreeCountryStateCity(); | ||
| + | var_dump($state); | ||
| + | |||
| + | $dataUser = new DAO_dataUsers('index.php'); | ||
| + | |||
| + | $name = $dataUser->getNomfromId(1); | ||
| + | var_dump($name); | ||
| + | |||
| ?> | ?> | ||
| + | </code> | ||
| - | <!DOCTYPE html> | + | === Exécution du code === |
| - | <html> | + | |
| - | <head> | + | |
| - | <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> | + | |
| - | <title>formulaire d'identification</title> | + | |
| - | <link rel="stylesheet" href="../../style/masterPage.css"> | + | |
| - | <link rel="stylesheet" href="../../style/formulaire.css"> | + | |
| - | <link rel="icon" type="image/x-icon" href="../../images/hypathieIco.ico" /> | + | |
| - | </head> | + | |
| - | <body> | + | |
| - | <div class="header"> | + | |
| - | <?php include("../includes/headerPages.php"); ?> | + | |
| - | </div> | + | |
| - | <div class="body"> | + | <note tip> |
| - | <div class="validation"> | + | Comme attendu, nous avons bien qu'une seule fois "CREATION CONNEXION PDO MYSQL" ! |
| - | <?php | + | </note> |
| - | $managerIdentification = new ManagerIdentification(); | + | |
| - | $infos = $managerIdentification->infosVideIdentification(); | + | |
| - | $infos = $managerIdentification->creerIdentification(); | + | |
| - | + | ||
| - | ?> | + | |
| - | </div> | + | |
| - | <div class="formulaire"> | + | |
| - | <form action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]);?>" method="post"> | + | |
| - | <legend>S'identifier : </legend> | + | |
| - | <p><span class="error">* Champs obligatoires</span></p> | + | |
| - | <label>Pseudonyme</label> | + | |
| - | </br> | + | |
| - | <input type="text" name="pseudoInput" value="<?php echo $infos['pseudo'] ?>"> | + | |
| - | <span class="error">*</span><br /> | + | |
| - | <label>Mot de passe</label> | + | |
| - | </br> | + | |
| - | <input type="password" name="passInput" value="<?php echo $infos['motpasse'] ?>" | + | |
| - | placeholder="**************" | + | |
| - | > | + | |
| - | <span class="error">*</span><br /> | + | |
| - | <input type="submit" name="connexion" value="Connexion" | + | |
| - | <?php '<script type="text/javascript">location.reload(); </script>' ?> | + | |
| - | /> | + | |
| - | </form> | + | |
| - | </div> | + | |
| - | </div> | + | |
| - | <div class="footer"> | + | |
| - | <p>© 2020 comgocom.pw<p> | + | |
| - | </div> | + | |
| - | </body> | + | |
| - | </html> | + | * Dans le navigateur : **localhost/testSingleton/index.php** |
| - | <?php | + | |
| - | $managerIdentification->msgInfoCookie(); | + | |
| - | ?> | + | |
| - | </code> | + | Voir le wiki prendre en main apache pour installer l'alias ou le virtualhost "testSingleton". |
| + | <code retour> | ||
| + | CREATION CONNEXION PDO MYSQL | ||
| + | Constructeur GestionPDO.php calling by /home/hypathie/www/Test/public/data_tree.php. | ||
| + | Constructeur DAO_dataTree.php | ||
| + | /home/hypathie/www/Test/public/index.php:10: | ||
| + | array (size=2) | ||
| + | 'name' => string 'USA' (length=3) | ||
| + | 0 => string 'USA' (length=3) | ||
| + | |||
| + | Constructeur GestionPDO.php calling by /home/hypathie/www/Test/public/data_users.php. | ||
| + | Constructeur DAO_dataUsers.php | ||
| + | |||
| + | /home/hypathie/www/Test/public/\index.php:15: | ||
| + | array (size=2) | ||
| + | 'name' => string 'Jean-Marc' (length=9) | ||
| + | 0 => string 'Jean-Marc' (length=9) | ||
| + | |||
| + | </code> | ||
