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 Prochaine révision Les deux révisions suivantes | ||
utilisateurs:hypathie:tutos:php_connexion_mysql [08/07/2021 16:17] Hypathie [Objet PDO] |
utilisateurs:hypathie:tutos:php_connexion_mysql [08/07/2021 17:03] Hypathie [Objet PDO] |
||
---|---|---|---|
Ligne 8: | Ligne 8: | ||
===== BDD mysql/mariadb ===== | ===== BDD mysql/mariadb ===== | ||
- | ===== Objet PDO ===== | + | ==== 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> | ||
+ | <?php | ||
+ | error_reporting(E_ALL); | ||
+ | ini_set('display_errors', '1'); | ||
+ | |||
+ | class MysqlSingleton | ||
+ | { | ||
+ | const SQL_USER = 'root'; | ||
+ | const SQL_HOST = 'localhost'; | ||
+ | const SQL_PASS = '********'; | ||
+ | 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); | ||
+ | //self::$connect = new PDO('mysql:host=localhost;dbname=nath_test;charset=utf8','root', 'arawak'); | ||
+ | echo "CREATION CONNEXION PDO MYSQL"; | ||
+ | } | ||
+ | catch(PDOException $e) | ||
+ | { | ||
+ | $this->message=$e->getMessage(); | ||
+ | die(); | ||
+ | } | ||
+ | } | ||
+ | return self::$connect; | ||
+ | } | ||
+ | |||
+ | protected function getStatus() | ||
+ | { | ||
+ | return !is_null(self::$connect); | ||
+ | } | ||
+ | |||
+ | protected function getErreurMessage() | ||
+ | { | ||
+ | return $this->message; | ||
+ | } | ||
+ | |||
+ | protected function disconnectMysql() | ||
+ | { | ||
+ | self::$connect = null; | ||
+ | } | ||
+ | } | ||
+ | </code> | ||
+ | |||
+ | === gestionnairePDO.php === | ||
+ | |||
+ | <code php> | ||
+ | <?php | ||
+ | |||
+ | require 'MysqlSingleton.php'; | ||
+ | |||
+ | class GestionPDO extends MysqlSingleton | ||
+ | { | ||
+ | private $PDO_connexion = null; | ||
+ | private $callingBy = null; | ||
+ | | ||
+ | public function __construct($callingBy) | ||
+ | { | ||
+ | $this->PDO_connexion = parent::firstConnexion(); | ||
+ | $this->callingBy = $callingBy; | ||
+ | echo " <br/>Constructeur GestionPDO.php calling by " . $callingBy . ". "; | ||
+ | } | ||
+ | |||
+ | /*public function queryFetch($query) | ||
+ | { | ||
+ | return $this->securConnexion()->query($query)->fetch(); | ||
+ | }*/ | ||
+ | public function logSqlErreur() | ||
+ | { | ||
+ | } | ||
+ | |||
+ | public function securConnexion() | ||
+ | { | ||
+ | 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 "; | ||
+ | } | ||
+ | } | ||
+ | |||
+ | </code> | ||
+ | |||
+ | === data_tree.php === | ||
+ | |||
+ | <code php> | ||
+ | <?php | ||
+ | |||
+ | require_once 'gestionnairePDO.php'; | ||
+ | |||
+ | |||
+ | class DAO_dataTree | ||
+ | { | ||
+ | const F_NAME = __FILE__; | ||
+ | private $gestionPDO; | ||
+ | |||
+ | public function __construct() | ||
+ | { | ||
+ | $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 | ||
+ | |||
+ | require_once 'gestionnairePDO.php'; | ||
+ | |||
+ | |||
+ | class DAO_dataUsers | ||
+ | { | ||
+ | |||
+ | const F_NAME = __FILE__; | ||
+ | private $gestionPDO; | ||
+ | |||
+ | public function __construct() | ||
+ | { | ||
+ | $this->gestionPDO = new GestionPDO(self::F_NAME); | ||
+ | echo " <br/>Constructeur DAO_dataUsers.php "; | ||
+ | } | ||
+ | |||
+ | public function getNomfromId($id) | ||
+ | { | ||
+ | $q = "SELECT name FROM users where id=" . $id . ";"; | ||
+ | $result = $this->gestionPDO->securConnexion()->query($q)->fetch(); | ||
+ | |||
+ | //ou | ||
+ | //$result = $this->gestionPDO->queryFetch($q); | ||
+ | return $result; | ||
+ | |||
+ | } | ||
+ | } | ||
+ | </code> | ||
+ | |||
+ | === index.php === | ||
+ | |||
+ | <code php> | ||
+ | <?php | ||
+ | |||
+ | require 'data_tree.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> |