PHP code example of simplon / mysql

    
        
<?php
require_once('vendor/autoload.php');

/* Start to develop here. Best regards https://php-download.com/ */

    

simplon / mysql example snippets

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

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

$store = new NamesStore($dbConn);

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

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

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

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

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

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

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

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

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

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

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

$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

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

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

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

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

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

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

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

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

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

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

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

var_dump($result); // true || 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

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

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

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

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

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

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

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

var_dump($result); // true || 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

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

var_dump($result); // true

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;
    }
}

namespace Test\Crud;

use Simplon\Mysql\CrudModel;

/**
 * @package Test\Crud
 */
class NameModel extends CrudModel
{
    const COLUMN_ID = 'id';
    const COLUMN_NAME = 'name';
    const COLUMN_AGE = 'age';

    /**
     * @var int
     */
    protected $id;
    /**
     * @var string
     */
    protected $name;
    /**
     * @var int
     */
    protected $age;

    /**
     * @return int
     */
    public function getId(): int
    {
        return (int)$this->id;
    }

    /**
     * @param int $id
     *
     * @return NameModel
     */
    public function setId(int $id): NameModel
    {
        $this->id = $id;

        return $this;
    }

    /**
     * @return string
     */
    public function getName(): string
    {
        return $this->name;
    }

    /**
     * @param string $name
     *
     * @return NameModel
     */
    public function setName(string $name): NameModel
    {
        $this->name = $name;

        return $this;
    }

    /**
     * @return int
     */
    public function getAge(): int
    {
        return (int)$this->age;
    }

    /**
     * @param int $age
     *
     * @return NameModel
     */
    public function setAge(int $age): NameModel
    {
        $this->age = $age;

        return $this;
    }
}

$store = new NamesStore($pdoConn);

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

echo $model->getId(); // prints user id

$model = $store->readOne(
    ReadQueryBuilder::create()->addCondition(NameModel::COLUMN_AGE, 20, '>')
);

echo $model->getId(); // prints user id

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

echo $models[0]->getId(); // prints user id from first matched model

$model = $store->create(
    CreateQueryBuilder::create()->setModel(
        (new NameModel())
            ->setName('Johnny')
            ->setAge(22)
    )
);

//
// fetch a model
//

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

//
// update age
//

$model->setAge(36);

//
// persist change
//

$model = $store->update(
    UpdateQueryBuilder::create()
        ->setModel($model)
        ->addCondition(NameModel::COLUMN_ID, $model->getId())
);

//
// fetch a model
//

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

//
// delete model from store
//

$store->delete(
    DeleteQueryBuilder::create()
        ->addCondition(NameModel::COLUMN_ID, $model->getId())
)

/**
 * @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;
}

$ids = array(1,2,3,4,5);
$query = "SELECT * FROM users WHERE id IN (" . join(',', $ids) . ")";

$emails = array('[email protected]', '[email protected]');
$query = "SELECT * FROM users WHERE email IN (" . join(',', $emails) . ")";

// integers
$conds = array('ids' => array(1,2,3,4,5));
$query = "SELECT * FROM users WHERE id IN (:ids)";

// strings
$conds = array('emails' => array('[email protected]', '[email protected]'));
$query = "SELECT * FROM users WHERE email IN (:emails)";