1. Go to this page and download the library: Download eftec/pdoone library. Choose the download type require.
2. Extract the ZIP file and open the index.php.
3. Add this code to the index.php.
<?php
require_once('vendor/autoload.php');
/* Start to develop here. Best regards https://php-download.com/ */
eftec / pdoone example snippets
$stmt = $pdo->prepare("SELECT * FROM myTable WHERE name = ?");
$stmt->bindParam(1,$_POST['name'],PDO::PARAM_STR);
$stmt->execute();
$result = $stmt->get_result();
$products=[];
while($row = $result->fetch_assoc()) {
$product[]=$row;
}
$stmt->close();
ProductRepo // this class was generated with echo $pdoOne()->generateCodeClass(['Product']); or using the cli.
::where("name = ?",[$_POST['name']])
::toList();
use eftec\PdoOne;
// mysql
$dao=new PdoOne("mysql","127.0.0.1","root","abc.123","sakila","");
$conn->logLevel=3; // it is for debug purpose and it works to find problems.
$dao->connect();
// sql server 10.0.0.1\instance or (local)\instance or machinename\instance or machine (default instance)
$dao=new PdoOne("sqlsrv","(local)\sqlexpress","sa","abc.123","sakila","");
$conn->logLevel=3; // it is for debug purpose and it works to find problems.
$dao->connect();
// test (mockup)
$dao=new PdoOne("test","anyy","any","any","any","");
$dao->connect();
// oci (oracle) ez-connect. Remember that you must have installed Oracle Instant client and added to the path.
$cs='(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = instancia1)))';
$dao=new PdoOne("oci",$cs,"sa","abc.123"); // oracle uses the user as the schema
$conn->logLevel=3; // it is for debug purpose and it works to find problems.
$dao->connect();
// oci (oracle) tsnnames (the environment variables TNS_ADMIN and PATH must be correctly configured), also tnsnames.ora must exists.
$cs='instancia1';
$dao=new PdoOne("oci",$cs,"sa","abc.123"); // oracle uses the user as the schema
$conn->logLevel=3; // it is for debug purpose and it works to find problems.
$dao->connect();
$sql='select * from table where id=1';
$pdoStatement=$pdoOne->runRawQuery($sql,[],false); // [] are the parameters
$sql='select * from table where id=1';
$values=$pdoOne->runRawQuery($sql); // [] are the parameters
$values=$con->runRawQuery('select * from table where id=?',[20]); // with parameter
$values=$con->runRawQuery('select * from table where id=:name',['name'=>20]); // with named parameter
$values=$con->runRawQuery('select * from table',[]); // without parameter.
$name="O'hara";
$values=$con->runRawQuery("select * from table where name=:name",['name'=> $name]); // it works.✅
$values=$con->runRawQuery("select * from table where name=?",[$name]); // it works ok.✅
$values=$con->runRawQuery("select * from table where name='$name'"); // it will crash.❌
$sql="insert into `product`(name) values(?)";
$stmt=$pdoOne->prepare($sql);
$productName="Cocacola";
$stmt->bind_param("s",$productName); // s stand for a string. Also i =integer, d = double and b=blob
$rows=$pdoOne->runQuery($stmt);
$allRows=$rows->fetch_all(PDO::FETCH_ASSOC);
try {
$sql="insert into `product`(name) values(?)";
$pdoOne->startTransaction();
$result=$pdoOne->runRawQuery($sql,['Fanta'=>$productName],false);
$pdoOne->commit(); // transaction ok
} catch (Exception $e) {
$pdoOne->rollback(false); // error, transaction cancelled, the false means that it doesn't throw an exception if we want rollback.
}
$stats=$pdoOne->statValue('actor','actor_id');
$result=$pdoOne->columnTable('actor');
$pdo->createTable('film',
[
"film_id" => "smallint unsigned not null auto_increment",
"title" => "varchar(255) not null",
"description" => "text",
"release_year" => "year",
"language_id" => "tinyint unsigned not null",
"original_language_id" => "tinyint unsigned",
"rental_duration" => "tinyint unsigned not null default '3'",
"rental_rate" => "decimal(4,2) not null default '4.99'",
"length" => "smallint unsigned",
"replacement_cost" => "decimal(5,2) not null default '19.99'",
"rating" => "enum('G','PG','PG-13','R','NC-17') default 'G'",
"special_features" => "set('Trailers','Commentaries','Deleted Scenes','Behind the Scenes')",
"last_update" => "timestamp not null default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP"
],[
"film_id" => "PRIMARY KEY",
"title" => "KEY",
"language_id" => "FOREIGN KEY REFERENCES`language`(`language_id`) ON UPDATE CASCADE",
"original_language_id" => "FOREIGN KEY REFERENCES`language`(`language_id`) ON UPDATE CASCADE"
]);
$pdo->createTable('film',
[
"film_id" => "smallint unsigned not null auto_increment",
"title" => "varchar(255) not null",
"description" => "text",
"release_year" => "year",
"language_id" => "tinyint unsigned not null",
"original_language_id" => "tinyint unsigned",
"rental_duration" => "tinyint unsigned not null default '3'",
"rental_rate" => "decimal(4,2) not null default '4.99'",
"length" => "smallint unsigned",
"replacement_cost" => "decimal(5,2) not null default '19.99'",
"rating" => "enum('G','PG','PG-13','R','NC-17') default 'G'",
"special_features" => "set('Trailers','Commentaries','Deleted Scenes','Behind the Scenes')",
"last_update" => "timestamp not null default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP"
],'film_id');
$dao = new PdoOne('sqlsrv', "(local)\sqlexpress", "sa", "abc.123", "sakila");
$dao->open();
echo "<pre>";
var_dump($dao->tableSorted(3, false, true)); // it returns the tables sortered
var_dump($dao->tableSorted(3, true, true)); // it returns all the tables that can't be sortered
echo "</pre>";
$results = $pdoOne->select("*")->from('table')->where("p1=1 and p2>2.5 or p3 like '%aa%'");
$aa='aa';
$results = $pdoOne->select("*")->from('table')->where("p1=? and p2>? or p3 like ?",[1
,2.5
,"%$aa%"]);
// (if there is only a single argument without a type)
$results = $pdoOne->select("*")->from('table')->where("p1=?",[1]); // = where("p1=?",[1]);
// (if we don't define to where to put the value)
$results = $pdoOne->select("*")->from('table')->where("p1",[1]); // = where("p1=?",[1]);
// select * from table where p1='1' and p2='2.5' and p3='aa'
$results = $pdoOne->select("*")->from('table')->where(['p1'=>1
,'p2'=>2.5
,'p3'=>'aa']);
// select * from table where p1=1 and p2='2.5' and p3='aa'
$results = $pdoOne->select("*")->from('table')->where(['p1'=>[1]
,'p2'=>[2.5]
,'p3'=>['aa']]);
$results = $pdoOne->select("*")->from("table")
->where('condition=:p1 and condition2=:p2',['p1'=>'Coca-Cola','p2'=>1])
->toList();
$pdoOne->delete("producttype"
,['idproducttype'] // where
,[7]); // where
$pdoOne->delete("producttype"
,['idproducttype'=>7]); // where
$pdoOne->from("producttype")
->where('idproducttype=?',[7]) // where
->delete();
$pdoOne->from("producttype")
->where(['idproducttype'=>7]) // where
->delete();
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();
$pdoOne->select('select * from table')
->useCache()->toList(); // cache that never expires
$pdoOne->select('select * from table')
->useCache(1000)->toList(); // cache that lasts 1000ms.
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();
$dao->nodeId=1; // optional
$dao->tableSequence='snowflake'; // optional
$dao->createSequence(); // it creates a table (and it could create a store procedure) called snowflake and a function called next_snowflake(). You could create it only once.
$pdo->getSequence(true) // returns a sequence by flipping some values.
$pdo->getSequencePHP(true) // string(19) "1739032938181434311"
$this->setEncryption(12345678, '', 'INTEGER'); // the type of encryption is integer and it only works with integers. It doesn't use a salt value
$this->setEncryption('password', 'some-salt', 'AES-256-CTR'); // the password, the salt and the type of encryption (aes-256-ctr), you can use other methods
$this->setEncryption('passwrd', '', 'SIMPLE'); // the type of encryption is simple and it only works with primitive values. It doesn't use a salt.
$this->setEncryption('12345', 'salt-1234'); // it will use AES-256-CTR, the password and the salt must be secret.
// create user
$this->set(['username' => 1, 'password' => $this->encrypt($password)])
->from('user')
->insert();
// validate user
$user=$this->select(['username','password'])
->from('user')
->where(['username','password'],[1,$this->encrypt($password)])
->first();
// $user= if false or null then the user does not exist or the password is incorrect.
$pdoOne->logLevel=3; // the highest for debug.
$pdoOne->throwOnError=false; // it could be used in production.
var_dump($pdoOne->lastQuery); // it shows the last query
var_dump($pdoOne->lastParam); // and it shows the last parameters.
$pdoOne->logFile=true;
/**
* Generated by PdoOne Version 1.28
* Class ActorRepo
*/
class ActorRepo
{
const TABLE = 'Actor';
const PK = 'actor_id';
/** @var PdoOne */
public static $pdoOne = null;
/**
* It creates a new table<br>
* If the table exists then the operation is ignored (and it returns false)
*
* @param array $definition
* @param null $extra
*
* @return array|bool|PDOStatement
* @throws Exception
*/
public static function createTable($definition, $extra = null) {
if (!self::getPdoOne()->tableExist(self::TABLE)) {
return self::getPdoOne()->createTable(self::TABLE, $definition, self::PK, $extra);
}
return false; // table already exist
}
// .....
}
// 1) option 1, inject an instance of $pdo
ActorRepo::setPdoOne($pdoOne); // it inject the current connect to the database
// 2) option 2.
// If the global variable $pdoOne exists, then it is injected. (unless it is defined by using setPdoOne()
$pdoOne=new PdoOne("mysql","127.0.0.1","root","abc.123","sakila","");
$pdoOne->connect();
// 3) option 3
// If the global function pdoOne() exists, then it is used for obtain the instance.
function pdoOne() {
global $pdo;
if ($pdo===null) {
$pdo=new PdoOne('mysql','127.0.0.1','root','abc.123','sakila');
}
return $pdo;
}
$actorActorRepo::get(2); // it will read the actor with the pk=2 and it will return as an array.
$actors=$actorArray=ActorRepo::select(); // it returns all the rows.
use eftec\PdoOne;
use mapache_commons\Collection;
y"); // we need any connection.
$dao->logLevel=3;
$dao->render();
TablaParentRepo::createTable();
TablaParentRepo::createForeignKeys();
TablaParentRepo::dropTable();
TablaParentRepo::truncate();
// We don't have a method to alter a table.
$ok=TablaParentRepo::validTable(); // it returns true if the table matches with the definition stored into the clas
// select *
// from table
// inner join table2 on t1=t2
// where col=:arg
// and col2=:arg2
// group by col
// having col3=:arg3
// order by col
// limit 20,30
$results=$pdo->select('*')
->from('table')
->innerjoin('table2 on t1=t2')
->where('col=:arg and col2:=arg2',[20,30])
// it also works with ->where('col=:arg',20)->where('col2'=>30)
// it also works with ->where('col=?',20)->where('col2=?'=>30)
->group('col')
->having('col3=:arg3',400)
->order('col')
->limit('20,30')
->toList(); // end of the chain
// where obj is an associative array or an object, where the keys are the name of the columns (case sensitive)
$identity=TablaParentRepo::insert($obj);
TablaParentRepo::update($obj);
TablaParentRepo::delete($obj);
TablaParentRepo::deleteById(id);
$obj=['IdUser'=>1,'Name'='John Doe'];
UserRepo::validateModel($obj,false,['_messages']); // returns true if $obj is a valid User.
$this->select('*')->from('table')->recursive(['table1','table1.table2']);
// some operations that involves recursive
if($this->hasRecursive('table1')) {
$this->innerJoin('table1 on table.c=table1.c');
}
if($this->hasRecursive('table1.table2')) {
$this->innerJoin('table1 on table1.c=table2.c');
}
$r=$this->toList(); // recursive is resetted.