PHPackages                             eftec/pdoone - PHPackages - PHPackages  [Skip to content](#main-content)[PHPackages](/)[Directory](/)[Categories](/categories)[Trending](/trending)[Leaderboard](/leaderboard)[Changelog](/changelog)[Analyze](/analyze)[Collections](/collections)[Log in](/login)[Sign up](/register)

1. [Directory](/)
2. /
3. [Database & ORM](/categories/database)
4. /
5. eftec/pdoone

ActiveLibrary[Database & ORM](/categories/database)

eftec/pdoone
============

Minimaist procedural PDO wrapper library

4.13.2(1y ago)1116.3k↓47.5%189MITPHPPHP >=7.4CI failing

Since May 21Pushed 1y ago5 watchersCompare

[ Source](https://github.com/EFTEC/PdoOne)[ Packagist](https://packagist.org/packages/eftec/pdoone)[ Docs](https://github.com/EFTEC/PdoOne)[ RSS](/packages/eftec-pdoone/feed)WikiDiscussions master Synced yesterday

READMEChangelog (10)Dependencies (3)Versions (170)Used By (9)

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

[](#database-access-object-wrapper-for-php-and-pdo-in-a-single-class)

PdoOne. It's a simple wrapper for PHP's PDO library compatible with SQL Server (2008 R2 or higher), MySQL (5.7 or higher) and Oracle (12.1 or higher).

This library tries to **work as fast as possible**. Most of the operations are simple string/array managements and work in the bare metal of the PDO library, but it also allows to create an ORM using the extension [eftec/PdoOneORM](https://github.com/EFTEC/PdoOneORM).

[![Packagist](https://camo.githubusercontent.com/ebdc2e290158a4d40ab98abd8e8f4c8eb1e91007ed63fb9a6b10bb5fb7a6b406/68747470733a2f2f696d672e736869656c64732e696f2f7061636b61676973742f762f65667465632f50646f4f6e652e737667)](https://packagist.org/packages/eftec/PdoOne)[![Total Downloads](https://camo.githubusercontent.com/ead0e4d38e2fcee9f07d5ff3223f17c8884f7e261a99174ffe8d1b3c443d8f82/68747470733a2f2f706f7365722e707567782e6f72672f65667465632f50646f4f6e652f646f776e6c6f616473)](https://packagist.org/packages/eftec/PdoOne)![Maintenance](https://camo.githubusercontent.com/0c8f829897840ac35cb3daf181a719612c0f64c0ed5fca3c7b90ed7591169162/68747470733a2f2f696d672e736869656c64732e696f2f6d61696e74656e616e63652f7965732f323032352e737667)![composer](https://camo.githubusercontent.com/08623182d7b037246f11c0ad4aec3fe405dcf9d668058398497abd1a9a770e9d/68747470733a2f2f696d672e736869656c64732e696f2f62616467652f636f6d706f7365722d253345312e362d626c75652e737667)![php](https://camo.githubusercontent.com/59558613d05bebac3748d4f75f0c94435dec5fb11d059b448c2d172e25d82120/68747470733a2f2f696d672e736869656c64732e696f2f62616467652f7068702d372e342d677265656e2e737667)![php](https://camo.githubusercontent.com/5cd91a78fb469ca20b235b6951fb6dd77bda78ac4633eb432e93699bcb141589/68747470733a2f2f696d672e736869656c64732e696f2f62616467652f7068702d382e342d677265656e2e737667)![CocoaPods](https://camo.githubusercontent.com/347353606ed8f26b45bcf9da083db0063fa1dadd1baef36a5f3bf9ce1d127548/68747470733a2f2f696d672e736869656c64732e696f2f62616467652f646f63732d37302532352d79656c6c6f772e737667)

Turn this

```
$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();
```

into this

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

or using the ORM (using [eftec/PdoOneORM](https://github.com/EFTEC/PdoOneORM) library)

```
ProductRepo // this class was generated with echo $pdoOne()->generateCodeClass(['Product']); or using the cli.
    ::where("name = ?",[$_POST['name']])
    ::toList();
```

Table of contents
=================

[](#table-of-contents)

- [Database Access Object wrapper for PHP and PDO in a single class](#database-access-object-wrapper-for-php-and-pdo-in-a-single-class)
- [Table of contents](#table-of-contents)
    - [Examples](#examples)
    - [Installation](#installation)
        - [Install (using composer)](#install-using-composer)
        - [Install (manually)](#install-manually)
    - [How to create a Connection?](#how-to-create-a-connection)
        - [OCI](#oci)
    - [How to run a SQL command?](#how-to-run-a-sql-command)
        - [1. Running a raw query](#1-running-a-raw-query)
        - [2. Running a native PDO statement](#2-running-a-native-pdo-statement)
        - [3. Running using the query builder](#3-running-using-the-query-builder)
        - [4. Running using an ORM](#4-running-using-an-orm)
        - [5. Run a query with a different mode](#5-run-a-query-with-a-different-mode)
    - [How to work with Date values?](#how-to-work-with-date-values)
    - [How to run a transaction?](#how-to-run-a-transaction)
    - [Custom Queries](#custom-queries)
        - [tableExist($tableName)](#tableexisttablename)
        - [statValue($tableName,$columnName)](#statvaluetablenamecolumnname)
        - [columnTable($tablename)](#columntabletablename)
        - [foreignKeyTable($tableName)](#foreignkeytabletablename)
        - [createTable($tableName,$definition,$primaryKey=null,$extra='',$extraOutside='')](#createtabletablenamedefinitionprimarykeynullextraextraoutside)
        - [tableSorted($maxLoop = 5, $returnProblems = false, $debugTrace = false)](#tablesortedmaxloop--5-returnproblems--false-debugtrace--false)
        - [validateDefTable($pdoInstance,$tablename,$defTable,$defTableKey)](#validatedeftablepdoinstancetablenamedeftabledeftablekey)
        - [foreignKeyTable](#foreignkeytable)
    - [Query Builder (DQL)](#query-builder-dql)
        - [select($columns)](#selectcolumns)
        - [count($sql,$arg='\*')](#countsqlarg)
        - [min($sql,$arg='\*')](#minsqlarg)
        - [max($sql,$arg='\*')](#maxsqlarg)
        - [sum($sql,$arg='\*')](#sumsqlarg)
        - [avg($sql,$arg='\*')](#avgsqlarg)
        - [distinct($distinct='distinct')](#distinctdistinctdistinct)
        - [from($tables)](#fromtables)
        - [where($where,\[$arrayParameters=array()\])](#wherewherearrayparametersarray)
            - [Where() without parameters.](#where-without-parameters)
            - [Where() with parameters defined by an indexed array.](#where-with-parameters-defined-by-an-indexed-array)
            - [Where() using an associative array](#where-using-an-associative-array)
            - [Where() using an associative array and named arguments](#where-using-an-associative-array-and-named-arguments)
            - [Examples of where()](#examples-of-where)
        - [order($order)](#orderorder)
        - [group($group)](#groupgroup)
        - [having($having,\[$arrayParameters\])](#havinghavingarrayparameters)
        - [End of the chain](#end-of-the-chain)
            - [runGen($returnArray=true)](#rungenreturnarraytrue)
            - [toList($pdoMode)](#tolistpdomode)
- [toPdoStatement($pdoMode)](#topdostatementpdomode)
- [fetchLoop($callable,$pdoMode)](#fetchloopcallablepdomode)\* [toMeta()](#tometa)\* [toListSimple()](#tolistsimple)\* [toListKeyValue()](#tolistkeyvalue)\* [toResult()](#toresult)\* [firstScalar($colName=null)](#firstscalarcolnamenull)\* [first()](#first)\* [last()](#last)\* [sqlGen()](#sqlgen)
    - [Query Builder (DML)](#query-builder-dml)
        - [insert($table,$schema,\[$values\])](#inserttableschemavalues)
        - [insertObject($table,\[$declarativeArray\],$excludeColumn=\[\])](#insertobjecttabledeclarativearrayexcludecolumn)
        - [update($$table,$schema,$values,\[$schemaWhere\],\[$valuesWhere\])](#updatetableschemavaluesschemawherevalueswhere)
        - [delete(\[$table\],\[$schemaWhere\],\[$valuesWhere\])](#deletetableschemawherevalueswhere)
    - [Cache](#cache)
        - [How to configure it?](#how-to-configure-it)
        - [Example using apcu](#example-using-apcu)
    - [Sequence](#sequence)
        - [Creating a sequence](#creating-a-sequence)
        - [Creating a sequence without a table.](#creating-a-sequence-without-a-table)
        - [Using the sequence](#using-the-sequence)
    - [Fields](#fields)
    - [Encryption](#encryption)
    - [How to debug and trace errors in the database?](#how-to-debug-and-trace-errors-in-the-database)
        - [Setting the log level](#setting-the-log-level)
        - [Throwing errors](#throwing-errors)
        - [Getting the last Query](#getting-the-last-query)
        - [Generating a log file](#generating-a-log-file)
    - [CLI](#cli)
        - [Run as cli](#run-as-cli)
        - [Run as CLI interative](#run-as-cli-interative)
            - [Examples](#examples-1)
        - [Run CLI to generate repository classes.](#run-cli-to-generate-repository-classes)
        - [cli-classcode](#cli-classcode)
        - [cli-selectcode](#cli-selectcode)
        - [cli-arraycode](#cli-arraycode)
        - [cli-json](#cli-json)
        - [cli-csv](#cli-csv)
        - [UI](#ui)
        - [How to run the UI?](#how-to-run-the-ui)
        - [DDL Database Design Language](#ddl--database-design-language)
        - [Nested Operators](#nested-operators)
        - [DQL Database Query Language](#dql-database-query-language)
        - [DML Database Model Language](#dml-database-model-language)
        - [Validate the model](#validate-the-model)
        - [Recursive](#recursive)
            - [recursive()](#recursive-1)
            - [getRecursive()](#getrecursive)
            - [hasRecursive()](#hasrecursive)
    - [Benchmark (mysql, estimated)](#benchmark-mysql-estimated)
    - [migration from 3 to 4](#migration-from-3-to-4)
    - [Error FAQs](#error-faqs)
        - [Uncaught Error: Undefined constant eftec\_BasePdoOneRepo::COMPILEDVERSION](#uncaught-error-undefined-constant-eftec_basepdoonerepocompiledversion)
    - [Changelist](#changelist)

Examples
--------

[](#examples)

[ExampleTicketPHP](https://github.com/jorgecc/ExampleTicketPHP)[Example cupcakes](https://github.com/EFTEC/example.cupcakes)[Example Search](https://github.com/EFTEC/example-search)[Example Different Method](https://github.com/escuelainformatica/example-pdoone)[![example php bladeone](https://camo.githubusercontent.com/3c938f71f46a90eb85bb104f0f396fcba62b8f4a/68747470733a2f2f74686570726163746963616c6465762e73332e616d617a6f6e6177732e636f6d2f692f3436696b7061376661717677726533797537706a2e6a7067)](https://camo.githubusercontent.com/3c938f71f46a90eb85bb104f0f396fcba62b8f4a/68747470733a2f2f74686570726163746963616c6465762e73332e616d617a6f6e6177732e636f6d2f692f3436696b7061376661717677726533797537706a2e6a7067)[![example php bladeone cupcakes](https://github.com/EFTEC/example.cupcakes/raw/master/docs/result.jpg)](https://github.com/EFTEC/example.cupcakes/raw/master/docs/result.jpg)[![example php bladeone search](https://github.com/EFTEC/example-search/raw/master/img/search_bootstrap.jpg)](https://github.com/EFTEC/example-search/raw/master/img/search_bootstrap.jpg)[![](https://github.com/escuelainformatica/example-pdoone/raw/master/docs/database.jpg)](https://github.com/escuelainformatica/example-pdoone/raw/master/docs/database.jpg)More examples:

[Example Mysql PHP and PDO using PDOOne](https://www.southprojects.com/Programming/mysql-php-pdo)

Installation
------------

[](#installation)

This library requires PHP 7.1 and higher, and it requires the extension PDO and the extension PDO-MYSQL (Mysql), PDO-SQLSRV (sql server) or PDO-OCI (Oracle)

### Install (using composer)

[](#install-using-composer)

Edit **composer.json** the next requirement, then update composer.

```
  {
      "require": {
        "eftec/PdoOne": "^4.0.1"
      }
  }
```

or install it via cli using

> composer require eftec/PdoOne

### Install (manually)

[](#install-manually)

Just download the folder lib from the library and put in your folder project. Then you must include all the files included on it.

How to create a Connection?
---------------------------

[](#how-to-create-a-connection)

Create an instance of the class PdoOne as follows. Then, you can open the connection using the method connect() or open()

```
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();
```

where

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

- "**mysql**" is the MySQL database. It also allows sqlsrv (for sql server) or "oci"
- **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

### OCI

[](#oci)

Oracle is tricky to install. In Windows, from the Oracle home's bin folder, you must copy all the dll to the PHP folder and Apache Folder.

How to run a SQL command?
-------------------------

[](#how-to-run-a-sql-command)

### 1. Running a raw query

[](#1-running-a-raw-query)

With the method **RunRawQuery()**, we could execute a command directly to PDO with or without parameters. And it could return a **PdoStatement** or an **array**. It is useful when we want speed.

> **RunRawQuery($rawSql,$param,$returnArray,$fetchMode,$fetchArgument)**
>
> string **$rawSql** The query to execute array|null **$param** \[type1,value1,type2,value2\] or \[name1=>value,name2=value2\] bool **$returnArray** if true (default) then it returns an array. If false then it returns a **PDOStatement**int **$fetchMode** Indicates the mode to fetch. Example: PDO::FETCH\_ASSOC null **$fetchArgument** The argument of the fetchMode.

```
$sql='select * from table where id=1';
$pdoStatement=$pdoOne->runRawQuery($sql,[],false);  // [] are the parameters
```

But we could change it to returns an array

```
$sql='select * from table where id=1';
$values=$pdoOne->runRawQuery($sql);  // [] are the parameters
```

We could also pass 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.
```

> Note, this library uses prepared statements, so it is free of SQL injection (if you use parameters)

```
$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.❌
```

### 2. Running a native PDO statement

[](#2-running-a-native-pdo-statement)

With the method **runQuery()** we could execute a prepared statement in PDO. It is useful when we want to pass arguments to it. **runQuery()** requires a PDO **PreparedStatement**.

> This method is not recommended unless you are already working with PDO statements, and you don't want to adapt all your code.

```
$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);
```

### 3. Running using the query builder

[](#3-running-using-the-query-builder)

You can use the query builder to build your command. You could check the chapter about [Query Builder (DQL)](#query-builder-dql) for more information.

```
// query builder
$pdoOne->set(['name'=>'cocacola'])
    ->from('product')
    ->insert();
```

### 4. Running using an ORM

[](#4-running-using-an-orm)

The library eftec\\PdoOneORM allows to create an **\[orm\]**(#orm) of your tables. If you are generated an ORM, then you can use the next code

```
ProductRepo::toList(['category'=>'drink']);
```

Where **ProductRepo** is a service class generated by using the ORM.

### 5. Run a query with a different mode

[](#5-run-a-query-with-a-different-mode)

By default, PdoOne executes the queries in the mode PDO::FETCH\_ASSOC You can change by running the queries as:

```
$pdo->setFechMode(PDO::FETCH_CLASS,'stdClass')->runRawQuery($query);
// or you can run as
$pdo->runRawQuery($query,null,true,false,null,PDO::FETCH_CLASS,'stdClass')
```

How to work with Date values?
-----------------------------

[](#how-to-work-with-date-values)

PdoOne allows 5 types of dates.

- **SQL Format** It is the format how the date is stored into the database. It depends on the type of the database. For example MySQL could use the format Y-m-d.
- **Human Format** It is the format how the end user looks our date.
- **ISO Date Format**. It is the format how the value could be transported and serialized.
- **Timestamp**: It counts the number of seconds after 1-1-1970
- **Class / PHP Class**: It is an **DateTime** object.

How to run a transaction?
-------------------------

[](#how-to-run-a-transaction)

There are 3 methods to runs a transaction:

MethodDescriptionstartTransaction()It starts a transaction. Depending on the type database, it could be stacked or not.commit()Commit (and closes) a transactionrollback()Rollback (and closes) a transactionExample:

```
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.
}
```

Custom Queries
--------------

[](#custom-queries)

### tableExist($tableName)

[](#tableexisttablename)

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

### statValue($tableName,$columnName)

[](#statvaluetablenamecolumnname)

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

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

minmaxavgsumcount1205103.000021115205### columnTable($tablename)

[](#columntabletablename)

Returns all columns of a table

```
$result=$pdoOne->columnTable('actor');
```

colnamecoltypecolsizecolprescolscaleiskeyisidentityactor\_idsmallint5011first\_namevarchar4500last\_namevarchar4500last\_updatetimestamp00### foreignKeyTable($tableName)

[](#foreignkeytabletablename)

Returns all foreign keys of a table (source table)

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

[](#createtabletablenamedefinitionprimarykeynullextraextraoutside)

Creates a table using a definition and primary key.

- **$definition** The definition is an associative array with the name of the column as key and the definition as value.
- **primaryKey** It could be a string or associative array.
    - if it is a string then it is the name of the primary key, example "user\_id";
    - if it is an associative array, then it could be used to define primary key, unique, key and foreign keys:
        - 'key\_name'=>'PRIMARY KEY'
        - 'key\_name'=>'KEY'
        - 'key\_name'=>'UNIQUE KEY'
        - 'key\_name'=>'FOREIGN KEY REFERENCES TABLEREF(COLREF) ...'
- **$extra** It defines an extra definition inside the definition of the table.
- **extraOutside** It defines an extra definition after the definition of the table.

> Note: You could generate a code to create a table using an existing table by executing cli (output classcode)
> php pdoone.php -database mysql -server 127.0.0.1 -user root -pwd abc.123 -db sakila -input film -output classcode

Example: (mysql)

```
$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');
```

Example (sqlsrv)

```
$pdo->createTable('film',
   [
       "film_id" => "int NOT NULL IDENTITY(1,1)",
       "title" => "varchar(255) NOT NULL",
       "description" => "text(2147483647) DEFAULT (NULL)",
       "release_year" => "varchar(4)",
       "language_id" => "tinyint NOT NULL",
       "original_language_id" => "tinyint DEFAULT (NULL)",
       "rental_duration" => "tinyint NOT NULL DEFAULT ((3))",
       "rental_rate" => "decimal(4,2) NOT NULL DEFAULT ((4.99))",
       "length" => "smallint DEFAULT (NULL)",
       "replacement_cost" => "decimal(5,2) NOT NULL DEFAULT ((19.99))",
       "rating" => "varchar(10) DEFAULT ('G')",
       "special_features" => "varchar(255) DEFAULT (NULL)",
       "last_update" => "datetime NOT NULL DEFAULT (getdate())"
   ],[
       "language_id" => "FOREIGN KEY REFERENCES language(language_id)",
       "original_language_id" => "FOREIGN KEY REFERENCES language(language_id)",
       "film_id" => "PRIMARY KEY"
   ]);
```

### tableSorted($maxLoop = 5, $returnProblems = false, $debugTrace = false)

[](#tablesortedmaxloop--5-returnproblems--false-debugtrace--false)

It returns a list of tables ordered by dependency (from no dependent to more dependent)

**Note**: This operation is not foolproof because the tables could have circular references.

```
$dao = new PdoOne('sqlsrv', "(local)\sqlexpress", "sa", "abc.123", "sakila");
$dao->open();
echo "";
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 "";
```

### validateDefTable($pdoInstance,$tablename,$defTable,$defTableKey)

[](#validatedeftablepdoinstancetablenamedeftabledeftablekey)

It validates a table if the table matches the definition asigned by values.

```
$def=[
       "film_id" => "int NOT NULL IDENTITY(1,1)",
       "title" => "varchar(255) NOT NULL",
       "description" => "text(2147483647) DEFAULT (NULL)",
       "release_year" => "varchar(4)",
       "language_id" => "tinyint NOT NULL",
       "original_language_id" => "tinyint DEFAULT (NULL)",
       "rental_duration" => "tinyint NOT NULL DEFAULT ((3))",
       "rental_rate" => "decimal(4,2) NOT NULL DEFAULT ((4.99))",
       "length" => "smallint DEFAULT (NULL)",
       "replacement_cost" => "decimal(5,2) NOT NULL DEFAULT ((19.99))",
       "rating" => "varchar(10) DEFAULT ('G')",
       "special_features" => "varchar(255) DEFAULT (NULL)",
       "last_update" => "datetime NOT NULL DEFAULT (getdate())"
   ];
$keys=[
       "language_id" => "FOREIGN KEY REFERENCES language(language_id)",
       "original_language_id" => "FOREIGN KEY REFERENCES language(language_id)",
       "film_id" => "PRIMARY KEY"
   ];

var_dump(PdoOne::validateDefTable(self::getPdoOne(),self::TABLE,$def,$keys));

```

### foreignKeyTable

[](#foreignkeytable)

It returns all the foreign keys of a table.

```
$result=$pdoOne->foreignKeyTable('actor');
```

collocaltableremcolremcustomer\_idcustomercustomer\_idrental\_idrentalrental\_idstaff\_idstaffstaff\_idQuery Builder (DQL)
-------------------

[](#query-builder-dql)

You could also build a procedural query.

Example:

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

### select($columns)

[](#selectcolumns)

Indicates the columns to return. The argument is a SQL command, so it allows any operation that the database support, including functions, constants, operators, alias and such.

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

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

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

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

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

[](#countsqlarg)

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

```
$result = $pdoOne->count('from table where condition=1'); // select count(*) from table where c..
$result = $pdoOne->count()->from('table')->where('condition=?',[1]); // select count(*) from table where c..
$result = $pdoOne->count('from table','col1'); // select count(col1) from table
$result = $pdoOne->count()->from('table'); // select count(*) from table
```

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

[](#minsqlarg)

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 = $pdoOne->min('from table where condition=1','col'); // select min(col) from table where c..
$result = $pdoOne->min('from table','col1'); // select min(col1) from table
$result = $pdoOne->min('','col1')->from('table'); // select min(col1) from table
$result = $pdoOne->min('col1')->from('table'); // select min(col1) from table
```

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

[](#maxsqlarg)

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 = $pdoOne->max('from table where condition=1','col'); // select max(col) from table where c..
$result = $pdoOne->max('from table','col1'); // select max(col1) from table
```

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

[](#sumsqlarg)

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 = $pdoOne->sum('from table where condition=1','col'); // select sum(col) from table where c..
$result = $pdoOne->sum('from table','col1'); // select sum(col1) from table
```

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

[](#avgsqlarg)

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 = $pdoOne->avg('from table where condition=1','col'); // select avg(col) from table where c..
$result = $pdoOne->avg('from table','col1'); // select avg(col1) from table
```

### distinct($distinct='distinct')

[](#distinctdistinctdistinct)

Generates a select command.

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

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

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

### from($tables)

[](#fromtables)

Generates a "from" sql command.

```
$results = $pdoOne->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 = $pdoOne->select("*")->from('table t1 inner join t2 on t1.c1=t2.c2'); //...
```

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

[](#wherewherearrayparametersarray)

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 = $pdoOne->select("*")
->from('table')
->where('p1=1'); //...
```

The where could be expressed in different ways.

#### Where() without parameters.

[](#where-without-parameters)

It is possible to write the where without parameters as follows:

```
$results = $pdoOne->select("*")->from('table')->where("p1=1 and p2>2.5 or p3 like '%aa%'");
```

#### Where() with parameters defined by an indexed array.

[](#where-with-parameters-defined-by-an-indexed-array)

```
$aa='aa';
$results = $pdoOne->select("*")->from('table')->where("p1=? and p2>? or p3 like ?",[1
                                                                                    ,2.5
                                                                                    ,"%$aa%"]);
```

It also works

```
// (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]);
```

#### Where() using an associative array

[](#where-using-an-associative-array)

It is a shorthand definition of a query using an associative array, where the key is the name of the column and the value is the value to compare

It only works with **equality** (=) and the logic operator **'and'** (the type is defined automatically)

```
// 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']);
```

Also, it is possible to specify the type of parameter.

```
// 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']]);
```

#### Where() using an associative array and named arguments

[](#where-using-an-associative-array-and-named-arguments)

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

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

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

#### Examples of where()

[](#examples-of-where)

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

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

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

> Generates the query: select \* from table **where p1=?(1)**

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

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

> Note. where could be nested.

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

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

You could also use:

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

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

### order($order)

[](#orderorder)

Generates an order command.

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

> Generates the query: select \* from table **order by p1 desc**

### group($group)

[](#groupgroup)

Generates a group command.

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

> Generates the query: select \* from table **group by p1**

### having($having,\[$arrayParameters\])

[](#havinghavingarrayparameters)

Generates a having command.

> Note: it uses the same parameters as **where()**

```
$results = $pdoOne->select("*")
->from('table')
->group('p1')
->having('p1>?',array(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')

### End of the chain

[](#end-of-the-chain)

#### runGen($returnArray=true)

[](#rungenreturnarraytrue)

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)

[](#tolistpdomode)

It's a macro of **runGen()**. It returns an associative array or false if the operation fails.

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

toPdoStatement($pdoMode)
========================

[](#topdostatementpdomode)

It returns a PdoStatement from the current query

> Note: if you want to loop the statement, then you can use fetchLoop()

**Example**:

```
$stmt = $pdoOne->select("*")
  ->from('table')
  ->toPdoStatement();
while ($row = $stmt->fetch()) {
  // do something
}
```

fetchLoop($callable,$pdoMode)
=============================

[](#fetchloopcallablepdomode)

It fetches a query for every row.
This method could be used when we don't want to read all the information at once, so you can read and process each line separately
**Example**:

```
$this->select('select id,name from table')
      ->fetchLoop(static function($row) {return($row);},\PDO::FETCH_ASSOC)
```

#### toMeta()

[](#tometa)

It returns a **metacode** (definitions) of each column of a query.

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

or

```
$results = $pdoOne->toMeta('select * from table');
```

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()

[](#tolistsimple)

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

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

#### toListKeyValue()

[](#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 = $pdoOne->select("cod,name")
->from('table')
->toListKeyValue(); // ['cod1'=>'name1','cod2'=>'name2']
```

#### toResult()

[](#toresult)

It's a macro of runGen. It returns a mysqli\_result or null.

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

#### firstScalar($colName=null)

[](#firstscalarcolnamenull)

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

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

#### first()

[](#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 = $pdoOne->select("*")
->from('table')
->first();
```

#### last()

[](#last)

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

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

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

#### sqlGen()

[](#sqlgen)

It returns the sql command and string.

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

> Note: it doesn't reset the query.

Query Builder (DML)
-------------------

[](#query-builder-dml)

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.

```
$pdoOne->insert("table"
    ,['col1']
    ,[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.

```
$pdoOne->insert("table"
    ,['col1',20]);
```

**Schema and values using two associative arrays**:

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

**Schema and values using a single associative array**: The type is calculated automatically.

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

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

[](#inserttableschemavalues)

Generates an insert command.

```
$pdoOne->insert("producttype"
    ,['idproducttype','name','type']
    ,[1,'cocacola',1]);
```

Using nested chain (single array)

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

Using nested chain multiple set

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

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

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

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

[](#insertobjecttabledeclarativearrayexcludecolumn)

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

Using nested chain declarative set

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

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

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

[](#updatetableschemavaluesschemawherevalueswhere)

Generates an insert command.

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

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

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

or

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

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

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

[](#deletetableschemawherevalueswhere)

Generates a delete command.

```
$pdoOne->delete("producttype"
    ,['idproducttype'] // where
    ,[7]); // where
```

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

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

You could also delete via a DQL builder chain.

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

```
$pdoOne->from("producttype")
    ->where(['idproducttype'=>7]) // where
    ->delete();
```

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

Cache
-----

[](#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 a 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 to configure it?

[](#how-to-configure-it)

1. We need to define a class that implements the interface **\\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 follows, 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

[](#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)

Sequence is an alternative to AUTO\_NUMERIC (identity) field. It has two methods to create a sequence: **snowflake** and **sequence**. It is an alternative to create a GUID mainly because it returns a number (a GUID usually is a string that it is more expensive to index and to store)

The goal of the sequence is to create a unique number that it is never repeated.

- **sequence**: It uses the functionality of the database to create and use a sequence. MySql doesn't have sequences but they are emulated. The main problem of the sequence is it returns a consecutive number, example: 1,2,3,4... This number is predictable. For example, if you are the user number **20**, then you can guess another user = **19, 21**, etc.
- **snowflakes**: It uses a table to generate a unique ID. The sequence used is based on Twitter's Snowflake, and it is generated based on time (with microseconds), **nodeId** and a unique sequence. This generates a LONG (int 64) value that it's unique. Example: **10765432100123456789**. This number is partially predictable .

### Creating a sequence

[](#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 (and it could create a store procedure) called snowflake and a function called next_snowflake(). You could create it only once.
```

### Creating a sequence without a table.

[](#creating-a-sequence-without-a-table)

It is possible to create a new sequence without any table. It is fast, but it could have problems of collisions.

> It ensures a collision free number only if we don't do more **than one operation per 0.0001 second** However, it also adds a pseudo random number (0-4095 based in time) so the chances of collision is **1/4095** (per two operations done every 0.0001 second). It is based on Twitter's Snowflake number. i.e.. **you are safe of collisions if you are doing less than 1 million of operations per second** (technically: 45 millions).

- **$pdo->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)

```
$pdo->getSequencePHP() // string(19) "3639032938181434317"
```

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

### Using the sequence

[](#using-the-sequence)

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

```
$pdo->getSequence() // string(19) "3639032938181434317"
$pdo->getSequencePHP() // string(19) "3639032938181434317"
```

```
$pdo->getSequence(true) // returns a sequence by flipping some values.
$pdo->getSequencePHP(true) // string(19) "1739032938181434311"
```

Fields
------

[](#fields)

FieldDescriptionExample$prefixBaseIf we need to add a prefix to every table$this->prefixBase='example\_';$internalCacheCounterThe counter of hits of the internal cache.$this->internalCacheCounter=;$nodeIdUsed by sequence (snowflake). nodeId It is the identifier of the node. It must be between 0..1023$this->nodeId=3;$tableSequenceThe name of the table sequence (snowflake)$this->tableSequence="tableseq1";$masks0If we want to generate an unpredictable number (used by sequence)$this->masks0=\[0,1,2,3,4\];$masks1If we want to generate an unpredictable number (used by sequence)$this->masks1=\[4,3,2,1,0\];$databaseTypeThe current type of database. It is set via el constructorecho $this->databaseType;$serverThe current server machineecho $this->server;$userThe current userecho $this->user;$pwdThe current passwordecho $this->pwd;$dbThe current database or schema (oracle ignores this value)echo $this->db;$charsetTo set the default charset. It must be set via constructorecho $this->charset;$isOpenIt is true if the database is connected otherwise,it's falseif($this->isOpen) { …};$throwOnErrorIf true (default), then it throws an error if happens an error. If false, then the execution continues$this->throwOnError=false;$conn1The instance of PDO. You can set it or use it directly.$this->conn1->pdoStatement(..);$transactionOpenTrue if the transaction is openif($this->transactionOpen) { …};$readonlyif the database is in READ ONLY mode or not. If true then we must avoid to write in the database$this->readonly=true;$logFilefull filename of the log file. If it's empty then it doesn't store a log file. The log file is limited to 1mb$this->logFile="/folder/file.log";$errorTextIt stores the last error. runGet and beginTry resets itecho $this->errorText;$isThrowtodo$this->isThrow=;$logLevelIt indicates the current level of log. 0 = no log (for production), 3= full log$this->logLevel=3;$lastQueryLast query executedecho $this->lastQuery;$lastParamThe last parameters. It is an associative arrayecho $this->lastParam;Encryption
----------

[](#encryption)

This library permits encryption/decryption of the information.

To set the encryption you could use the next command:

```
$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.
```

Then you can encrypt and decrypt a value using

```
$encrypted=$this->encrypt($original); // encrypt $original
$original=$this->decrypt($encrypted); // decrypt $encrypted
```

Example:

```
$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.
```

How to debug and trace errors in the database?
----------------------------------------------

[](#how-to-debug-and-trace-errors-in-the-database)

### Setting the log level

[](#setting-the-log-level)

You can set the log level to 3. The log level works when the operation fails, the higher the log level, then it shows most information.

```
$pdoOne->logLevel=3; // the highest for debug.
```

- 0=no debug for production (all message of error are generic)
- 1=it shows an error message
- 2=it shows the error messages and the last query
- 3=it shows the error message, the last query and the last parameters (if any). It could be unsafe (it could show passwords)

### Throwing errors

[](#throwing-errors)

By default, PdoOne throws PHP errors, but we could avoid it by setting the field $throwOnError to false.

```
$pdoOne->throwOnError=false; // it could be used in production.
```

### Getting the last Query

[](#getting-the-last-query)

```
var_dump($pdoOne->lastQuery); // it shows the last query
var_dump($pdoOne->lastParam); // and it shows the last parameters.
```

### Generating a log file

[](#generating-a-log-file)

If empty then it will not generate a log file (using the php log file)

```
$pdoOne->logFile=true;
```

CLI
---

[](#cli)

**PdoOne** has some features available only in CLI.

[![](examples/cli.jpg)](examples/cli.jpg)

### Run as cli

[](#run-as-cli)

Execute the next line (in the lib folder)

> php pdoonecli.php

(or pointing to the right folder)

> php /var/web/vendor/eftec/lib/pdoonecli

### Run as CLI interative

[](#run-as-cli-interative)

You could use the flag "-i" to enter in interactive mode.

You could use the TAB key to autocomplete values (if any).

[![](examples/cli2.jpg)](examples/cli2.jpg)

Note: You could also save and load the configuration.

#### Examples

[](#examples-1)

Connect to mysql and generate a csv from the table "actor"

```
## via arguments
php pdoonecli --databasetype mysql --server 127.0.0.1 -u root -p abc.123 --database sakila -in actor -out csv
## via user input (interactive)
php pdoonecli -i -in actor -out csv
```

Save the configuration in a file

```
php pdoonecli --databasetype mysql --server 127.0.0.1 -u root -p abc.123 --database sakila --saveconfig myconfig
```

Load the configuration from a file

```
php pdoonecli --loadconfig myconfig -in actor -out csv
```

### Run CLI to generate repository classes.

[](#run-cli-to-generate-repository-classes)

You could use the flag "-cli" to generate the repository classes

[![](examples/cli3.jpg)](examples/cli3.jpg)

The CLI is interactive, and it allows to load and save the configuration.

### cli-classcode

[](#cli-classcode)

The functionality will generate a ready-to-use repository class.

Let's say the next example

> mysql:
> php pdoone.php --database mysql --server 127.0.0.1:3306 --user root -p abc.123 -db sakila --input "Actor" --output classcode
> sqlsrv:
> php pdoone.php --database sqlsrv --server PCJC\\SQLEXPRESS --user sa -p abc.123 -db sakila --input "Actor" --output classcode

It will connect to the database mysql, ip: 127.0.0.1 and database sakila, and it will read the "actor" table.

It will return the next result

```
/**
 * 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
     * 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
    }
    // .....
}
```

This functionality will generate a new Repository class with the most common operations: insert, list, update, delete, get, count, create table, drop table and truncate table

Why we need to generate a class? (instead of inherit one) This Crud class is only a starting point. The developer could modify the code, add new methods, modify previous method and so on.

For to use the class, we could write the next code:

```
// 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.
```

Alternatively, you could generate the php file automatically as follows:

> php pdoone.php -database mysql -server 127.0.0.1:3306 -user root -pwd abc.123 -db sakila -input "Actor" -output classcode >ActorRepo.php

Note: the code lacks of php-tags, namespace and use but everything else is here.

### cli-selectcode

[](#cli-selectcode)

It will take a query and will return a php code with the query formatted.

Example:

> php pdoone.php -database mysql -server 127.0.0.1:3306 -user root -pwd abc.123 -db sakila -input "select \* from actor" -output selectcode

It will generate the next code:

```
 /** @var array $result=array(["actor_id"=>0,"first_name"=>'',"last_name"=>'',"last_update"=>'']) */
 $result=$pdo
         ->select("*")
         ->from("actor")
         ->toList();
```

### cli-arraycode

[](#cli-arraycode)

It will generate an associative array (with default values) based in the query or table selected.

> php pdoone.php -database mysql -server 127.0.0.1:3306 -user root -pwd abc.123 -db sakila -input "select \* from actor" -output arraycode

It will return:

```
// ["actor_id"=>0,"first_name"=>'',"last_name"=>'',"last_update"=>'']
```

### cli-json

[](#cli-json)

It will return the result of the query as a json

> php pdoone.php -database mysql -server 127.0.0.1:3306 -user root -pwd abc.123 -db sakila -input "select \* from actor" -output json

It will return:

```
[{"actor_id":"1","first_name":"PENELOPE","last_name":"GUINESS","last_update":"2006-02-15 01:34:33"}
,{"actor_id":"2","first_name":"NICK","last_name":"WAHLBERG","last_update":"2006-02-15 01:34:33"}
,{"actor_id":"3","first_name":"ED","last_name":"CHASE","last_update":"2006-02-15 01:34:33"}
,{"actor_id":"4","first_name":"JENNIFER","last_name":"DAVIS","last_update"}]
```

### cli-csv

[](#cli-csv)

It will return the result of the query as a json

> php pdoone.php -database mysql -server 127.0.0.1:3306 -user root -pwd abc.123 -db sakila -input "select \* from actor" -output csv

It will return:

```
actor_id,first_name,last_name,last_update
1,"PENELOPE","GUINESS","2006-02-15 01:34:33"
2,"NICK","WAHLBERG","2006-02-15 01:34:33"
3,"ED","CHASE","2006-02-15 01:34:33"
4,"JENNIFER","DAVIS","2006-02-15 01:34:33"
```

### UI

[](#ui)

Alternatively to the CLI, the library has an interface visual. It does all the operation of the CLI.

[![](examples/ui.jpg)](examples/ui.jpg)

### How to run the UI?

[](#how-to-run-the-ui)

Simply call the method render()

```