PHP Classes

PHP PDO One: Access to database with PDO and run common queries

Recommend this page to a friend!
  Info   View files Example   View files View files (43)   DownloadInstall with Composer Download .zip   Reputation   Support forum   Blog    
Ratings Unique User Downloads Download Rankings
Not yet rated by the usersTotal: 287 All time: 7,523 This week: 150Up
Version License PHP version Categories
pdoone 1.0.1GNU Lesser Genera...5PHP 5, Databases
Description 

Author

This package can access to database with PDO and run common queries.

It can connect to a given database supported by PDO and executes several types of common operations to process SQL queries. Currently it can:

- Run an unprepared queries
- Run a prepared query with parameters
- Retrieve query results
- Run a transaction
- Make the package throw exceptions when errors happen
- Check if a connection was opened
- Check if a table exists
- Get access statistics values from a table and a give field
- Get all columns of a table
- Get a table foreign keys
- Create a table
- Build and run SELECT queries with options to get distinct rows, group results by column values, or having certain values
- Build and run INSERT, UPDATE and DELETE queries from given parameter values
- Create sequences and return their values

Picture of Jorge Castro
  Performance   Level  
Name: Jorge Castro <contact>
Classes: 30 packages by
Country: Chile Chile
Age: 48
All time rank: 12763 in Chile Chile
Week rank: 103 Up1 in Chile Chile Up
Innovation award
Innovation award
Nominee: 14x

Winner: 2x

Example

<?php

use eftec\PdoOne;
use
mapache_commons\Collection;

include
"../vendor/autoload.php";
include
"Collection.php";
include
"dBug.php";
echo
"<body><div style='width:600px'>";
// connecting to database sakila at 127.0.0.1 with user root and password abc.123
$dao=new PdoOne("mysql","127.0.0.1","root","abc.123","sakila","logpdoone.txt");
try {
    echo
"<h1>Connection. The instance {$dao->server}, base:{$dao->db} user:{$dao->user} and password:{$dao->pwd} must exists</h1>";
   
$dao->connect();
    echo
"Connected A-OK!<br>";
} catch (
Exception $e) {
    echo
"<h2>connection error:</h2>";
    echo
$dao->lastError()."-".$e->getMessage()."<br>";
    die(
1);
}
// creating tables
echo "<hr>Dropping and Creating table<br>";
try {
   
$dao->runRawQuery('drop table myproducts');
} catch (
Exception $e) {
}
$sqlT1="CREATE TABLE `myproducts` (
    `idproduct` INT NOT NULL,
    `name` VARCHAR(45) NULL,
    `type` VARCHAR(45) NULL,
    `id_category` INT NOT NULL,
    PRIMARY KEY (`idproduct`));"
;

try {
   
$dao->runRawQuery($sqlT1);
} catch (
Exception $e) {
    echo
$e->getMessage()."<br>";
}
echo
"<hr>Dropping And Creating table<br>";
try {
   
$dao->runRawQuery('drop table product_category');
} catch (
Exception $e) {
}
$sqlT2="CREATE TABLE `product_category` (
    `id_category` INT NOT NULL,
    `catname` VARCHAR(45) NULL,
    PRIMARY KEY (`id_category`));"
;

try {
   
$dao->runRawQuery($sqlT2);
} catch (
Exception $e) {
    echo
$e->getMessage()."<br>";
}
echo
"<hr>adding<br>";
// adding some data
try {
   
$dao->set(['id_category' => 1, 'catname' => 'cheap'])
        ->
from('product_category')->insert();
    echo
"added<br>";
   
$dao->set(['id_category'=>2,'catname'=>'normal'])
        ->
from('product_category')->insert();
    echo
"added<br>";
   
$dao->set(['id_category'=>3,'catname'=>'expensive'])
        ->
from('product_category')->insert();
    echo
"added<br>";
} catch (
Exception $e) {
}
echo
"<hr>adding<br>";
// adding categories
try {
   
$dao->set(['idproduct'=>1,'name'=>'cocacola'
       
,'type'=>'drink','id_category'=>1])
        ->
from("myproducts")->insert();
   
$dao->set(['idproduct'=>2,'name'=>'fanta'
       
,'type'=>'drink','id_category'=>1])
        ->
from("myproducts")->insert();
   
$dao->set(['idproduct'=>3,'name'=>'sprite'
       
,'type'=>'drink','id_category'=>1])
        ->
from("myproducts")->insert();
   
$dao->set(['idproduct'=>4,'name'=>'iphone'
       
,'type'=>'phone','id_category'=>2])
        ->
from("myproducts")->insert();
   
$dao->set(['idproduct'=>5,'name'=>'galaxy note'
       
,'type'=>'phone','id_category'=>2])
        ->
from("myproducts")->insert();
   
$dao->set(['idproduct'=>6,'name'=>'xiami'
       
,'type'=>'phone','id_category'=>2])
        ->
from("myproducts")->insert();
   
$dao->set(['idproduct'=>7,'name'=>'volvo',
       
'type'=>'car','id_category'=>3])
        ->
from("myproducts")->insert();
   
$dao->set(['idproduct'=>8,'name'=>'bmw'
       
,'type'=>'car','id_category'=>3])
        ->
from("myproducts")->insert();
} catch (
Exception $e) {
}

// list products
$products=$dao->runRawQuery("select * from myproducts",null,true);
echo
Collection::generateTable($products);

// Listing using procedure call
$products=$dao->select("*")->from("myproducts")->toList();
echo
Collection::generateTable($products);

// list join (we could even add having()
$products=$dao->select("*")->from("myproducts my")
    ->
join("product_category p on my.id_category=p.id_category")->toList();
echo
Collection::generateTable($products);
// Let's clean the join
$products=$dao->select("name,type,catname")->from("myproducts my")
    ->
join("product_category p on my.id_category=p.id_category")->toList();
echo
Collection::generateTable($products);

// list join order
$products=$dao->select("name,type,catname")->from("myproducts my")
    ->
join("product_category p on my.id_category=p.id_category")
    ->
order("name")->toList();
echo
Collection::generateTable($products);

// We also could obtain the first value (or the last)
$products=$dao->select("name,type,catname")->from("myproducts my")
    ->
join("product_category p on my.id_category=p.id_category")->first();
echo
Collection::generateTable($products);

// We also could obtain an escalar. It's useful if you want, for example, returns the number of elements.
$products=$dao->select("count(*)")->from("myproducts my")
    ->
join("product_category p on my.id_category=p.id_category")->firstScalar();
echo
Collection::generateTable($products);

// And, we could add limit
$products=$dao->select("*")->from("myproducts my")
    ->
join("product_category p on my.id_category=p.id_category")
    ->
order("name")->limit("1,3")->toList();
echo
Collection::generateTable($products);

// And we could group
$products=$dao->select("catname,count(*) count")
    ->
from("myproducts my")
    ->
join("product_category p on my.id_category=p.id_category")
    ->
group("catname")
    ->
toList();
echo
Collection::generateTable($products);

die(
1);

$now=new DateTime();
// running a raw query (unprepared statement)
try {
    echo
"<h1>Table creation (it's ok if it fails if exists):</h1>";
   
$dao->runRawQuery($sqlT1);
    echo
$dao->lastQuery."<br>";
   
$dao->runRawQuery($sqlT2);
    echo
$dao->lastQuery."<br>";
} catch (
Exception $e) {
    echo
"<h2>Table creation error:</h2>";
    echo
$dao->lastError()."-".$e->getMessage()."<br>";
}


try {
    echo
"<hr>toList:";
   
$results = $dao->select("*")->from("producttype")
        ->
where('name=?', ['s', 'Coca-Cola'])
        ->
where('idproducttype=?', ['i', 1])
        ->
toList();
    echo
$dao->lastQuery;
    echo
Collection::generateTable($results);

    echo
"<hr>toList:";
   
$results = $dao->select("*")->from("producttype")
        ->
where("name='Coca-Cola'")
        ->
toList();
    echo
$dao->lastQuery;
    echo
Collection::generateTable($results);

    echo
"<hr>toList using associative array:";
   
$results = $dao->select("*")->from("producttype")
        ->
where(['name'=>'Coca-Cola','idproducttype'=>1])
        ->
toList();
    echo
$dao->lastQuery;
    echo
Collection::generateTable($results);

    echo
"<hr>toList using associative array:";
   
$results = $dao->select("*")->from("producttype")
        ->
where(['name'=>'s','idproducttype'=>'i'],
            [
'name'=>'Coca-Cola','idproducttype'=>1])
        ->
toList();
    echo
$dao->lastQuery;
    echo
Collection::generateTable($results);

    echo
"<hr>toList using associative array:";
   
$results = $dao->select("*")->from("producttype")
        ->
where(['name','s','idproducttype','i'],
            [
'Coca-Cola',1])
        ->
toList();
    echo
$dao->lastQuery;
    echo
Collection::generateTable($results);

    echo
"<hr>toList using associative array:";
   
$results = $dao->select("*")->from("producttype")
        ->
where(['name','s','Coca-Cola','idproducttype','i',1])
        ->
toList();
    echo
$dao->lastQuery;
    echo
Collection::generateTable($results);

    echo
"<hr>toList (from join):";
   
$results = $dao->select("pt.*,tt.name typetable_name")
        ->
from("producttype pt")
        ->
join("typetable tt on pt.type=tt.type")
        ->
toList();
    echo
$dao->lastQuery;
    echo
Collection::generateTable($results);

    echo
"<hr>toList (join left):";
   
$results = $dao->select("pt.*,tt.name typetable_name")
        ->
join("producttype pt")
        ->
left("typetable tt on pt.type=tt.type")
        ->
toList();
    echo
$dao->lastQuery;
    echo
Collection::generateTable($results);

    echo
"<hr>toList (join left):";
   
$results = $dao->select("pt.*,tt.name typetable_name")
        ->
join("producttype pt")
        ->
left("typetable tt on pt.type=tt.type")
        ->
first();
    echo
$dao->lastQuery;
    echo
Collection::generateTable($results);

    echo
"<hr>toList: ";
   
$results = $dao->select("*")->from("producttype")
        ->
where('idproducttype>=?', ['i', 1])
        ->
order('idproducttype desc')
        ->
toList();
    echo
$dao->lastQuery;
    echo
Collection::generateTable($results);


    echo
"<hr>toResult: ";
   
$resultsQuery = $dao->select("*")->from("producttype")
        ->
where('name=?', ['s', 'Coca-Cola'])
        ->
where('idproducttype=?', ['i', 1])
        ->
toResult();
    echo
$dao->lastQuery;
   
$results=$resultsQuery->fetch_all(PDO::FETCH_ASSOC);
    echo
Collection::generateTable($results);
   
$resultsQuery->free_result();

    echo
"<hr>first: ";
   
$results = $dao->select("*")->from("producttype")
        ->
where('name=?', ['s', 'Coca-Cola'])
        ->
where('idproducttype=?', ['i', 1])
        ->
limit('1')
        ->
first();
    echo
$dao->lastQuery;
    echo
Collection::generateTable($results);

    echo
"<hr>first returns nothing :";
   
$results = $dao->select("*")->from("producttype")
        ->
where('name=?', ['s', 'Coca-Cola'])
        ->
where('idproducttype=?', ['i', 55])
        ->
limit('1')
        ->
first();
    echo
$dao->lastQuery;
    echo
"<br><pre>";
   
var_dump($results);
    echo
"</pre>";

    echo
"<hr>";
   
$results = $dao->select("*")->from("producttype")
        ->
where('idproducttype=1')
        ->
runGen();
    echo
$dao->lastQuery;
    echo
Collection::generateTable($results);

    echo
"<hr>";
   
$results = $dao->select("*")->from("producttype p")
        ->
where('idproducttype between ? and ?', ['i', 1, 'i', 3])
        ->
toList();
    echo
$dao->lastQuery;

    echo
Collection::generateTable($results);

    echo
"<hr>";
   
$results = $dao->select("p.type,count(*) c")->from("producttype p")
        ->
group("p.type")
        ->
having('p.type>?',['i',0])
        ->
toList();
    echo
$dao->lastQuery;
    echo
Collection::generateTable($results);



} catch(
Exception $ex) {
    echo
$ex->getMessage();
    echo
$ex->getTraceAsString();
}

echo
"</div></body>";
function
build_table($array){
    if (!isset(
$array[0])) {
       
$tmp=$array;
       
$array=array();
       
$array[0]=$tmp;
    }
// create an array with a single element
   
if ($array[0]===null) {
        return
"NULL<br>";
    }
   
// start table
   
$html = '<table style="border: 1px solid black;">';
   
// header row
   
$html .= '<tr>';
    foreach(
$array[0] as $key=>$value){
       
$html .= '<th>' . htmlspecialchars($key) . '</th>';
    }
   
$html .= '</tr>';

   
// data rows
   
foreach( $array as $key=>$value){
       
$html .= '<tr>';
        foreach(
$value as $key2=>$value2){
           
$html .= '<td>' . htmlspecialchars($value2) . '</td>';
        }
       
$html .= '</tr>';
    }

   
// finish table and return it

   
$html .= '</table>';
    return
$html;
}


Details

Database Access Object wrapper for PHP and PDO in a single class

PdoOne. It's a simple wrapper for PHP's PDO library.

This library is as fast as possible. Most of the operations are simple string/array managements.

Build Status Packagist Total Downloads [Maintenance]() [composer]() [php]() [php]() [CocoaPods]()

Turn this

$stmt->bind_param("s", $_POST['name']);
$stmt->execute();
$result = $stmt->get_result();
if($result->num_rows === 0) exit('No rows');
while($row = $result->fetch_assoc()) {
  $ids[] = $row['id'];
  $names[] = $row['name'];
  $ages[] = $row['age'];
}
var_export($ages);
$stmt->close();

into this

$products=$dao
    ->select("*")
    ->from("myTable")
    ->where("name = ?",[$_POST['name']])
    ->toList();

Table of Content

Install (using composer)

>

Add to composer.json the next requirement, then update composer.

  {
      "require": {
        "eftec/PdoOne": "^1.6"
      }
  }

or install it via cli using

> composer require eftec/PdoOne

Install (manually)

Just download the file lib/PdoOne.php and save it in a folder.

Usage

Start a connection

$dao=new PdoOne("mysql","127.0.0.1","root","abc.123","sakila","");
$dao->connect();

where * "mysql" is the mysql database. It also allows sqlsrv (for sql server) * 127.0.0.1 is the server where is the database. * root is the user * abc.123 is the password of the user root. * sakila is the database used. * "" (optional) it could be a log file, such as c:\temp\log.txt

Run an unprepared query

$sql="CREATE TABLE `product` (
    `idproduct` INT NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(45) NULL,
    PRIMARY KEY (`idproduct`));";
$dao->runRawQuery($sql);  

Run a prepared query

$sql="insert into `product`(name) values(?)";
$stmt=$dao->prepare($sql);
$productName="Cocacola";
$stmt->bind_param("s",$productName); // s stand for string. Also i =integer, d = double and b=blob
$dao->runQuery($stmt);

> note: you could also insert using a procedural chain [insert($table,$schema,[$values])](#insert--table--schema---values--)

Run a prepared query with parameters.

$dao->runRawQuery('insert into `product` (name) values(?)'
    ,array('s','cocacola'));

Return data (first method)

It returns a mysqli_statement.

    $sql="select * from `product` order by name";
    $stmt=$dao->prepare($sql);
    $dao->runQuery($stmt);
    $rows = $stmt->get_result();
    while ($row = $rows->fetch_assoc()) {
        var_dump($row);
    }
    

> This statement must be processed manually.

Return data (second method)

It returns an associative array.

    $sql="select * from `product` order by name";
    $stmt=$dao->prepare($sql);
    $dao->runQuery($stmt);
    $rows = $stmt->get_result();
    $allRows=$rows->fetch_all(PDO::FETCH_ASSOC);
    var_dump($allRows);

Running a transaction

try {
    $sql="insert into `product`(name) values(?)";
    $dao->startTransaction();
    $stmt=$dao->prepare($sql);
    $productName="Fanta";
    $stmt->bind_param("s",$productName); 
    $dao->runQuery($stmt);
    $dao->commit(); // transaction ok
} catch (Exception $e) {
    $dao->rollback(false); // error, transaction cancelled.
}

startTransaction()

It starts a transaction

commit($throw=true)

It commits a transaction. * If $throw is true then it throws an exception if the transaction fails to commit. Otherwise, it does not.

rollback($throw=true)

It rollbacks a transaction. * If $throw is true then it throws an exception if the transaction fails to rollback. If false, then it ignores if the rollback fail or if the transaction is not open.

Fields

throwOnError=true

If true (default), then it throws an error if happens an error. If false, then the execution continues

isOpen=true

It is true if the database is connected otherwise,it's false.

Custom Queries

tableExist($tableName)

Returns true if the table exists (current database/schema)

statValue($tableName,$columnName)

Returns the stastictic (as an array) of a column of a table.

$stats=$dao->statValue('actor','actor_id');

| min | max | avg | sum | count | |-----|-----|----------|-------|-------| | 1 | 205 | 103.0000 | 21115 | 205 |

columnTable($tablename)

Returns all columns of a table

$result=$dao->columnTable('actor');

| colname | coltype | colsize | colpres | colscale | iskey | isidentity | |-------------|-----------|---------|---------|----------|-------|------------| | actor_id | smallint | | 5 | 0 | 1 | 1 | | first_name | varchar | 45 | | | 0 | 0 | | last_name | varchar | 45 | | | 0 | 0 | | last_update | timestamp | | | | 0 | 0 |

foreignKeyTable($tableName)

Returns all foreign keys of a table (source table)

createTable($tableName,$definition,$primaryKey=null,$extra='')

Creates a table using a definition and primary key.

$result=$dao->foreignKeyTable('actor');

| collocal | tablerem | colrem | |-------------|----------|-------------| | customer_id | customer | customer_id | | rental_id | rental | rental_id | | staff_id | staff | staff_id |

Query Builder (DQL)

You could also build a procedural query.

Example:

$results = $dao->select("*")->from("producttype")
    ->where('name=?', ['s', 'Cocacola'])
    ->where('idproducttype=?', ['i', 1])
    ->toList();   

select($columns)

Generates a select command.

$results = $dao->select("col1,col2"); //...

> Generates the query: select col1,col2 ....

$results = $dao->select("select * from table"); //->...

> Generates the query: select * from table ....

count($sql,$arg='*')

Generates a query that returns a count of values. It is a macro of the method select()

$result = $dao->count('from table where condition=1')->firstScalar(); // select count(*) from table where c..
$result = $dao->count('from table','col1')->firstScalar(); // select count(col1) from table

min($sql,$arg='*')

Generates a query that returns the minimum value of a column. If $arg is empty then it uses $sql for the name of the column It is a macro of the method select()

$result = $dao->min('from table where condition=1','col')->firstScalar(); // select min(col) from table where c..
$result = $dao->min('from table','col1')->firstScalar(); // select min(col1) from table
$result = $dao->min('','col1')->from('table')->firstScalar(); // select min(col1) from table
$result = $dao->min('col1')->from('table')->firstScalar(); // select min(col1) from table

max($sql,$arg='*')

Generates a query that returns the maximum value of a column. If $arg is empty then it uses $sql for the name of the column It is a macro of the method select()

$result = $dao->max('from table where condition=1','col')->firstScalar(); // select max(col) from table where c..
$result = $dao->max('from table','col1')->firstScalar(); // select max(col1) from table

sum($sql,$arg='*')

Generates a query that returns the sum value of a column. If $arg is empty then it uses $sql for the name of the column It is a macro of the method select()

$result = $dao->sum('from table where condition=1','col')->firstScalar(); // select sum(col) from table where c..
$result = $dao->sum('from table','col1')->firstScalar(); // select sum(col1) from table

avg($sql,$arg='*')

Generates a query that returns the average value of a column. If $arg is empty then it uses $sql for the name of the column It is a macro of the method select()

$result = $dao->avg('from table where condition=1','col')->firstScalar(); // select avg(col) from table where c..
$result = $dao->avg('from table','col1')->firstScalar(); // select avg(col1) from table

distinct($distinct='distinct')

Generates a select command.

$results = $dao->select("col1,col2")->distinct(); //...

> Generates the query: select distinct col1,col2 ....

>Note: ->distinct('unique') returns select unique ..

from($tables)

Generates a from command.

$results = $dao->select("*")->from('table'); //...

> Generates the query: select from table*

$tables could be a single table or a sql construction. For examp, the next command is valid:

$results = $dao->select("*")->from('table t1 inner join t2 on t1.c1=t2.c2'); //...

where($where,[$arrayParameters=array()])

Generates a where command.

  • $where is an array or a string. If it's a string, then it's evaluated by using the parameters. if any
$results = $dao->select("*")
->from('table')
->where('p1=1'); //...

> Generates the query: select from table* where p1=1

> Note: ArrayParameters is an array as follow: type,value. > Where type is i=integer, d=double, s=string or b=blob. In case of doubt, use "s" > Example of arrayParameters: > ['i',1 ,'s','hello' ,'d',20.3 ,'s','world']

$results = $dao->select("*")
->from('table')
->where('p1=?',['i',1]); //...

> Generates the query: select from tablewhere p1=?(1)*

$results = $dao->select("*")
->from('table')
->where('p1=? and p2=?',['i',1,'s','hello']); //...

> Generates the query: select from tablewhere p1=?(1) and p2=?('hello')*

> Note. where could be nested.

$results = $dao->select("*")
->from('table')
->where('p1=?',['i',1])
->where('p2=?',['s','hello']); //...

> Generates the query: select from tablewhere p1=?(1) and p2=?('hello')*

You could also use:

$results = $dao->select("*")->from("table")
    ->where(['p1'=>'Coca-Cola','p2'=>1])
    ->toList();

> Generates the query: select from tablewhere p1=?(Coca-Cola) and p2=?(1)*

You could also use an associative array as argument and named parameters in the query

$results = $dao->select("*")->from("table")
    ->where('condition=:p1 and condition2=:p2',['p1'=>'Coca-Cola','p2'=>1])
    ->toList();

> Generates the query: select from tablewhere condition=?(Coca-Cola) and condition2=?(1)*

order($order)

Generates a order command.

$results = $dao->select("*")
->from('table')
->order('p1 desc'); //...

> Generates the query: select from tableorder by p1 desc*

group($group)

Generates a group command.

$results = $dao->select("*")
->from('table')
->group('p1'); //...

> Generates the query: select from tablegroup by p1*

having($having,[$arrayParameters])

Generates a group command.

$results = $dao->select("*")
->from('table')
->group('p1')
->having('p1>?',array('i',1)); //...

> Generates the query: select * from table group by p1 having p1>?(1)

> Note: Having could be nested having()->having() > Note: Having could be without parameters having('col>10')

runGen($returnArray=true)

Run the query generate.

>Note if returnArray is true then it returns an associative array. > if returnArray is false then it returns a mysqli_result >Note: It resets the current parameters (such as current select, from, where,etc.)

toList($pdoMode)

It's a macro of runGen. It returns an associative array or null.

$results = $dao->select("*")
->from('table')
->toList(); 

toMeta()

It returns a metacode of each columns of the query.

$results = $dao->select("*")
->from('table')
->toMeta(); 

result:

array(3) {
  [0]=>
  array(7) {
    ["native_type"]=>
    string(4) "LONG"
    ["pdo_type"]=>
    int(2)
    ["flags"]=>
    array(2) {
      [0]=>
      string(8) "not_null"
      [1]=>
      string(11) "primary_key"
    }
    ["table"]=>
    string(11) "producttype"
    ["name"]=>
    string(13) "idproducttype"
    ["len"]=>
    int(11)
    ["precision"]=>
    int(0)
  }
  [1]=>
  array(7) {
    ["native_type"]=>
    string(10) "VAR_STRING"
    ["pdo_type"]=>
    int(2)
    ["flags"]=>
    array(0) {
    }
    ["table"]=>
    string(11) "producttype"
    ["name"]=>
    string(4) "name"
    ["len"]=>
    int(135)
    ["precision"]=>
    int(0)
  }
}

toListSimple()

It's a macro of runGen. It returns an indexed array from the first column

$results = $dao->select("*")
->from('table')
->toListSimple(); // ['1','2','3','4']

toListKeyValue()

It returns an associative array where the first value is the key and the second is the value. If the second value does not exist then it uses the index as value (first value).

$results = $dao->select("cod,name")
->from('table')
->toListKeyValue(); // ['cod1'=>'name1','cod2'=>'name2']

toResult()

It's a macro of runGen. It returns a mysqli_result or null.

$results = $dao->select("*")
->from('table')
->toResult(); //

firstScalar($colName=null)

It returns the first scalar (one value) of a query. If $colName is null then it uses the first column.

$count=$this->pdoOne->count('from product_category')->firstScalar();

first()

It's a macro of runGen. It returns the first row if any, if not then it returns false, as an associative array.

$results = $dao->select("*")
->from('table')
->first(); 

last()

It's a macro of runGen. It returns the last row (if any, if not, it returns false) as an associative array.

$results = $dao->select("*")
->from('table')
->last(); 

> Sometimes is more efficient to run order() and first() because last() reads all values.

sqlGen()

It returns the sql command.

$sql = $dao->select("*")
->from('table')
->sqlGen();
echo $sql; // returns select * from table
$results=$dao->toList(); // executes the query

> Note: it doesn't reset the query.

Query Builder (DML), i.e. insert, update,delete

There are four ways to execute each command.

Let's say that we want to add an integer in the column col1 with the value 20

__Schema and values using a list of values__: Where the first value is the column, the second is the type of value (i=integer,d=double,s=string,b=blob) and second array contains the values.

$dao->insert("table"
    ,['col1','i']
    ,[20]);

__Schema and values in the same list__: Where the first value is the column, the second is the type of value (i=integer,d=double,s=string,b=blob) and the third is the value.

$dao->insert("table"
    ,['col1','i',20]);

__Schema and values using two associative arrays__:

$dao->insert("table"
    ,['col1'=>'i']
    ,['col1'=>20]);

__Schema and values using a single associative array__: The type is calculated automatically.

$dao->insert("table"
    ,['col1'=>20]);

insert($table,$schema,[$values])

Generates a insert command.

$dao->insert("producttype"
    ,['idproducttype','i','name','s','type','i']
    ,[1,'cocacola',1]);

Using nested chain (single array)

    $dao->from("producttype")
        ->set(['idproducttype','i',0 ,'name','s','Pepsi' ,'type','i',1])
        ->insert();

Using nested chain multiple set

    $dao->from("producttype")
        ->set("idproducttype=?",['i',101])
        ->set('name=?',['s','Pepsi'])
        ->set('type=?',['i',1])
        ->insert();

or (the type is defined, in the possible, automatically by MySql)

    $dao->from("producttype")
        ->set("idproducttype=?",['i',101])
        ->set('name=?','Pepsi')
        ->set('type=?',1)
        ->insert();

insertObject($table,[$declarativeArray],$excludeColumn=[])

    $dao->insertObject('table',['Id'=>1,'Name'=>'CocaCola']);


Using nested chain declarative set

    $dao->from("producttype")
        ->set('(idproducttype,name,type) values (?,?,?)',['i',100,'s','Pepsi','i',1])
        ->insert();

> Generates the query: insert into productype(idproducttype,name,type) values(?,?,?) ....

update($$table,$schema,$values,[$schemaWhere],[$valuesWhere])

Generates a insert command.

$dao->update("producttype"
    ,['name','s','type','i'] //set
    ,[6,'Captain-Crunch',2] //set
    ,['idproducttype','i'] // where
    ,[6]); // where

$dao->update("producttype"
    ,['name'=>'Captain-Crunch','type'=>2] // set
    ,['idproducttype'=>6]); // where

$dao->from("producttype")
    ->set("name=?",['s','Captain-Crunch']) //set
    ->set("type=?",['i',6]) //set
    ->where('idproducttype=?',['i',6]) // where
    ->update(); // update

or

$dao->from("producttype")
    ->set("name=?",'Captain-Crunch') //set
    ->set("type=?",6) //set
    ->where('idproducttype=?',['i',6]) // where
    ->update(); // update

> Generates the query: update producttype set name=?,type=? where idproducttype=? ....

delete([$table],[$schemaWhere],[$valuesWhere])

Generates a delete command.

$dao->delete("producttype"
    ,['idproducttype','i'] // where
    ,[7]); // where
$dao->delete("producttype"
    ,['idproducttype'=>7]); // where

> Generates the query: delete from producttype where idproducttype=? ....

You could also delete via a DQL builder chain.

$dao->from("producttype")
    ->where('idproducttype=?',['i',7]) // where
    ->delete(); 
$dao->from("producttype")
    ->where(['idproducttype'=>7]) // where
    ->delete(); 

> Generates the query: delete from producttype where idproducttype=? ....

Cache

It is possible to optionally cache the result of the queries. The duration of the query is also defined in the query. If the result of the query is not cached, then it is calculated normally (executing the query in the database. For identify a query as unique, the system generates an unique id (uid) based in sha256 created with the query, parameters, methods and the type of operation.

The library does not do any cache operation directly, instead it allows to cache the results using an external library.

  • Cache works with the next methods. * toList() * toListSimple() * first() * firstScalar() * last()

How it works

(1) We need to define a class that implements \eftec\IPdoOneCache

class CacheService implements \eftec\IPdoOneCache {
    public $cacheData=[];
    public $cacheCounter=0; // for debug
    public  function getCache($uid,$family='') {
        if(isset($this->cacheData[$uid])) {
            $this->cacheCounter++;
            echo "using cache\n";
            return $this->cacheData[$uid];
        }
        return false;
    }
    public function setCache($uid,$family='',$data=null,$ttl=null) {
        
        $this->cacheData[$uid]=$data;
    }
    public function invalidateCache($uid = '', $family = '') {
        unset($this->cacheData[$uid]);
    }
}
$cache=new CacheService();

(2) Sets the cache service

    $pdoOne=new PdoOne("mysql","127.0.0.1","travis","","travisdb");
    $cache=new CacheService();
    $$pdoOne->setCacheService($cache);

(3) Use the cache as as follow, we must add the method useCache() in any part of the query.

    $pdoOne->select('select * from table')
        ->useCache()->toList(); // cache that never expires
    $pdoOne->select('select * from table')
        ->useCache(1000)->toList(); // cache that lasts 1000ms.

Example using apcu

class CacheService implements \eftec\IPdoOneCache {
    public  function getCache($uid,$family='') {
        return apcu_fetch($uid);
    }
    public function setCache($uid,$family='',$data=null,$ttl=null) {
        apcu_store($uid,$data,$ttl);
    }
    public function invalidateCache($uid = '', $family = '') {
        // invalidate cache
        apcu_delete($uid);
    }
}
$cache=new CacheService();

Sequence

Sequence is an alternative to AUTO_NUMERIC field. It uses a table to generate an unique ID. The sequence used is based on Twitter's Snowflake and it is generated based on time (with microseconds), Node Id and a sequence. This generates a LONG (int 64) value that it's unique

Creating a sequence

  • $dao->nodeId set the node value (default is 1). If we want unique values amongst different clusters, then we could set the value of the node as unique. The limit is up to 1024 nodes.
  • $dao->tableSequence it sets the table (and function), the default value is snowflake.
$dao->nodeId=1; // optional
$dao->tableSequence='snowflake'; // optional
$dao->createSequence(); // it creates a table called snowflake and a function called next_snowflake()

Using the sequence

  • $dao->getSequence([unpredictable=false]) returns the last sequence. If the sequence fails to generate, then it returns -1. The function could fails if the function is called more than 4096 times every 1/1000th second.
$dao->getSequence() // string(19) "3639032938181434317" 

$dao->getSequence(true) // returns a sequence by flipping some values.

Creating a sequence without a table.

  • $dao->getSequencePHP([unpredictable=false]) Returns a sequence without using a table. This sequence is more efficient than $dao->getSequence but it uses a random value to deals with collisions.
  • If upredictable is true then it returns an unpredictable number (it flips some digits)
$dao->getSequencePHP() // string(19) "3639032938181434317" 

$dao->getSequencePHP(true) // string(19) "1739032938181434311" 

Benchmark (mysql, estimated)

| Library | Insert | findPk | hydrate | with | time | |-------------------------|--------|--------|---------|------|--------| | PDO | 671 | 60 | 278 | 887 | 3,74 | | PdoOne | 774 | 63 | 292 | 903 | 4,73 | | LessQL | 1413 | 133 | 539 | 825 | 5,984 | | YiiM | 2260 | 127 | 446 | 1516 | 8,415 | | YiiMWithCache | 1925 | 122 | 421 | 1547 | 7,854 | | Yii2M | 4344 | 208 | 632 | 1165 | 11,968 | | Yii2MArrayHydrate | 4114 | 213 | 531 | 1073 | 11,22 | | Yii2MScalarHydrate | 4150 | 198 | 421 | 516 | 9,537 | | Propel20 | 2507 | 123 | 1373 | 1960 | 11,781 | | Propel20WithCache | 1519 | 68 | 1045 | 1454 | 8,228 | | Propel20FormatOnDemand | 1501 | 72 | 994 | 1423 | 8,228 | | DoctrineM | 2119 | 250 | 1592 | 1258 | 18,139 | | DoctrineMWithCache | 2084 | 243 | 1634 | 1155 | 17,952 | | DoctrineMArrayHydrate | 2137 | 240 | 1230 | 877 | 16,83 | | DoctrineMScalarHydrate | 2084 | 392 | 1542 | 939 | 18,887 | | DoctrineMWithoutProxies | 2119 | 252 | 1432 | 1960 | 19,822 | | Eloquent | 3691 | 228 | 708 | 1413 | 12,155 |

PdoOne adds a bit of ovehead over PDO, however it is simple a wrapper to pdo.

Changelist

  • 1.24 2020-03-26 * builderReset() is now public
  • 1.23.1 2020-03-10 * Fixed a problem with the cache
  • 1.23 2020-03-10 * method toMeta()
  • 1.22 2020-02-08 * method invalidateCache() * changed the interface IPdoOneCache
  • 1.21 2020-02-07 * method setCacheService() and getCacheService() * method useCache()
  • 1.20 2020-jan-25 * Many cleanups. * update() and delete() now allows to set the query. * new method addDelimiter() to add delimiters to the query (i.e. 'table' for mysql and [table] for sql server)
  • 1.19 2020-jan-15 * getSequence() now has a new argument (name of the sequence, optional) * createSequence() has a new argument (type of sequence) and it allows to create a sequential sequence. * objectexist() now is public and it allows to works with functions * Bug fixed: objectExist() now works correctly (used by tableExist()) * new DDL methods drop(), dropTable() and truncate()
  • 1.16 2020-jan-14 * new method toListKeyValue()
  • 1.15 2019-dec-29 * Fix small bug if the argument of isAssoc() is not an array.
  • 1.14 2019-dec-26 * method where() works with associative array
  • 1.13 2019-dec-26 * new method count() * new method sum() * new method min() * new method max() * new method avg() * method select now allows null definition. * obtainSqlFields() discontinued
  • 1.12 2019-oct-20 Added argument (optional) ->toList($pdomodel) Added method ->toListSimple()
  • 1.11 2019-oct-01 1.11 It is still compatible with php 5.6.Added to composer.json
  • 1.10 2019-oct-01 1.10 Added method dateConvert(). Added trace to the throw.
  • 1.9 2019-aug-10 1.8 republished
  • 1.8 2019-aug-10 Added a date format. Methods dateSql2Text() and dateText2Sql()
  • 1.7 2019-jun-23 Added some benchmark. It also solves a problem with the tags. Now: table.field=? is converted to `table`.`field`=?
  • 1.6 2019-jun-22 affected_rows() returns a correct value.
  • 1.5 2019-may-31 some cleanups. columnTable() returns if the column is nullable or not.
  • 1.4 2019-may-30 insertobject()
  • 1.3 2019-may-23 New changes
  • 1.2 2019-may-22 New fixed.
  • 1.1 2019-may-21 Some maintenance
  • 1.0 2019-may-21 First version

  Files folder image Files  
File Role Description
Files folder imageexamples (22 files, 3 directories)
Files folder imagelib (3 files)
Files folder imagetests (2 files)
Accessible without login Plain text file .travis.yml Data Auxiliary data
Accessible without login Plain text file autoload.php Aux. Auxiliary script
Accessible without login Plain text file composer.json Data Auxiliary data
Accessible without login Plain text file LICENSE Lic. License text
Accessible without login Plain text file phpunit.xml Data Auxiliary data
Accessible without login Plain text file README.md Doc. Documentation

  Files folder image Files  /  examples  
File Role Description
Files folder imagebenchmark (6 files)
Files folder imagemysql (1 file)
Files folder imagesqlsrv (3 files)
  Plain text file Collection.php Class Class source
  Plain text file dBug.php Class Class source
  Accessible without login Plain text file logdaoone.txt Doc. Documentation
  Accessible without login Plain text file medium_code.php Example Example script
  Accessible without login Plain text file medium_code_v2.php Example Example script
  Plain text file MessageItem.php Class Class source
  Plain text file MessageList.php Class Class source
  Accessible without login Plain text file testautomap.php Example Example script
  Accessible without login Plain text file testbuilder.php Example Example script
  Accessible without login Plain text file testcatch.php Example Example script
  Accessible without login Plain text file testdberror.php Example Example script
  Accessible without login Plain text file testdberrorMessage.php Example Example script
  Accessible without login Plain text file testdbwithdate.php Example Example script
  Accessible without login Plain text file testgenerator.php Example Example script
  Accessible without login Plain text file testinsert.php Example Example script
  Accessible without login Plain text file testselect.php Example Example script
  Plain text file testselectcache.php Class Class source
  Accessible without login Plain text file testselectsimple.php Example Example script
  Accessible without login Plain text file testsequence.php Example Example script
  Accessible without login Plain text file testsequence2.php Example Example script
  Accessible without login Plain text file testtable.php Example Example script
  Accessible without login Plain text file tmp.php Example Example script

  Files folder image Files  /  examples  /  benchmark  
File Role Description
  Plain text file AbstractTestSuite.php Class Class source
  Accessible without login Plain text file PdoOneTestRunner.php Example Example script
  Plain text file PdoOneTestSuite.php Class Class source
  Accessible without login Plain text file PdoTestRunner.php Example Example script
  Plain text file PDOTestSuite.php Class Class source
  Plain text file sfTimer.php Class Class source

  Files folder image Files  /  examples  /  mysql  
File Role Description
  Accessible without login Plain text file testselect.php Example Example script

  Files folder image Files  /  examples  /  sqlsrv  
File Role Description
  Accessible without login Plain text file testdb.php Example Example script
  Accessible without login Plain text file testinsert.php Example Example script
  Accessible without login Plain text file testselect.php Example Example script

  Files folder image Files  /  lib  
File Role Description
  Plain text file IPdoOneCache.php Class Class source
  Plain text file PdoOne.php Class Class source
  Plain text file PdoOneEncryption.php Class Class source

  Files folder image Files  /  tests  
File Role Description
  Accessible without login Plain text file bootstrap.php Aux. Auxiliary script
  Plain text file PdoOneTest.php Class Class source

 Version Control Unique User Downloads Download Rankings  
 100%
Total:287
This week:0
All time:7,523
This week:150Up