neutronstars/database-sql

Sql connector library for PHP

1.0.7 2021-06-30 22:32 UTC

Requires

• php: >=7.4.0
• ext-pdo: *

Requires (Dev)

• phpunit/phpunit: ^9.5

Apache-2.0

• NeutronStars

README

Installation

composer require neutronstars/database-sql

{
  "require": {
    "neutronstars/database-sql": ">=1.0.*"
  }
}

Importation

<?php
  require_once __DIR__.DIRECTORY_SEPARATOR.'..'.DIRECTORY_SEPARATOR.'vendor'.DIRECTORY_SEPARATOR.'autoload.php';
  use NeutronStars\Database\Database;

Connection

$options = [
  'url'      => '127.0.0.1',
  'port'     => 3306,
  'user'     => 'root',
  'password' => '',
  'charset'  => 'utf8mb4'
];
$database = new Database('database_name', $options);

SQL Request Samples

SELECT

SELECT * FROM table;

$results = $database->query('table')
                    ->select('*')
                    ->getResults();

SELECT name,email FROM table where id=4;

$result = $database->query('table')
                   ->select('name', 'email')
                   ->where('id=:id')
                   ->setParameters([
                      ':id' => 4
                   ])
                   ->getResult();

SELECT * FROM table LIMIT 10 OFFSET 20;
SELECT * FROM table LIMIT 20,10;

$results = $database->query('table')
                   ->select('*')
                   ->limit(10, 20)
                   ->getResults();

SELECT * FROM table ORDER BY points DESC;

use NeutronStars\Database\Query;

$results = $database->query('table')
                   ->select('*')
                   ->orderBy('points', Query::ORDER_BY_DESC)
                   ->getResults();

SELECT * FROM table GROUP BY name;

$results = $database->query('table')
                   ->select('*')
                   ->groupBy('name')
                   ->getResults();

SELECT * FROM table1 t1 LEFT JOIN table2 t2 ON t1.id = t2.id;

use NeutronStars\Database\Query;

$results = $database->query('table t1')
                   ->select('*')
                   ->join('table2 t2', 't1.id = t2.id', Query::LEFT_JOIN)
                   ->getResults();

DELETE

DELETE FROM table WHERE id=4;

$database->query('table')
         ->delete()
         ->where('id=:id')
         ->setParameters([
            ':id' => 4
         ])
         ->execute();

UPDATE

UPDATE table SET name='Doe' WHERE lastname='John';

$database->query('table')
         ->update('name=:name')
         ->where('lastname=:lastname')
         ->setParameters([
             ':name'     => 'Doe',
             ':lastname' => 'John'
         ])
         ->execute();

INSERT INTO

INSERT INTO table (name,lastname) VALUES (:name1,:lastname1), (:name2,:lastname2);

$database->query('table')
         ->insertInto('name,lastname', ':name1,:lastname1', ':name2,:lastname2')
         ->setParameters([
            ':name1'     => 'Doe',
            ':lastname1' => 'John',
            ':name2'     => 'Doe',
            ':lastname2' => 'Jane'
         ])
         ->execute();

INSERT INTO table (name,lastname) VALUES (:name,:lastname) ON DUPLICATE KEY UPDATE name=:name, lastname=:lastname;

$database->query('table')
         ->insertInto('name,lastname', ':name,:lastname')
         ->onDuplicateKeyUpdate('name=:name,lastname=:lastname')
         ->setParameters([
            ':name'     => 'Doe',
            ':lastname' => 'John'
         ])
         ->execute();

SUB REQUEST

SELECT * FROM (SELECT * FROM table) AS test;

use NeutronStars\Database\QueryBuilder;

$builder = (new QueryBuilder('table'))->select('*');
$results = $database->withQuery($builder, 'test')
                    ->getResults();

SELECT * FROM users u LEFT JOIN (SELECT * from test) AS t ON t.id=u.id WHERE u.points>5;

use NeutronStars\Database\QueryBuilder;
use NeutronStars\Database\Query;

$builder = (new QueryBuilder('test'))->select('*');
$results = $database->query('users u')
                    ->select('*')
                    ->joinQuery($builder, 't', 't.id=u.id', Query::LEFT_JOIN)
                    ->where('u.points>:points')
                    ->setParameters([
                       ':points'     => 5
                    ])
                    ->getResults();

UNION

SELECT u.* FROM users u UNION ALL SELECT p.* FROM profile p;

$results = $database->query('users')
                   ->select('u.*')
                   ->union('profile p', true)
                   ->select('p.*')
                   ->getResults();