README

This library is for database connection in PHP using PDO.

Require

• PHP >= 7.0
• Composer installed

Start

The first you need install the library:

// Version 1.0.0
composer require gabriel-binotti/connection-pdo

Configuration .ini

After downloading, you need set the parameters in the database.ini file with your database connection data. If you prefer, you can create a new .ini file, the file is located in vendor/gabriel-binotti/database/databaseApplication/config/database.ini

DB_HOST    = host
DB_NAME    = name
DB_PORT    = port
DB_USER    = username
DB_PASS    = password
DB_TYPE    = type_connection 

• DB_HOST => hostname or IP
• DB_NAME => name of database
• DB_PORT => number porto to connection
• DB_USER => username
• DB_PASS => password
• DB_TYPE => type to batabase coneccetion tha value cad do(mysql, pgsql. srqsrv)

Import

Include the autoload.php file in the header file

use GabrielBinottiDatabase\Connection;
require "vendor/autoload.php";

Simple Struct

try{
    Connection::init('database');
        $pdo = Connection::get();    
        // Command SQL
    Connection::close();
    
}catch(Exception $e){
    echo $e->getMessage();
}

• Connection::init('database'): This method init() is called to initiate the database connection and your parameter is the file name configured before .ini.

• Connection::close(): This method should be called every time a database connection is opened.

• $pdo = Connection::get(): The method get() return the same instance of the class every time

All code should be between try and catch

Transactions

If you need to ensure that the transaction is successfully executed, you can initiate a transaction with the command Connection::transaction() and after execute SQL command. To finish using the command Connection::commit() when the transaction is successfully or Connection::rollback() to return to the initial state.

try{
    Connection::init('database');
    $pdo = Connection::get(); 
    Connection::transaction();
    // Command SQL

    Connection::commit();
    Connection::close();
    
}catch(Exception $e){
    Connection::rollback();
    echo $e->getMessage();
}

What use PDO?

Above I show like to use PDO in PHP with simple examples:

Select All

$stmt = $pdo->prepare("SELECT * FROM TABELA");
$stmt->execute();
$return = $stmt->fetchAll();

Select with where

$stmt = $pdo->prepare("SELECT * FROM TABELA WHERE id=:id");
$stmt->bindValue(':id',1);
$stmt->execute();
$return = $stmt->fetch();

By default is returned a object, if you need an array you can set parameter \PDO::FETCH_ASSOC in method fetchAll() or in method fetch().

Insert

$stmt = $pdo->prepare("INSERT INTO TABELA(nome, telefone) VALUES(:nome, :telefone) ");
$stmt->bindValue(":nome", "Valor");
$stmt->bindValue(":telefone", "Valor");
$return = $stmt->execute();

Update

$stmt = $pdo->prepare("UPDATE TABELA SET nome=:nome WHERE id=:id");
$stmt->bindValue(":id", "Valor");
$stmt->bindValue(":nome", "Valor");
$return = $stmt->execute();

Delete

$stmt = $pdo->prepare("DELETE FROM TABELA WHERE id=:id");
$stmt->bindValue(":id", "Valor");
$return = $stmt->execute();

bindValue VS bindParam

The simple form bindParam receives a variable like argument, not accepting direct values or functions return.

$var = 1;
$stmt->bindParam(':campo', $var);

The bindValue() accept direct values, variables and functions return.

$stmt->bindValue(':campo', 1);
$stmt->bindValue(':campo', $var);
$stmt->bindValue(':campo', $obj->getValor());

Third parameter bindValue or bindParam

The third parameter is the data type, by default is string, but you can change that accord with value type.

$stmt->bindValue(':campo', getValor(), PDO::PARAM_BOOL);               // reference bool value
$stmt->bindValue(':campo', getValor(), PDO::PARAM_NULL);               // reference null value
$stmt->bindValue(':campo', getValor(), PDO::PARAM_INT);                // reference int value 
$stmt->bindValue(':campo', getValor(), PDO::PARAM_STR);                // reference string value
$stmt->bindValue(':campo', getValor(), PDO::PARAM_LOB);                // reference object value to store large binary data.

SQL Server

To use SQL Server you need to install the driver and configure some lines in php.ini, as shown in the example above:

• Download => https://learn.microsoft.com/pt-br/sql/connect/php/download-drivers-php-sql-server?view=sql-server-ver15

• After you need copy the files to directory ext, my example using xampp

• To finish edit the file php.ini

// Version X86
extension=php_pdo_sqlsrv_73_ts_x86.dll
extension=php_sqlsrv_73_ts_x86.dll

// Version x64
extension=php_pdo_sqlsrv_73_ts_x64.dll
extension=php_sqlsrv_73_ts_x64.dll