EntityModel for PHP 7 PDO Queries ($1728351) · Snippets

Example use in a PHP file

<?php
 use Lynq\Entity\EntityModel;

class UserModel
{

    private $table='users';
    private $yourDB;
   public function __construct()
    {
        $dsn = 'mysql:dbname=yourDBname;host=127.0.0.1';
        $user = 'root';
        $password = 'glory';
        $this->yourDB = new EntityModel($dsn, $user, $password);
    }
    public function getUsers()
    {
        return $this->yourDB->table($this->table)
                    ->get();
    }

    public function getUser(int $id)
    {
        return  $this->yourDB->table($this->table)
                    ->where('id', $id)
                    ->get();
    }
}

$this->yourDB->sql('SELECT * FROM users t WHERE u.age > 45 LIMIT 10 ORDER BY u.name')
        ->get();

SELECT All Users :

$this->yourDB->table('user')
          ->get();

SELECT All Users DISTINCT:

$this->yourDB->table('user')
          ->distinct();

Count All Users :

$this->yourDB->table('user')
         ->count();

SELECT All Users with only id and name Fields:

$this->yourDB->table('user')
          ->fields('id, name')
          ->get();

SELECT All Users with only id and name Fields 'AS' username:

$this->yourDB->table('user')
          ->fields('id, name AS username')
          ->get();

SELECT All Users Limit to 10 :

$this->yourDB->table('user')
          ->limit(10)
          ->get();

SELECT All Users Limit to 10 Offset 100:

$this->yourDB->table('user')
         ->limit(10)
         ->offset(100)
         ->get();

SELECT First User in Users row :

$this->yourDB->table('user')->first();

SELECT Last User in Users row :

$this->yourDB->table('user')->last();

SELECT All Users Order by name DESCENDING :

DB::table('user')
       ->orderBy('name')
       ->get();

SELECT All Users Order by ASCENDING :

$this->yourDB->table('user')
          ->orderBy('name',2)
           ->get();

SELECT User with id == 3 :

$this->yourDB->table('user')
        ->where('id',3)
    	  ->get();

SELECT User with id != 3 :

$this->yourDB->table('user')
        ->where('id','!=',3)
        ->get();

SELECT User with id < 3 :

$this->yourDB->table('user')
        ->where('id','<',3)
        ->get();

SELECT User with id > 3 :

$this->yourDB->table('user')
          ->where('id','>',3)
          ->get();

SELECT User with id <= 3 :

$this->yourDB->table('user')
          ->where('id','<=',3)
          ->get();

SELECT User with name LIKE 'kel' :

$this->yourDB->table('user')
          ->where('id','LIKE','%kel%')
          ->get();

** These returns a single object

SELECT A Single User with id == 3 :

$this->yourDB->table('user')
          ->where('id',3)
          ->single();

SELECT Users with id == 3 or name = 'kelvin' :

$this->yourDB->table('user')
          ->where('id',3)
          ->orWhere('name','kelvin')
          ->get();

SELECT Users with id == 3 and name = 'kelvin' :

$this->yourDB->table('user')
          ->where('id',3)
          ->andWhere('name','kelvin')
          ->get();

** These returns a true or false if its sucessfull or failed

INSERT New User:

$data = [
  'name'=>'kelvin',
  'email'=>'kelvin@air.com',
  'gender'=>'male'
  ];
$this->yourDB->table('user')
          ->add($data);

UPDATE User with id = 3 :

$data = [
  'name'=>'kelvin',
  'email'=>'kelvin@air.com',
  'gender'=>'male'
  ];
$this->yourDB->table('user')
          ->where('id',3)
          ->update($data);

DELETE User with id = 3 :

$this->yourDB->table('user')
          ->where('id',3)
          ->delete();

JOIN queries
- SELECT * FROM Users u and INNER JOIN comments c ON u.id == c.userId

$this->yourDB->table('user u')
        ->join('comment','c')
        ->on('u.id','c.userId')
        ->get();

SELECT * FROM Users u and LEFT JOIN comments c ON u.id == c.userId

$this->yourDB->table('user u')
    ->leftJoin('comment','c')
    ->on('u.id','c.userId')
    ->get();

SELECT * FROM Users u and RIGHT JOIN comments c ON u.id == c.userId

$this->yourDB->table('user u')
    ->rightJoin('comment','c')
    ->on('u.id','c.userId')
    ->get();

The letter 'u' in the table method after the table name 'user' is the alias of the table.
There also is the rightJoin, leftJoin, innerJoin, fullJoin
GROUP BY
SELECT Users u and RIGHT JOIN comments c ON u.id == c.userId GROUP BY u.id

$this->yourDB->table('user u')
    ->rightJoin('comment','c')
    ->on('u.id','c.userId')
    ->groupBy('t.id')
    ->get();

MULTI DATABASE
SELECT identityDB.Users u and INNER JOIN blogDB.comments c ON u.id

$this->yourDB->table('identityDB.user u')
    ->join('blogDB.comment','c')
    ->on('u.id','c.userId')
    ->groupBy('t.id')
    ->get();