PHPackages                             davidecesarano/embryo-pdo - 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. davidecesarano/embryo-pdo

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

davidecesarano/embryo-pdo
=========================

Embryo PDO is a PHP SQL query builder using PDO.

1.0.7(4y ago)914.6k↓48.7%6[1 PRs](https://github.com/davidecesarano/Embryo-PDO/pulls)1MITPHPPHP >=7.1

Since Aug 19Pushed 4y ago2 watchersCompare

[ Source](https://github.com/davidecesarano/Embryo-PDO)[ Packagist](https://packagist.org/packages/davidecesarano/embryo-pdo)[ Docs](https://github.com/davidecesarano/embryo-pdo)[ RSS](/packages/davidecesarano-embryo-pdo/feed)WikiDiscussions master Synced 1mo ago

READMEChangelog (8)Dependencies (2)Versions (9)Used By (1)

Embryo PDO
==========

[](#embryo-pdo)

A quick and light PHP query builder using PDO.

```
$users = $pdo->table('users')
    ->where('country', 'Italy')
    ->and('city', 'Naples')
    ->and(function($query) {
        $query
            ->where('age', 20)
            ->or('age', 30)
    })
    ->andIsNotNull('updated_at')
    ->andIn('roles', [1, 2, 3])
    ->get();
```

Requirements
------------

[](#requirements)

- PHP >= 7.1

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

[](#installation)

Using Composer:

```
$ composer require davidecesarano/embryo-pdo

```

Usage
-----

[](#usage)

- [Connection](#connection)
- [Retrieving results](#retrieving-results)
    - [Retrieving a single row](#retrieving-a-single-row)
    - [Forcing array](#forcing-array)
    - [Aggregates](#aggregates)
- [Where conditions](#where-conditions)
    - [Simple Where](#simple-where)
    - [OR condition](#or-condition)
    - [AND/OR closure](#andor-closure)
    - [BETWEEN condition](#between-condition)
    - [IN condition](#in-condition)
    - [IS NULL condition](#is-null-condition)
    - [Raw Where](#raw-where)
    - [Method Aliases](#method-aliases)
- [Joins](#joins)
- [Insert](#insert)
- [Update](#update)
- [Delete](#delete)
- [Ordering, grouping, limit and offset](#ordering-grouping-limit-and-offset)
- [Raw Query](#raw-query)
- [Pagination](#pagination)
- [Security](#security)
- [Debugging](#debugging)

### Connection

[](#connection)

Create a multidimensional array with database parameters and pass it at the `Database` object. Later, create connection with `connection` method.

```
$database = [
    'local' => [
        'engine'   => 'mysql',
        'host'     => '127.0.0.1',
        'name'     => 'db_name',
        'user'     => 'user',
        'password' => 'password',
        'charset'  => 'utf8mb4',
        'options'  => [
            \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION
        ]
    ]
];

$database = new Embryo\PDO\Database($database);
$pdo = $database->connection('local');
```

### Retrieving results

[](#retrieving-results)

You can create a simple query:

```
$users = $pdo->table('users')->get();
```

The `table` method returns a fluent query builder instance for the given table. This would build the query below:

```
SELECT * FROM users
```

To get data from the select, we can is loop through the returned array of objects:

```
foreach ($users as $user) {
    echo $user->name;
}
```

#### Retrieving a single row

[](#retrieving-a-single-row)

If you just need to retrieve a single row from the database table, you may use the same (`get`) method.

```
$user = $pdo->table('users')
    ->where('id', 1)
    ->get();

echo $user->name;
```

If you don't even need an entire row, you may extract one or more values from a record using the `select` method.

```
$user = $pdo->table('users')
    ->select('name, surname')
    ->where('id', 1)
    ->get();

echo "Hi, i am $user->name $user->surname";
```

#### Forcing array

[](#forcing-array)

If you want to force return array of objects, you can use `all` method

```
$user = $pdo->table('users')
    ->where('id', 1)
    ->all();

foreach ($users as $user) {
    echo $user->name;
}
```

#### Aggregates

[](#aggregates)

The query builder also provides a variety of aggregate methods such as `count`, `max`, `min`, `avg`, and `sum`.

```
$avg = $pdo->table('orders')->avg('price');
echo $avg;
```

### Where conditions

[](#where-conditions)

#### Simple Where

[](#simple-where)

You may use the `where` method to add where clauses to the query. The most basic call to where requires three arguments. The first argument is the name of the column. The second argument is an operator, which can be any of the database's supported operators. Finally, the third argument is the value to evaluate against the column.

```
$users = $pdo->table('users')
    ->where('id', '>', 1)
    ->get();
```

For convenience, if you want to verify that a column is equal to a given value, you may pass the value directly as the second argument to the where method:

```
$user = $pdo->table('users')
    ->where('id', 1)
    ->get();
```

You may use a variety of other operators when writing a where clause:

```
$user = $pdo->table('users')
    ->where('country', 'Italy')
    ->and('name', 'LIKE', 'David%')
    ->get();
```

#### OR condition

[](#or-condition)

You may chain where constraints together as well as add "or" clauses to the query.

```
$user = $pdo->table('users')
    ->where('country', 'Italy')
    ->or('country', 'Spain')
    ->get();
```

#### AND/OR closure

[](#andor-closure)

If you need to group an "or" or "and" condition within parentheses, you may pass a Closure as the first argument to the method:

```
$user = $pdo->table('users')
    ->where('country', 'Italy')
    ->and(function($query){
        $query
            ->where('country', 'Spain')
            ->or('country', 'France')
    })
    ->get();
```

This would build the query below:

```
SELECT *
FROM users
WHERE country = 'Italy'
AND (
    country = 'Spain'
    OR country = 'France'
)
```

#### BETWEEN condition

[](#between-condition)

The `whereBetween` / `whereNotBetween` method verifies that a column's value is between / not between two values:

```
$user = $pdo->table('users')
    ->whereBetween('age', [20, 30])
    ->get();

$user = $pdo->table('users')
    ->whereNotBetween('age', [20, 30])
    ->get();
```

#### IN condition

[](#in-condition)

The `whereIn` / `whereNotIn` method verifies that a given column's value is contained / not contained within the given array:

```
$user = $pdo->table('users')
    ->whereIn('age', [20, 30])
    ->get();

$user = $pdo->table('users')
    ->whereNotIn('age', [20, 30])
    ->get();
```

#### IS NULL condition

[](#is-null-condition)

The `whereNull` / `whereNotNull` method verifies that the value of the given column is `NULL` / not NULL:

```
$user = $pdo->table('users')
    ->whereNull('updated_at')
    ->get();

$user = $pdo->table('users')
    ->whereNotNull('updated_at')
    ->get()
```

#### Raw Where

[](#raw-where)

The `rawWhere` method can be used to inject a raw where condition into your query. This method accept an array of bindings argument.

```
$users = $pdo->table('users')
    ->rawWhere('WHERE age = :age AND role = :role', [
        'age' => 20,
        'role' => 1
    ])
    ->get();
```

#### Method aliases

[](#method-aliases)

Below is a table with all the methods of the where conditions and their aliases.

MethodAliaswhere()and()
andWhere()orWhere()or()whereBetween()andBetween()
andWhereBetween()orWhereBetween()orBetween()whereNotBetween()andNotBetween()
andWhereNotBetween()orWhereNotBetween()orNotBetween()whereIn()andIn()
andWhereIn()orWhereIn()orIn()whereNotIn()andNotIn()
andWhereNotIn()orWhereNotIn()orNotIn()whereNull()andNull()
andWhereNull()
whereIsNull()
andIsNull()
andWhereIsNull()orWhereNull()orNull()
orWhereIsNull()
orIsNull()whereNotNull()andNotNull()
andWhereNotNull()
whereIsNotNull()
andIsNotNull()
andWhereIsNotNull()orWhereNotNull()orNotNull()
orWhereIsNotNull()
orIsNotNull()### Joins

[](#joins)

The query builder may also be used to write simple join statements with `leftJoin`, `rightJoin`, `crossJoin`, `innerJoin` or `rawJoin` methods:

```
// left join
$users = $pdo->table('users')
    ->leftJoin('roles ON roles.id = users.role_id')
    ->select('users.*', 'roles.name')
    ->get();

// right join
$users = $pdo->table('users')
    ->rightJoin('roles ON roles.id = users.role_id')
    ->select('users.*', 'roles.name')
    ->get();

// cross join
$users = $pdo->table('users')
    ->crossJoin('roles ON roles.id = users.role_id')
    ->select('users.*', 'roles.name')
    ->get();

// inner join
$users = $pdo->table('users')
    ->innerJoin('roles ON roles.id = users.role_id')
    ->select('users.*', 'roles.name')
    ->get();

// raw join
$users = $pdo->table('users')
    ->rawJoin('LEFT JOIN roles ON roles.id = users.role_id')
    ->select('users.*', 'roles.name')
    ->get();
```

### Insert

[](#insert)

You can insert row/s in database with `insert` method.

```
$lastInsertedId = $pdo->table('users')
    ->insert([
        'name' => 'Name',
        'surname' => 'Surname'
    ])
    ->lastId();
```

This will return the last inserted id. The insert method also accepts the `exec()` method and it will return true on success or false on failure.

### Update

[](#update)

You can update row/s with `update` method.

```
$update = $pdo->table('users')
    ->where('id', 1)
    ->update([
        'name' => 'Name',
        'surname' => 'Surname'
    ])
    ->exec();

// $update return TRUE or FALSE
```

### Delete

[](#delete)

You can delete row/s with `delete` method.

```
$delete = $pdo->table('users')
    ->where('id', 1)
    ->delete()
    ->exec();

// $delete return TRUE or FALSE
```

### Ordering, grouping, limit and offset

[](#ordering-grouping-limit-and-offset)

You may use the `groupBy` method to group the query results.

```
$users = $pdo->table('users')
    ->groupBy('role')
    ->get();
```

The `orderBy` method allows you to sort the result of the query by a given column:

```
$users = $pdo->table('users')
    ->orderBy('id DESC')
    ->get();
```

You may use the `limit` method to limit the number of results returned from the query:

```
$users = $pdo->table('users')
    ->limit('0,10')
    ->get();
```

To skip a given number of results in the query, you may use the `limit` and `offset` methods:

```
$users = $pdo->table('users')
    ->limit('10')
    ->offset(5)
    ->get();
```

### Raw Query

[](#raw-query)

Sometimes you may need to use a raw expression in a query. To create a raw expression, you may use the `query` method:

```
$users = $pdo->query("
    SELECT
        users.*,
        roles.name
    FROM users
    LEFT JOIN roles ON roles.id = users.role_id
    WHERE users.city = :city
    ORDER BY users.id DESC
")->values([
    'city' => 'Naples'
])->get();
```

The `values` method binds a value to a parameter. Binds a value to a corresponding named placeholder in the SQL statement that was used to prepare the statement.

### Pagination

[](#pagination)

Pagination means displaying all your fetched results in multiple pages instead of showing them all on one page. To change the page number use `page` query param in URI (`http://example.com/?page=1`).

```
$perPage = 15;
$users = $pdo->table("users")->paginate($perPage);
```

We will have this result:

```
{
   "total": 50,
   "per_page": 15,
   "current_page": 1,
   "last_page": 4,
   "first_page": 1,
   "next_page": 2,
   "prev_page": null,
   "from": 1,
   "to": 15,
   "data":[
        {
            // Record...
        },
        {
            // Record...
        }
   ]
}
```

If you want retrieve specific fields from records you may use:

```
$perPage = 15;
$users = $pdo->table("users")->select('id, first_name, last_name')->paginate($perPage);
```

### Security

[](#security)

Embryo PDO uses **PDO parameter binding** to protect your application against SQL injection attacks. There is no need to clean strings being passed as bindings.

### Debugging

[](#debugging)

You may use the `debug` method for for to dumps the information contained by a prepared statement directly on the output.

```
    $fruits = $pdo->table('fruit')
        ->where('calories', '