Clasa PDO_MySQLi

Curs Php-mysql

PDO_MySQLi este o clasa PHP pentru conectare la MySQL si efectuarea de comenzi SQL foarte Usor si Sigur, folosind oricare din extensiile PDO sau MySQLi. Necesita PHP 5.4+.
Initial, clasa foloseste MySQLi, daca serverul PHP nu are MySQLi, clasa va folosi PDO. Se poate modifica usor setarea clasei ca sa foloseasca initial PDO.
Clasa aplica prepare() si execute() , astfel datele sunt adaugate in Siguranta in baza de date MySQL (fara sa mai fie necesara filtrare separata).

- Mai jos e codul clasei, il puteti copia sau il descarcati de la acest link: Download PDO_MySQLi
(Arhiva contine exemple si un fisier "readme.html" cu documentatie).

Metoda si Proprietati ale clasei pdo_mysqli

- $conn reprezinta instanta de obiect la clasa pdo_mysqli.

• $conn->sqlExec($sql, $values) - executa interogarea SQL (primita in $sql). Parametrul $values este Optional, si e un Array cu valori pentru inlocuitorii adaugati in sirul SQL.
  - La comenzi SELECT sau SHOW, metoda returneaza un Array cu datele selectate. La alte tipuri de comenzi returneaza True. In caz de eroare, returneaza False.
• $conn->mysql_version - un sir cu numarul versiunii serverului MySQL (de exemplu, daca versiunea serverului MySQL e 5.6.11, aceasta proprietate va returna: "5611").
• $conn->fetch - defineste cu ce fel de index sa returneze datele din setul de rezultate ('assoc' - cu numele coloanelor, 'num' - cu index-numeric al coloanelor, Default - ambele tipuri)
• $conn->group - seteaza cum sa returneze rezultatele Select. Valoarea "cols" returneaza un array grupat pe coloane. In rest, returneaza un array cu randurile selectate.
• $conn->affected_rows - contine numarul de randuri afectate de ultima instructiune Insert, Update sau Delete
• $conn->num_rows - numar de randuri selectate de instructiunea Select /Show.
• $conn->num_cols - numar de coloane din interogarea Select /Show.
• $conn->last_insertid - ultimul ID adaugat de comanda Insert in coloana AUTO_INCREMENT.
• $conn->nr_queries - numarul de interogari SQL efectuate.
• $conn->error - contine un sir cu eroare, daca apare, altfel e False.

Pentru comenzi SQL care afecteaza datele din baza de date MySQL, este indicata folosirea inlocuitorilor in sirul SQL (semne de intrebare sau nume), iar valorile asociate lor sa fie adaugate /transmise intr-un array. Astfel datele vor fi filtrate automat de PHP si adaugate in Siguranta in MySQL.

Utilizare PDO_MySQLi

1. Copiati clasa pdo_mysqli class pe serverul dv. (codul de mai jos intr-un fisier ".php", sau fisierul "class.pdo_mysqli.php" din arhiva descarcata).
2. In fisierul PHP in care veti folosi clasa, includeti acel fisier ("class.pdo_mysqli.php"), si creati o instanta de obiect la clasa pdo_mysqli, transmitand un array cu datele de conectare la MySQL; precum in acest exemplu.

<?php
// datele pt. conectare la baza de date MYSQL
$mysql = array(
 'host'=> 'localhost',
 'user'=> 'nume',
 'pass'=> 'parola',
 'bdname'=> 'baza_date'
);

include('class.pdo_mysqli.php'); // include fisierul cu clasa pdo_mysqli

// creaza obiectul cu conexiunea la MySQL
$conn = new pdo_mysqli($mysql);

3. Definiti instructiunea SQL (optional, array cu valori pentru inlocuitorii din comanda SQL), si apelati metoda sqlExec(), cu sirul SQL, si optional array-ul cu valori. Se pot folosi inlocuitori cu nume sau cu semnul intrebarii.

// Select folosind inlocuitor cu nume
$sql = "SELECT * FROM table_name WHERE column = :val";
$values = array('val'=>'value');
$rows = $conn->sqlExec($sql, $values);

// SAU, Select fara inlocuitor
$sql = "SELECT * FROM table_name WHERE id = 8";
$rows = $conn->sqlExec($sql);

// parcurge randurile selectate
foreach($rows AS $row) {
 echo $row['coloana'];
}

Exemplu 2 (gruparea rezultatelor din Select pe coloane):

// Select ca sa obtina datele pe coloane
$sql = "SELECT * FROM table_name WHERE column = :val";
$values = array('val'=>'value');
$conn->fetch = 'assoc'; //sa returneze datele asociate numai la nume-coloana
$conn->group = 'cols'; //sa returneze valorile grupate pe coloane
$cols = $conn->sqlExec($sql, $values);

// SAU, Select fara inlocuitor
$sql = "SELECT * FROM table_name WHERE id = 8";
$cols = $conn->sqlExec($sql);

// parcurge valorile din coloana 'col_name'
foreach($cols['col_name'] AS $val) {
 echo $val;
}

//reset $group la valoarea initiala, daca vreau ca urmatoarele Select sa returneze datele pe randuri
$conn->group = '';

- Cand se efectueaza comenzi Select sau Show, metoda sqlExec() returneaza un Array cu datele selectate.
- Datele returnate pot fi grupate pe randuri sau coloane, in functie de valoarea proprietatii $group.
- Array-ul returnat poate sa contina datele din randuri asociate la numele coloanei, asociate la numarul de ordine /index al coloanelor (primul index e 0), sau ambele tipuri; in functie de valoarea proprietatii $fetch.
Array-ul cu randurile poate fi parcurs folosind foreach() sau for().

Pentru diferite tipuri de comenzi SQL, testati si vedeti codul si comentariile din exemplele adaugate in pagina "examples.php", accesand acest fisier de pe server.

Clasa PDO_MySQLi

• Aceasta clasa este Gratuita, fara asistenta, o puteti folosi, modifica si publica liber.
- Clic pe cod pentru a-l selecta tot.

<?php
define('CONN_MOD', 'mysqli'); //sets default connection method: 'pdo', or 'mysqli'

// class to connect to MySQL, and perform easily and safe SQL queries
// From - https://coursesweb.net/php-mysql/
class pdo_mysqli {
 protected $conn_mod =''; // 'pdo', or 'mysqli'
 protected $conn = false; // stores the connection to mysql
 protected $conn_data = []; // to store data for connecting to database
 public $mysql_version =''; // mysql server version
 public $fetch =''; // 'assoc' - columns with named index, 'num' - columns numerically indexed, Else - both
 public $group =''; //If 'cols': returns data grouped by columns; Otherwise, return Select results grouped by Rows. 
 public $affected_rows =0; // number affected rows for Insert, Update, Delete
 public $num_rows =0; // number of rows from Select /Show results
 public $num_cols =0; // number of columns from Select /Show results
 public $last_insertid; // stores the last ID in an AUTO_INCREMENT column, after Insert query
 public $nr_queries =0; // to store number of sql queries
 public $error = false; // to store and check for errors

 function __construct($conn_data){
 $this->conn_data = $conn_data; //stores connection data to MySQL database
 }

 // to set the connection to mysql, with PDO, or MySQLi
 protected function setConn($conn_data) {
 // sets the connection method, check if can use pdo or mysqli (with MySQLnd)
 if(CONN_MOD == 'mysqli') {
 if(extension_loaded('mysqli') === true && function_exists('mysqli_get_client_stats')) $this->conn_mod ='mysqli';
 else if(extension_loaded('PDO') === true) $this->conn_mod ='pdo';
 }
 else if(CONN_MOD == 'pdo') {
 if(extension_loaded('PDO') === true) $this->conn_mod ='pdo';
 else if(extension_loaded('mysqli') === true && function_exists('mysqli_get_client_stats')) $this->conn_mod ='mysqli';
 }

 if($this->conn_mod == 'mysqli') $this->connMySQLi($conn_data);
 else if($this->conn_mod == 'pdo') $this->connPDO($conn_data);
 else $this->setSqlError('Unable to use PDO or MySQLi');

 //if error, output it
 if($this->error !== false) echo 'Error: '. $this->error;
 }

 // for connecting to mysql with PDO
 protected function connPDO($conn_data) {
 try {
 // Connect and create the PDO object
 $this->conn = new PDO("mysql:host=".$conn_data['host']."; dbname=".$conn_data['bdname'], $conn_data['user'], $conn_data['pass']);

 // Sets to handle the errors in the ERRMODE_EXCEPTION mode
 $this->conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
 $this->mysql_version = str_replace('.', '', $this->conn->getAttribute(PDO::ATTR_SERVER_VERSION));

 // Sets transfer with encoding UTF-8
 $this->conn->exec('SET character_set_client="utf8",character_set_connection="utf8",character_set_results="utf8";');
 }
 catch(PDOException $e) {
 $this->setSqlError($e->getMessage());
 }
 }

 // method that create the connection to mysql with MySQLi
 protected function connMySQLi($conn_data) {
 // if the connection is successfully established
 $this->conn = new mysqli($conn_data['host'], $conn_data['user'], $conn_data['pass'], $conn_data['bdname']);
 if(mysqli_connect_errno()){
 $this->setSqlError('MySQL connection failed: '. mysqli_connect_error());
 $this->conn = false;
 }
 else {
 $this->mysql_version = strval($this->conn->server_version);
 $this->conn->query('SET character_set_client="utf8",character_set_connection="utf8",character_set_results="utf8";');
 }
 }

 // Performs SQL queries
 // $sql - SQL query with prepared statement
 // $param - array of values for SQL query
 public function sqlExec($sql, $param=[]) {
 if($this->conn === false || $this->conn === NULL) $this->setConn($this->conn_data); // sets the connection to mysql

 // resets previous regstered data
 $this->affected_rows = 0;
 $this->num_rows = 0;
 $this->num_cols = 0;

 // if there is a connection set ($conn property not false)
 if($this->conn !== false){
 // gets the first word in $sql, to determine when SELECT query
 $mode = explode(' ', trim(preg_replace('/[^A-z ]+/i', '', str_ireplace(PHP_EOL, ' ', $sql))), 2);
 $mode = strtolower($mode[0]);
 $this->error = false; // to can perform current $sql if previous has error
 $nr_p = count($param); // number of elements for placeholders

 // code to replace ":placeholder" with "?" (for MySQLi)
 if($this->conn_mod == 'mysqli') {
 // check if number of :placeholders match number of items in $param. If they match, replace :placeholder with ? (for MySQLi)
 // else, replace :placeholder with its value, and empty $param
 if(preg_match_all('/:[^,|"|\'|;|\)\} ]*/i', $sql, $mt)){
 $nr_m = count($mt[0]);
 if($nr_p == $nr_m) $sql = preg_replace('/:[^,|"|\'|;|\)\} ]*/i','?', $sql);
 else {
 foreach($param AS $k => $v) {
 if(is_string($v)) $v = "'". str_replace("'", "\\'", $v) ."'";
 $sql = str_ireplace(':'. $k, $v, $sql);
 }
 $param = []; $nr_p = 0;
 }
 }
 }

 $sqlre = $this->conn->prepare($sql); // prepares statement

 // if successfully prepared execute query
 if(is_object($sqlre)){
 if($this->conn_mod == 'mysqli'){
 // if values in $param, sets to use "bind_param" before execute()
 if($nr_p >0){
 // stores in $args[0] the type of the value of each value in $param, the rest of items in $args are the values
 $args = [''];
 foreach($param AS $k=>$v) {
 if(is_int($v)) $args[0] .= 'i';
 else if(is_double($v)) $args[0] .= 'd';
 else $args[0] .= 's';
 $args[] = &$param[$k];
 }

 // binds the values with their types in prepared statement
 call_user_func_array([$sqlre,'bind_param'], $args);
 }

 if(!$sqlre->execute()) {
 if(isset($this->conn->error_list[0]['error'])) $this->setSqlError($this->conn->error_list[0]['error']);
 else $this->setSqlError('Unable to execute the SQL query, check if values are passed to sqlExec()');
 }
 }
 else if($this->conn_mod == 'pdo'){
 try { $sqlre->execute($param); }
 catch(PDOException $e) { $this->setSqlError($e->getMessage()); }
 }

 $this->nr_queries++; // to know number of sql queries
 }
 else {
 if(isset($this->conn->error_list[0]['error'])) $this->setSqlError($this->conn->error_list[0]['error']);
 else $this->setSqlError('Unable to prepare the SQL query, check if SQL query data are correctly');
 }

 // if no error
 if($this->error === false) {
 // if $mode is 'select' or 'show', gets the result_set to return
 if($mode == 'select' || $mode == 'show') {
 $re = ($this->conn_mod == 'mysqli') ? $this->getSelectMySQLi($sqlre) : $this->getSelectPDO($sqlre); // gets select results

 //set number of rows and items
 $nr_i = count($re); // number of items in results
 if(isset($re[0])) { $nr_i2 = ($this->fetch != 'assoc' && $this->fetch != 'num') ? count($re[0]) /2 : count($re[0]); }
 else $nr_i2 =0;
 if($this->group !='cols') {$this->num_rows = $nr_i; $this->num_cols = $nr_i2;}
 else {$this->num_rows = $nr_i2; $this->num_cols = $nr_i;}
 }
 else { $re = true; $this->affected_rows = ($this->conn_mod == 'mysqli') ? $sqlre->affected_rows : $sqlre->rowCount(); } // affected rows for Insert, Update, Delete

 // if Insert query, stores the last insert ID
 if($mode == 'insert') $this->last_insertid = ($this->conn_mod == 'mysqli') ? $this->conn->insert_id : $this->conn->lastInsertId();
 }
 }

 // sets to return false in case of error
 if($this->error !== false) $re = false;
 return $re;
 }

 // gets and returns Select results performed with PDO
 // receives the object created with prepare() statement
 protected function getSelectPDO($sqlre) {
 $re = [];

 // if fetch() returns at least one row (not false), adds the rows in $re, according to $fetch property
 $fetch = $this->fetch =='assoc' ? PDO::FETCH_ASSOC :($this->fetch =='num' ? PDO::FETCH_NUM : PDO::FETCH_BOTH);
 if($row = $sqlre->fetch($fetch)){
 do {
 // check each column if it has numeric value, to convert it from "string"
 foreach($row AS $k=>$v){
 if(is_numeric($v)) $row[$k] = $v + 0;
 if($this->group =='cols'){ //store by cols
 if(!isset($re[$k])) $re[$k] =[];
 $re[$k][] = $v;
 }
 }
 if($this->group !='cols') $re[] = $row; //store by rows
 }
 while($row = $sqlre->fetch($fetch));
 }
 return $re;
 }

 // gets and returns Select results performed with MySQLi
 // receives the object created with prepare() statement
 protected function getSelectMySQLi($sqlre) {
 $re = [];
 $result = $sqlre->get_result(); //get result set from a prepared /execute statement

 // gets array with results according to $fetch
 $fetch = $this->fetch =='assoc' ? MYSQLI_ASSOC :($this->fetch =='num' ? MYSQLI_NUM : MYSQLI_BOTH);
 while($row = $result->fetch_array($fetch)){
 if($this->group !='cols') $re[] = $row; //get by rows
 else { //get by columns
 foreach($row AS $k=>$v){
 if(!isset($re[$k])) $re[$k] =[];
 $re[$k][] = $v;
 }
 }
 }
 return $re;
 }

 // set sql error in $error
 protected function setSqlError($err) {
 $this->error = $err ;
 }
}

<p>Adresa: <strong>http://CoursesWeb.net/</strong> - Tutoriale.</p>

#id {
  font-weight: 800;
}

function someFunction() { alert("CoursesWeb.net"); }
setInterval("someFunction()", 2000);

$vname = 8;
echo $vname;

We have traveled in space.
- Noi am calatorit (deja) in spatiu.

La niña pintaba un arco iris.
- Fetita picta un curcubeu.