logo Debian Debian Debian-France Debian-Facile Debian-fr.org Forum-Debian.fr Debian ? Communautés logo inclusivité

Debian-facile

Bienvenue sur Debian-Facile, site d'aide pour les nouveaux utilisateurs de Debian.

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 → ODT PDF Export

PHP singleton de connexion mysql

BDD mysql/mariadb

Créer une base

Script 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;

Executer le script

mysql -u root -p < ~/user/Test/nath_test.sql

Singleton php

└───public
      |_ MysqlSingleton.php 
      |_ gestionnairePDO.php
      |_ data_users.php
      |_ data_tree.php
      |_ index.php

MysqlSingleton.php

<?php
error_reporting(E_ALL);
ini_set('display_errors', '1');
 
class MysqlSingleton
{
    const SQL_USER = 'toto';
    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); 
		    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;
   }
}

gestionnairePDO.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()
    {
       // TODO
    }
 
    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  ";
    }	
}

data_tree.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 getCountryById($id)
    {
	$q = "SELECT name FROM tree_country_state_city where id=" . $id . ";";
	$result = $this->gestionPDO->securConnexion()->query($q)->fetch();
 
	//ou 
	//$result = $this->gestionPDO->queryFetch($q);
	return $result;
 
    }
}

data_users.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;
 
    }
}

index.php

<?php
 
	require 'data_tree.php';
	require 'data_users.php';
 
	$dataTree = new DAO_dataTree('index.php');
 
	$country = $dataTree->getCountryById(1);
	var_dump($country);
 
	$dataUser = new DAO_dataUsers('index.php');
 
	$name = $dataUser->getNomfromId(1);
	var_dump($name);	
 
?>

Exécution du code

Comme attendu, nous avons bien qu'une seule fois “CREATION CONNEXION PDO MYSQL” !
  • Dans le navigateur : localhost/testSingleton/index.php

Voir le wiki prendre en main apache pour installer l'alias ou le virtualhost “testSingleton”.

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)
utilisateurs/hypathie/tutos/php_connexion_mysql.txt · Dernière modification: 08/07/2021 17:34 par Hypathie

Pied de page des forums

Propulsé par FluxBB