====== PHP singleton de connexion mysql ======

  * Objet : code php de connexion
  * Niveau requis : {{tag>débutant}}
  * Commentaires : developpement web
  * Débutant, à savoir : [[:doc:systeme:commandes:le_debianiste_qui_papillonne|Utiliser GNU/Linux en ligne de commande, tout commence là !.]] :-)

===== BDD mysql/mariadb =====

==== 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 = '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;
   }
}
</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()
    {
       // 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  ";
    }	
}

</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 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;
			
    }
}
</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');
	
	$country = $dataTree->getCountryById(1);
	var_dump($country);

	$dataUser = new DAO_dataUsers('index.php');
	
	$name = $dataUser->getNomfromId(1);
	var_dump($name);	
	
?>
</code>

=== Exécution du code ===

<note tip>
Comme attendu, nous avons bien qu'une seule fois "CREATION CONNEXION PDO MYSQL" !
</note>

  * Dans le navigateur : **localhost/testSingleton/index.php**

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>