PHP code example of ramphor / sql

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

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

    

ramphor / sql example snippets

echo sql('SELECT * FROM @ WHERE @ = ? OR name IN ([?]) OR id IN ([]) AND created = @',
		'users', 'name', 'Trevor', ['Tom', 'Dick', 'Harry'], [1, 2, 3], 'NOW()');

echo sql('Hello @', 'World');

echo sql('Hello ?', 'World');

echo sql('SELECT ?, ?, ?, ?, @', 1, "2", null, 'Hello World', 'NOW()');

echo sql('?, ?, ?, ?, ?, ?, ?', 4, '5', "Trevor's", 'NOW()', true, false, null);

echo sql('@, @, @, @, @, @, @', 4, "5", "Trevor's", 'NOW()', true, false, null);

echo sql()->select('u.id', 'u.name', 'a.*')
          ->from('users u')
            ->leftJoin('accounts a ON a.user_id = u.id AND a.overdraft >= ?', 5000)
          ->where('a.account = ? OR u.name = ? OR a.id IN ([])', 'BST002', 'foobar', [1, 2, 3])
          ->orderBy('u.name DESC')
	  ->limit(5, 10);

echo sql('WHERE id IN ([])', [1, 2, 3]);

echo sql('WHERE name IN ([?])', ['joe', 'john', 'james']);

echo sql('WHERE id = :id OR name = :name OR dob = :dob:raw', ['id' => 5, 'name' => 'Trevor', 'dob' => 'NOW()']);

echo sql('WHERE id IN (1..?) OR id IN (?..?)', 3, 6, 8);

echo sql('SET description = %s:pack:trim:crop:20', "Hello     World's   Greatest");

class Sql
{
	function select(...$cols)
	{
		$this->sql .= 'SELECT ' . implode(', ', $cols);
    		return $this;
	}
	function from(...$tables)
	{
		$this->sql .= PHP_EOL . 'FROM ' . implode(', ', $tables);
		return $this;
	}
	function leftJoin($stmt, ...$params)
	{
		return $this->prepare(PHP_EOL . 'LEFT JOIN ', ...$params);
	}
	function where($stmt, ...$params)
	{
		return $this->prepare(PHP_EOL . 'WHERE ', ...$params);
	}
	function prepare($stmt, ...$params)
	{
		//	the magic happens here
		//	processing `?`, `@`, escaping, quoting etc.
	}
}

echo sql()->select('*')
          ->from('users u')
	    ->leftJoin('accounts a ON a.user_id = u.id')
	  ->where('u.id = ?', 5);

use Ramphor;

$sql = new sql();
$sql = new Sql();
$sql = new SQL();

// or

$sql = new \Ramphor\Sql();

function sql($stmt = null, ...$params)
{
	return new Ramphor\Sql($stmt, ...$params);
}

$sql = sql();
$sql = Sql();
$sql = SQL();

->select('col1', 'col2', 'col3')
->from('table t')
->join('table2 t2 ON ... = ?', $var)
->leftJoin('table3 t3 ON ... = ?', $var)
->where('foo = ?', 'bar')
->groupBy('t.col1', 't2.col2')
->orderBy('t.col1 DESC')
->limit(5, 10);

// other common functions

->selectDistinct(..)
->insert(..)
->insertInto(..)
->values(..)
->set(..)
->delete(..)
->deleteFrom(..)
->having(..)
->union(..)

->select('col1', 'col2', 'col3')
->from('table t')
->join('table2 t2 ON ... = ?', $var)
->left_join('table3 t3 ON ... = ?', $var)
->where('foo = ?', 'bar')
->group_by('t.col1', 't2.col2')
->order_by('t.col1 DESC')
->limit(5, 10);

// other common functions

->select_distinct(..)
->insert(..)
->insert_into(..)
->values(..)
->set(..)
->delete(..)
->delete_from(..)
->having(..)
->union(..)

->SELECT('col1', 'col2', 'col3')
->FROM('table t')
->JOIN('table2 t2 ON ... = ?', $var)
->LEFT_JOIN('table3 t3 ON ... = ?', $var)
->WHERE('foo = ?', 'bar')
->GROUP_BY('t.col1', 't2.col2')
->ORDER_BY('t.col1 DESC')
->LIMIT(5, 10);

// other common functions

->SELECT_DISTINCT(..)
->INSERT(..)
->INSERT_INTO(..)
->VALUES(..)
->SET(..)
->DELETE(..)
->DELETE_FROM(..)
->HAVING(..)
->UNION(..)

->s('col1', 'col2', 'col3')		//	s  = SELECT
->f('table t')				//	f  = FROM
->j('table2 t2 ON ... = ?', $var)	//	j  = JOIN
->lj('table3 t3 ON ...?', $var)         //	lj = LEFT JOIN
->w('foo = ?', 'bar')			//	w  = WHERE
->gb('t.col1', 't2.col2')		//	gb = GROUP BY
->ob('t.col1 DESC')			//	ob = ORDER BY
->l(5, 10);				//	l  = LIMIT

// other common functions

->sd(..)				//	sd = SELECT DISTINCT
->i(..)					//	i  = INSERT
->ii(..)				//	ii = INSERT INTO
->v(..)					//	v  = VALUES
->d(..)					//	d  = DELETE
->df(..)				//	df = DELETE FROM
->h(..)					//	h  = HAVING

\Ramphor\Sql::setConnection($conn);

$array = sql('SELECT ...')->fetchAll();

function ($sql) use ($conn)
{
	$recset = $conn->query($sql);
	if ( ! $recset) {
		throw new \Exception('PSO::query() error: ' . $conn->errorInfo()[2]);
	}
	$result = $recset->fetchAll(\PDO::FETCH_ASSOC);
	$recset->closeCursor();
	return $result;
};

$value = sql('SELECT 999')->lookup();
echo $value;

999

$data = sql('SELECT 1 AS id, "Trevor" AS name')->lookup();
var_dump($data);

array(2) {
  ["id"]   => string(1) "1"
  ["name"] => string(6) "Trevor"
}

$recset = sql(...)->query();

$affected_rows = sql('DELETE FROM ...')->exec();

json
/* composer.json */
	"hor/sql": "*"
	}

PHP 5.6+ (for ...$args syntax)
Multibyte mb_* extension