PHP code example of liftkit / database

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

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

    

liftkit / database example snippets

use LiftKit\Database\Connection\MySql;
use LiftKit\DependencyInjection\Container\Container;
use LiftKit\Database\Cache\Cache;
use PDO;

$connection = new MySql(
  new Container,
	new Cache,
	new PDO('connectionString', 'username', 'password')
);

$results = $connection->query(
  "
    SELECT *
    FROM tbl
  "
);

$connection->query(
  "
    SELECT *
    FROM tbl
    WHERE col1 = ?
      AND col2 = ?
  ",
  [
    'val1',
    'val2',
  ]
);

// NOTE: 
// Results are not loaded into memory. Instead they are
// wrapped by an object of the class
// \LiftKit\Database\Result\Result

$results = $connection->query(
	"
		SELECT *
		FROM tbl
	"
);

foreach ($results as $row) {
  echo 'column "name" = ' . $row['name'] . PHP_EOL;
  echo 'column "id" = ' . $row['id'] . PHP_EOL;
}

foreach ($results->fetchColumn('id') as $id) {
	echo $id . PHP_EOL;
}
// '1'
// '2'
// ...

foreach ($results->fetchAll() as $row) {
	// Do something with $row['column']
}

foreach ($results->flatten() as $row) {
	// Do something with $row['column']
}

use LiftKit\Database\Query\Query;

/**
 * @var Query $query
 */
$query = $connection->createQuery();

// SELECT field1, field2
// FROM tbl
// WHERE field1 = 'val1'

$results = $query->select('field1', 'field2')
  ->from('tbl')
  ->whereEqual('field1', 'val1')
  ->execute();

use LiftKit\Database\Query\Condition\Condition;

// SELECT `field1`, `field2`
// FROM `tbl`
// LEFT JOIN `other_tbl` ON (
//  `tbl`.`field1` = `other_tbl`.`field1`
//  OR `tbl`.`field2` > `other_tbl`.field2`
// )
// WHERE `tbl`.`field1` = 'val1'
//    OR `other_tbl`.`field2` = 'val2'
// GROUP BY `tbl`.`field3`, `tbl`.`field4`
// HAVING `tbl`.`field1` < 1
// ORDER BY `tbl`.`field5` ASC, `tbl`.`field6` DESC

$results = $query->select('field1', 'field2')
  ->from('tbl')
  ->leftJoin(
    'other_tbl',
    $connection->createCondition()
    	->equal(
     		'tbl.field1',
     		$connection->quoteIdentifier('other_tbl.field1')
    	)
    	->orGreaterThan(
     		'tbl.field2',
     		$connection->quoteIdentifier('other_tbl.field2')
    	)
  )
  ->whereEqual('tbl1.field1', 'val1')
  ->orWhereEqual('other_tbl.field2', 'val2')
  ->groupBy('tbl.field3')
  ->groupBy('tbl.field4')
  ->havingLessThan('tbl.field1', 1)
  ->orderBy('tbl.field5', Query::ORDER_ASC)
  ->orderBy('tbl.field6', Query::ORDER_DESC)
  ->execute();

// UPDATE tbl
// SET field2 = 'val2', field3 = 'val3'
// WHERE tbl.id = 2

$query->update()
  ->table('tbl')
  ->set(
    [
      'field2' => 'val2',
      'field3' => 'val3',
    ]
  )
  ->whereEqual('tbl.id', 2)
  ->execute();

// INSERT INTO tbl
// SET field2 = 'val2', field3 = 'val3'

$id = $query->insert()
  ->into('tbl')
  ->set(
    [
      'field2' => 'val2',
      'field3' => 'val3',
    ]
  )
  ->execute();

// DELETE tbl.*
// FROM tbl
// WHERE id = 1

$query->delete()
  ->from('tbl')
  ->whereEqual('id', 1)
  ->execute();

// SELECT *
// FROM tbl1
// WHERE
// ( SELECT COUNT(*)
//   FROM tbl2
//   WHERE tbl1.id = tbl2.tbl1_id
// ) = 1

$results = $query->select('*')
  ->from('tbl1')
  ->whereEqual(
    $connection->createQuery()
      ->select($connection->createRaw('COUNT(*)'))
      ->from('tbl2')
      ->whereEqual(
      	'tbl1.id', 
      	$connection->quoteIdentifier('tb2.tbl1_id')
      ),
    1
  )
  ->execute();

function getAllTblRows ()
{
  return $connection->createQuery()
  	->select('*')
    ->from('tbl')
    ->execute();
}

// SELECT *
// FROM tbl

$results = getActiveTblRows();

function getAllTblRows (Query $inputQuery = null)
{
  return $connection->createQuery()
  	->select('*')
    ->from('tbl')
    ->composeWith($inputQuery)
    ->execute();
}

function getActiveTblRows ()
{
  $query = $connection->createQuery()
  	->whereEqual('active', 1);
  
  return getAllTblRows($query);
}

// SELECT *
// FROM tbl
// WHERE active = 1

$results = getActiveTblRows();

use LiftKit\Database\Schema\Schema;
use LiftKit\Database\Schema\Table\Table;

// We'll get back to schemas in a moment

$table = new Table(
	$connection,
	new Schema($connection),
	'tbl'
);

// SELECT *
// FROM tbl

$results = $tbl->getRows();

// SELECT *
// FROM tbl
// WHERE active = 1

$results = $table->getRows(
	$connection->createQuery()
		->whereEqual('active', 1)
);

// SELECT *
// FROM tbl
// WHERE id = 1
// LIMIT 1

$row = $table->getRow(1);

// 'val1'
echo $row['field1'];

// 'val2'
echo $row['field2'];

// INSERT INTO tbl
// SET field1 = 'val1', field2 = 'val2'

$id = $table->insertRow(
	[
		'field1' => 'val1',
		'field2' => 'val2',
	]
);

// UPDATE tbl
// SET field1 = 'val1', field2 = 'val2'
// WHERE id = 1

$table->updateRow(
	[
		'id'     => 1,
		'field1' => 'val1',
		'field2' => 'val2',
	]
);

// DELETE FROM tbl
// WHERE id = 1

$table->deleteRow(1);