PHPackages                             simplon/mysql - 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. simplon/mysql

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

simplon/mysql
=============

Simplon MySQL Library

3.1.0(1y ago)6988.1k—6.8%23[8 issues](https://github.com/fightbulc/simplon_mysql/issues)[1 PRs](https://github.com/fightbulc/simplon_mysql/pulls)7MITPHPPHP >=8.2CI failing

Since Dec 2Pushed 1y ago6 watchersCompare

[ Source](https://github.com/fightbulc/simplon_mysql)[ Packagist](https://packagist.org/packages/simplon/mysql)[ Docs](https://github.com/fightbulc/simplon_mysql)[ RSS](/packages/simplon-mysql/feed)WikiDiscussions master Synced 1mo ago

READMEChangelog (2)Dependencies (1)Versions (75)Used By (7)

```
     _                 _                                         _
 ___(_)_ __ ___  _ __ | | ___  _ __    _ __ ___  _   _ ___  __ _| |
/ __| | '_ ` _ \| '_ \| |/ _ \| '_ \  | '_ ` _ \| | | / __|/ _` | |
\__ \ | | | | | | |_) | | (_) | | | | | | | | | | |_| \__ \ (_| | |
|___/_|_| |_| |_| .__/|_|\___/|_| |_| |_| |_| |_|\__, |___/\__, |_|
                |_|                              |___/        |_|
```

Simplon/Mysql
=============

[](#simplonmysql)

---

1. [**Installing**](#1-installing)
2. [**Direct vs. CRUD**](#2-direct-vs-crud)
3. [**Setup connection**](#3-setup-connection)
4. [**Usage: Direct access**](#4-usage-direct-access)
    4.1. Query
    4.2. Insert
    4.3. Update
    4.4. Replace
    4.5. Delete
    4.6. Execute
5. [**Usage: CRUD**](#5-usage-crud)
    5.1. Setup store
    5.2. Setup model
    5.3. Connect to store
    5.4. Query
    5.5. Insert
    5.6. Update
    5.7. Delete
    5.8. Custom queries
6. [**IN() Clause Handling**](#6-in-clause-handling)
    6.1. The issue
    6.2. The solution
7. [**Exceptions**](#7-exceptions)

---

### Dependecies

[](#dependecies)

- PHP >= 7.1
- PDO

---

1. Installing
-------------

[](#1-installing)

Easy install via composer. Still no idea what composer is? Inform yourself [here](http://getcomposer.org).

```
{
  "require": {
    "simplon/mysql": "*"
  }
}
```

---

2. Direct vs. CRUD
------------------

[](#2-direct-vs-crud)

I implemented two different ways of interacting with MySQL. The first option is the usual one which interacts directly with the database. Following a straight forward example to show you what I mean:

```
$data = $dbConn->fetchRow('SELECT * FROM names WHERE name = :name', ['name' => 'Peter']);

//
// $data is an array with our result
//
```

In constrast to the prior method CRUD is more structured with `store` and `model`. Further, it uses [Builder Patterns](http://sourcemaking.com/design_patterns/builder) to interact with the database. A quick example of how we would rewrite the above `direct query`:

```
$store = new NamesStore($dbConn);

$model = $store->read(
    ReadQueryBuilder::create()->addCondition(NameModel::COLUMN_NAME, 'Peter')
);

//
// $model is a class with our result data abstracted
//
```

---

3. Setup connection
-------------------

[](#3-setup-connection)

The library requires a config value object in order to instantiate a connection with MySQL. See how it's done:

```
$pdo = new PDOConnector(
	'localhost', // server
	'root',      // user
	'root',      // password
	'database'   // database
);

$pdoConn = $pdo->connect('utf8', []); // charset, options

//
// you could now interact with PDO for instance setting attributes etc:
// $pdoConn->setAttribute($attribute, $value);
//

$dbConn = new Mysql($pdoConn);
```

---

4. Usage: Direct access
-----------------------

[](#4-usage-direct-access)

### 4.1. Query

[](#41-query)

#### FetchColumn

[](#fetchcolumn)

Returns a selected column from the first match. The example below returns `id` or `null` if nothing was found.

```
$result = $dbConn->fetchColumn('SELECT id FROM names WHERE name = :name', ['name' => 'Peter']);

// result
var_dump($result); // '1' || null
```

#### FetchColumnMany

[](#fetchcolumnmany)

Returns an array with the selected column from all matching datasets. In the example below an array with all `ids` will be returned or `null` if nothing was found.

```
$result = $dbConn->fetchColumnMany('SELECT id FROM names WHERE name = :name', ['name' => 'Peter']);

// result
var_dump($result); // ['1', '15', '30', ...] || null
```

#### FetchColumnManyCursor

[](#fetchcolumnmanycursor)

Returns one matching dataset at a time. It is resource efficient and therefore handy when your result has many data. In the example below you either iterate through the foreach loop in case you have matchings or nothing will happen.

```
$cursor = $dbConn->fetchColumnMany('SELECT id FROM names WHERE name = :name', ['name' => 'Peter']);

foreach ($cursor as $result)
{
    var_dump($result); // '1'
}
```

#### FetchRow

[](#fetchrow)

Returns all selected columns from a matched dataset. The example below returns `id`, `age` for the matched dataset. If nothing got matched `null` will be returned.

```
$result = $dbConn->fetchRow('SELECT id, age FROM names WHERE name = :name', ['name' => 'Peter']);

var_dump($result); // ['id' => '1', 'age' => '22'] || null
```

#### FetchRowMany

[](#fetchrowmany)

Returns all selected columns from all matched dataset. The example below returns for each matched dataset `id`, `age`. If nothing got matched `null` will be returned.

```
$result = $dbConn->fetchRowMany('SELECT id, age FROM names WHERE name = :name', ['name' => 'Peter']);

var_dump($result); // [ ['id' => '1', 'age' => '22'],  ['id' => '15', 'age' => '40'], ... ] || null
```

#### FetchRowManyCursor

[](#fetchrowmanycursor)

Same explanation as for `FetchColumnManyCursor` except that we receive all selected columns.

```
$result = $dbConn->fetchRowMany('SELECT id, age FROM names WHERE name = :name', ['name' => 'Peter']);

foreach ($cursor as $result)
{
    var_dump($result); // ['id' => '1', 'age' => '22']
}
```

---

### 4.2. Insert

[](#42-insert)

#### Single data

[](#single-data)

Inserting data into the database is pretty straight forward. Follow the example below:

```
$data = [
    'id'   => false,
    'name' => 'Peter',
    'age'  => 45,
];

$id = $dbConn->insert('names', $data);

var_dump($id); // 50 || bool
```

The result depends on the table. If the table holds an `autoincrementing ID` column you will receive the ID count for the inserted data. If the table does not hold such a field you will receive `true` for a successful insert. If anything went bogus you will receive `false`.

#### Many datasets

[](#many-datasets)

Follow the example for inserting many datasets at once:

```
$data = [
    [
        'id'   => false,
        'name' => 'Peter',
        'age'  => 45,
    ],
    [
        'id'   => false,
        'name' => 'Peter',
        'age'  => 16,
    ],
];

$id = $dbConn->insertMany('names', $data);

var_dump($id); // 50 || bool
```

The result depends on the table. If the table holds an `autoincrementing ID` column you will receive the ID count for the inserted data. If the table does not hold such a field you will receive `true` for a successful insert. If anything went bogus you will receive `false`.

---

### 4.3. Updating

[](#43-updating)

#### Simple update statement

[](#simple-update-statement)

Same as for insert statements accounts for updates. Its easy to understand. If the update succeeded the response will be `true`. If nothing has been updated you will receive `null`.

```
$conds = [
    'id' => 50,
];

$data = [
    'name' => 'Peter',
    'age'  => 50,
];

$result = $dbConn->update('names', $conds, $data);

var_dump($result); // true || null
```

#### Custom update conditions query

[](#custom-update-conditions-query)

Same as for insert statements accounts for updates. Its easy to understand. If the update succeeded the response will be `true`. If nothing has been updated you will receive `null`.

```
$conds = [
    'id'   => 50,
    'name' => 'Peter',
];

// custom conditions query
$condsQuery = 'id = :id OR name =: name';

$data = [
    'name' => 'Peter',
    'age'  => 50,
];

$result = $dbConn->update('names', $conds, $data, $condsQuery);

var_dump($result); // true || null
```

---

### 4.4. Replace

[](#44-replace)

As MySQL states it: `REPLACE` works exactly like `INSERT`, except that if an old row in the table has the same value as a new row for a `PRIMARY KEY` or a `UNIQUE index`, the old row is deleted before the new row is inserted.

#### Replace a single datasets

[](#replace-a-single-datasets)

As a result you will either receive the `INSERT ID` or `false` in case something went wrong.

```
$data = [
    'id'   => 5,
    'name' => 'Peter',
    'age'  => 16,
];

$result = $dbConn->replace('names', $data);

var_dump($result); // 1 || false
```

#### Replace multiple datasets

[](#replace-multiple-datasets)

As a result you will either receive an array of `INSERT IDs` or `false` in case something went wrong.

```
$data = [
    [
        'id'   => 5,
        'name' => 'Peter',
        'age'  => 16,
    ],
    [
        'id'   => 10,
        'name' => 'John',
        'age'  => 22,
    ],
];

$result = $dbConn->replaceMany('names', $data);

var_dump($result); // [5, 10]  || false
```

---

### 4.5. Delete

[](#45-delete)

#### Simple delete conditions

[](#simple-delete-conditions)

The following example demonstrates how to remove data. If the query succeeds we will receive `true` else `false`.

```
$result = $dbConn->delete('names', ['id' => 50]);

var_dump($result); // true || false
```

#### Custom delete conditions query

[](#custom-delete-conditions-query)

The following example demonstrates how to remove data with a custom conditions query. If the query succeeds we will receive `true` else `false`.

```
$conds = [
    'id'   => 50,
    'name' => 'John',
];

// custom conditions query
$condsQuery = 'id = :id OR name =: name';

$result = $dbConn->delete('names', $conds, $condsQuery);

var_dump($result); // true || false
```

---

### 4.6. Execute

[](#46-execute)

This method is ment for calls which do not require any parameters such as `TRUNCATE`. If the call succeeds you will receive `true`. If it fails an `MysqlException` will be thrown.

```
$result = $dbConn->executeSql('TRUNCATE names');

var_dump($result); // true
```

---

5. Usage: CRUD
--------------

[](#5-usage-crud)

The following query examples will be a rewrite of the aforementioned `direct access` examples. For this we need a `Store` and a related `Model`.

### 5.1. Setup store

[](#51-setup-store)

```
namespace Test\Crud;

use Simplon\Mysql\CreateQueryBuilder;use Simplon\Mysql\CrudModelInterface;use Simplon\Mysql\CrudStore;use Simplon\Mysql\DeleteQueryBuilder;use Simplon\Mysql\MysqlException;use Simplon\Mysql\ReadQueryBuilder;use Simplon\Mysql\UpdateQueryBuilder;

/**
 * @package Test\Crud
 */
class NamesStore extends CrudStore
{
    /**
     * @return string
     */
    public function getTableName(): string
    {
        return 'names';
    }

    /**
     * @return CrudModelInterface
     */
    public function getModel(): CrudModelInterface
    {
        return new NameModel();
    }

    /**
     * @param CreateQueryBuilder $builder
     *
     * @return NameModel
     * @throws MysqlException
     */
    public function create(CreateQueryBuilder $builder): NameModel
    {
        /** @var NameModel $model */
        $model = $this->crudCreate($builder);

        return $model;
    }

    /**
     * @param ReadQueryBuilder|null $builder
     *
     * @return NameModel[]|null
     * @throws MysqlException
     */
    public function read(?ReadQueryBuilder $builder = null): ?array
    {
        /** @var NameModel[]|null $response */
        $response = $this->crudRead($builder);

        return $response;
    }

    /**
     * @param ReadQueryBuilder $builder
     *
     * @return null|NameModel
     * @throws MysqlException
     */
    public function readOne(ReadQueryBuilder $builder): ?NameModel
    {
        /** @var NameModel|null $response */
        $response = $this->crudReadOne($builder);

        return $response;
    }

    /**
     * @param UpdateQueryBuilder $builder
     *
     * @return NameModel
     * @throws MysqlException
     */
    public function update(UpdateQueryBuilder $builder): NameModel
    {
        /** @var NameModel|null $model */
        $model = $this->crudUpdate($builder);

        return $model;
    }

    /**
     * @param DeleteQueryBuilder $builder
     *
     * @return bool
     * @throws MysqlException
     */
    public function delete(DeleteQueryBuilder $builder): bool
    {
        return $this->crudDelete($builder);
    }

    /**
     * @param int $id
     *
     * @return null|NameModel
     * @throws MysqlException
     */
    public function customMethod(int $id): ?NameModel
    {
        $query = 'SELECT * FROM ' . $this->getTableName() . ' WHERE id=:id';

        if ($result = $this->getCrudManager()->getMysql()->fetchRow($query, ['id' => $id]))
        {
            return (new NameModel())->fromArray($result);
        }

        return null;
    }
}
```

### 5.2. Setup model

[](#52-setup-model)

```