09/09
2017
Wallogit.com
2017 © Pedro Peláez
library db-connector
Wrapper for PDO
garkavenkov/db-connector
Wrapper for PDO
- Saturday, September 9, 2017
- by garkavenkov
- Repository
- 1 Watchers
- 0 Stars
- 29 Installations
- PHP
- 3 Dependents
- 0 Suggesters
- 0 Forks
- 0 Open issues
- 17 Versions
- 0 % Grown
The README.md
db-connector
DBConnect - Class-wrapper for PDO Class., (*1)
Installation
Use Composer, (*2)
composer require garkavenkov/db-connector, (*3)
Usage
Initialization
There are two ways to set database configuration parameters. First one is through constants., (*4)
<?php
require ('./vendor/autoload.php');
use DBConnector\DBConnect;
define('DB_USERNAME', 'username');
define('DB_PASSWORD', 'password');
define('DB_SCHEMA' , 'schema_name');
define('DB_DRIVER' , 'database_driver');
define('DB_HOSTNAME', 'hostname');
use DBConnector\DBConnect;
$dbh = DBConnect::getInstance();
Second one is through an associative array as a parameter in getInstance() method., (*5)
<?php
require ('./vendor/autoload.php');
use DBConnector\DBConnect;
$params = array(
"db_username" => 'username',
"db_password" => 'password',
"db_schema" => 'schema_name',
"db_driver" => 'database_driver',
"db_hostname" => 'hostname'
);
$dbh = DBConnect::getInstance($params);
By default database parameter db_port is empty, thus database driver uses its own default port. If you need to use specific port number you must define it as a constant, (*6)
<?php
define('DB_PORT', 9999);
or as an associative array., (*7)
<?php
$params = array(
.
.
.
"db_port" => 9999
);
Methods
exec(string $sql)
Executes an SQL statement and returns the number of affected rows, (*8)
<?php
// $dbh initialization
$sql = "DROP TABLE IF EXISTS test; ";
$sql .= "CREATE TABLE test ( " ;
$sql .= "id INT NOT NULL AUTO_INCREMENT, ";
$sql .= "first_name VARCHAR(20) NOT NULL, ";
$sql .= "last_name VARCHAR(25) NOT NULL, ";
$sql .= "PRIMARY KEY(id)";
$sql .= ") ENGINE=InnoDB DEFAULT CHARSET=UTF8";
$dbh->exec($sql);
$sql = "INSERT INTO `test` (`first_name`, `last_name` ) VALUES ";
$sql .= "('John', 'Doe'),('Jane', 'Smith')";
$res = $dbh->exec($sql);
echo "Records were inserted: $res" . PHP_EOL;
Output:, (*9)
Records were inserted: 2
query(string $sql)
Executes an SQL statement and returns an DBConnect object with result set as a PDOStatement object., (*10)
For example, this code set UTF8 code, (*11)
<?php $sql = "SELECT * FROM `test`"; $dbh->query($sql); var_dump($dbh);
Output:, (*12)
object(DBConnector\DBConnect)#1 (2) {
["dbh":"DBConnector\DBConnect":private]=>
object(PDO)#2 (0) {
}
["stmt":"DBConnector\DBConnect":private]=>
object(PDOStatement)#3 (1) {
["queryString"]=>
string(20) "SELECT * FROM `test`"
}
}
After this method you can chain another method that works with PDOStatement object. For example getRow(), (*13)
getRow($fetch_style = null)
Fetches a row from a result set. The $fetch_style determines how PDO returns the row. By default uses PDO::FETCH_ASSOC, (*14)
<?php $sql = "SELECT * FROM `test`"; $row = $dbh->query($sql)->getRow(); var_dump($row); echo "Character: " . $person['first_name'] . " " . $person['last_name'] . PHP_EOL;
Output:, (*15)
array(3) {
["id"]=>
string(1) "1"
["first_name"]=>
string(4) "John"
["last_name"]=>
string(4) "Doe"
}
Character: John Doe
For getting result as an object use PDO::FETCH_OBJ, (*16)
<?php $sql = "SELECT * FROM `test`"; $row = $dbh->query($sql)->getRow(PDO::FETCH_OBJ); var_dump($row); echo "Character: " . $person->first_name . " " . $person->last_name . PHP_EOL;
Output:, (*17)
object(stdClass)#4 (3) {
["id"]=>
string(1) "1"
["first_name"]=>
string(4) "John"
["last_name"]=>
string(4) "Doe"
}
Character: John Doe
getRows($fetch_style = null)
Returns an array containing all of the result set rows. Result depends on $fetch_style. By
default uses PDO::FETCH_ASSOC, (*18)
<?php
$sql = "SELECT * FROM `test`";
$persons = $dbh->query($sql)->getRows();
foreach($persons as $person) {
echo "Character: " . $person['first_name'] . " " . $person['last_name'] . PHP_EOL;
}
Character: John Doe Character: Jane Smith
prepare(string $sql, $standalone = false)
Prepares an SQL statement for execution and returns DBConnect object that contains prepared PDOstatement., (*19)
<?php $sql = "INSERT INTO `test` (`first_name`, `last_name`) "; $sql .= "VALUES (:first_name, :last_name)"; $stmt = $dbh->prepare($sql); var_dump($stmt)
Output:, (*20)
object(DBConnector\DBConnect)#1 (2) {
["dbh":"DBConnector\DBConnect":private]=>
object(PDO)#2 (0) {
}
["stmt":"DBConnector\DBConnect":private]=>
object(PDOStatement)#3 (1) {
["queryString"]=>
string(79) "INSERT INTO `test` (`first_name`, `last_name`) VALUES (:first_name, :last_name)"
}
}
If you set $standalone=true, this method returns a PDOstatement object rather than DBConnect object., (*21)
$sql = "INSERT INTO `test` (`first_name`, `last_name`) "; $sql .= "VALUES (:first_name, :last_name)"; $stmt = $dbh->prepare($sql, true); var_dump($stmt)
Output, (*22)
object(PDOStatement)#3 (1) {
["queryString"]=>
string(79) "INSERT INTO `test` (`first_name`, `last_name`) VALUES (:first_name, :last_name)"
}
execute(array $params, $stmt = null)
Executes a prepared statement., (*23)
<?php
$sql = "INSERT INTO `test` (`fist_name`, `last_name`) ";
$sql .= "VALUES (:first_name, :lst_name)";
$param = [
':first_name' => 'Fhil',
':last_name' => 'Johnson'
];
$dbh->prepare($sql)->execute($param);
If you set $stmt parameter this method will execute standalone prepared statement., (*24)
<?php
getLastInsertedId()
Returns Id from last inserted record, (*25)
<?php
$sql = "INSERT INTO `test` (`fist_name`, `last_name`) ";
$sql .= "VALUES (:first_name, :last_name)";
$param = [
':first_name' => 'Fhil',
':last_name' => 'Johnson'
];
$dbh->prepare($sql)->execute($param);
$id = $dbh->getLastInsertedId();
echo "Id: $id" . PHP_EOL;
Output:, (*26)
Id: 3
getFieldValue(string $field_name)
Returns value of particular field, (*27)
<?php
$sql = "SELECT * FROM `test` WHERE `id` = 1";
$name = $dbh->query($sql)->getFieldValue('first_name');
echo "Name: $name" . PHP_EOL;
Output:, (*28)
Name: John
getFieldValues(string $field_name)
Returns an array that contains values from given field, (*29)
<?php
$sql = "SELECT * FROM `test`";
$names = $dbh->query($sql)->getFieldValues('first_name');
print_r($names);
Output:, (*30)
Array
(
[0] => John
[1] => Jane
[2] => Fhil
)
rowCount()
Returns the number of rows affected by the last SQL statement., (*31)
<? php $sql = "SELECT * FROM `test`"; $count = $dbh->query($sql)->rowCount(); echo "Count: $count" . PHP_EOL;
Output:, (*32)
Count: 3
closeCursor()
Closes cursor for next execution., (*33)
getAvailableDrivers()
Returns an array of available PDO drivers., (*34)
<?php $drivers = $dbh->getAvailableDrivers(); print_r($drivers);
Output:, (*35)
Array
(
[0] => mysql
[1] => sqlite
)