/**
* Set the ORDER BY value
*
* @param mixed $by
* @param string $order
* @return AbstractSql
*/
public function orderBy($by, $order = 'ASC')
{
$byColumns = null;
if (is_array($by)) {
$quotedAry = [];
foreach ($by as $value) {
$quotedAry[] = $this->sql->quoteId(trim($value));
}
$byColumns = implode(', ', $quotedAry);
} else {
if (strpos($by, ',') !== false) {
$ary = explode(',', $by);
$quotedAry = [];
foreach ($ary as $value) {
$quotedAry[] = $this->sql->quoteId(trim($value));
}
$byColumns = implode(', ', $quotedAry);
} else {
$byColumns = $this->sql->quoteId(trim($by));
}
}
$this->orderBy .= (null !== $this->orderBy ? ', ' : '') . $byColumns;
$order = strtoupper($order);
if (strpos($order, 'RAND') !== false) {
$this->orderBy = $this->sql->getDbType() == \Pop\Db\Sql::SQLITE ? ' RANDOM()' : ' RAND()';
} else {
if ($order == 'ASC' || $order == 'DESC') {
$this->orderBy .= ' ' . $order;
}
}
return $this;
}
/**
* Create table in databse
*
* @return void
*/
protected function createTable()
{
if (file_exists(__DIR__ . '/Sql/' . strtolower($this->sql->getDbType()) . '.sql')) {
$sql = str_replace('[{table}]', $this->sql->getTable(), file_get_contents(__DIR__ . '/Sql/' . strtolower($this->sql->getDbType()) . '.sql'));
$queries = explode(';', $sql);
foreach ($queries as $query) {
if (!empty($query) && $query != '') {
$this->sql->db()->query($query);
}
}
}
}
/**
* Get table info
*
* @return array
*/
public function getTableInfo()
{
$info = ['tableName' => $this->table, 'primaryId' => $this->primaryKeys, 'columns' => []];
$sql = null;
$field = 'column_name';
$type = 'data_type';
$nullField = 'is_nullable';
switch ($this->sql->getDbType()) {
case \Pop\Db\Sql::PGSQL:
$sql = 'SELECT * FROM information_schema.COLUMNS WHERE table_name = \'' . $this->table . '\' ORDER BY ordinal_position ASC';
break;
case \Pop\Db\Sql::SQLSRV:
$sql = 'SELECT c.name \'column_name\', t.Name \'data_type\', c.is_nullable, c.column_id FROM sys.columns c INNER JOIN sys.types t ON c.system_type_id = t.system_type_id WHERE object_id = OBJECT_ID(\'' . $this->table . '\') ORDER BY c.column_id ASC';
break;
case \Pop\Db\Sql::SQLITE:
$sql = 'PRAGMA table_info(\'' . $this->table . '\')';
$field = 'name';
$type = 'type';
$nullField = 'notnull';
break;
case \Pop\Db\Sql::ORACLE:
$sql = 'SELECT column_name, data_type, nullable FROM all_tab_cols where table_name = \'' . $this->table . '\'';
$field = 'COLUMN_NAME';
$type = 'DATA_TYPE';
$nullField = 'NULLABLE';
break;
default:
$sql = 'SHOW COLUMNS FROM `' . $this->table . '`';
$field = 'Field';
$type = 'Type';
$nullField = 'Null';
}
$this->sql->db()->query($sql);
while (($row = $this->sql->db()->fetch()) != false) {
switch ($this->sql->getDbType()) {
case \Pop\Db\Sql::SQLITE:
$nullResult = $row[$nullField] ? false : true;
break;
case \Pop\Db\Sql::MYSQL:
$nullResult = strtoupper($row[$nullField]) != 'NO' ? true : false;
break;
case \Pop\Db\Sql::ORACLE:
$nullResult = strtoupper($row[$nullField]) != 'Y' ? true : false;
break;
default:
$nullResult = $row[$nullField];
}
$info['columns'][$row[$field]] = ['type' => $row[$type], 'null' => $nullResult];
}
return $info;
}
/**
* Get single model object with dynamic field values using a table join
*
* @param string $table
* @param string $model
* @param int $modelId
* @param array $filters
* @return mixed
*/
public static function getModelObjectFromTable($table, $model, $modelId, array $filters = [])
{
$sql = Table\Fields::sql();
$sql->select()->where('models LIKE :models');
$value = $sql->getDbType() == \Pop\Db\Sql::SQLITE ? '%' . $model . '%' : '%' . addslashes($model) . '%';
$fields = Table\Fields::execute((string) $sql, ['models' => $value], Record::ROW_AS_ARRAYOBJECT);
$encrypted = [];
$multiples = [];
if ($fields->hasRows()) {
$sql = new Sql($sql->db(), $table);
$select = [$table . '.*'];
$where = [];
foreach ($fields->rows() as $field) {
$select[$field->name] = DB_PREFIX . 'field_' . $field->name . '.value';
}
$sql->select($select);
foreach ($fields->rows() as $field) {
if ($field->encrypt) {
$encrypted[$field->id] = $field->name;
}
if ($field->type != 'textarea-history' && ($field->dynamic || $field->type == 'checkbox' || $field->type == 'select' && strpos($field->attributes, 'multiple') !== false)) {
$multiples[$field->id] = $field->name;
}
$sql->select()->join(DB_PREFIX . 'field_' . $field->name, [$table . '.id' => DB_PREFIX . 'field_' . $field->name . '.model_id']);
}
$sql->select()->where($table . '.id = :id');
if (count($where) > 0) {
foreach ($where as $w) {
$sql->select()->where($w);
}
}
$record = new Record();
$record->setPrefix(DB_PREFIX)->setPrimaryKeys(['id'])->setTable($table);
$record->executeStatement($sql, [$table . '.id' => $modelId], Record::ROW_AS_ARRAYOBJECT);
$values = $record->getColumns();
foreach ($values as $key => $value) {
foreach ($filters as $filter => $params) {
if (null !== $params && count($params) > 0) {
$params = array_merge([$value], $params);
} else {
$params = [$value];
}
$value = call_user_func_array($filter, $params);
}
if (in_array($key, $encrypted)) {
$values[$key] = self::parse((new Mcrypt())->decrypt($value));
} else {
$values[$key] = self::parse($value);
}
}
if (count($multiples) > 0) {
foreach ($multiples as $id => $name) {
$fv = new Record();
$fv->setPrefix(DB_PREFIX)->setPrimaryKeys(['id'])->setTable('field_' . $name);
$fv->findRecordsBy(['model_id' => $modelId, 'model' => $model], null, Record::ROW_AS_ARRAYOBJECT);
if ($fv->hasRows()) {
$values[$name] = [];
foreach ($fv->rows() as $f) {
$values[$name][] = self::parse(in_array($id, $encrypted) ? (new Mcrypt())->decrypt($f->value) : $f->value);
}
}
}
}
} else {
$sql = new Sql($sql->db(), $table);
$sql->select([$table . '.*'])->where($table . '.id = :id');
$record = new Record();
$record->setPrefix(DB_PREFIX)->setPrimaryKeys(['id'])->setTable($table);
$record->executeStatement($sql, [$table . '.id' => $modelId], Record::ROW_AS_ARRAYOBJECT);
$values = $record->getColumns();
}
$values['id'] = $modelId;
return new $model($values);
}
/**
* Predicate render method
*
* @param int $count
* @return string
*/
public function render($count = 1)
{
$where = null;
// Build any nested predicates
//if (null !== $this->nested) {
// $where = '(' . $this->nested . ')';
//}
if (count($this->nested) > 0) {
$where = '(' . implode(') AND (', $this->nested) . ')';
}
// Loop through and format the predicates
if (count($this->predicates) > 0) {
if (null !== $where) {
$where .= ' ' . $this->predicates[0]['combine'] . ' ';
}
$paramCount = $count;
$dbType = $this->sql->getDbType();
foreach ($this->predicates as $key => $predicate) {
$format = $predicate['format'];
$curWhere = '(';
for ($i = 0; $i < count($predicate['values']); $i++) {
if ($i == 0) {
$format = str_replace('%1', $this->sql->quoteId($predicate['values'][$i]), $format);
} else {
if (is_array($predicate['values'][$i])) {
$vals = $predicate['values'][$i];
foreach ($vals as $k => $v) {
$predValue = strpos($predicate['values'][0], '.') !== false ? substr($predicate['values'][0], strpos($predicate['values'][0], '.') + 1) : $predicate['values'][0];
// Check for named parameters
if (':' . $predValue == substr($v, 0, strlen(':' . $predValue)) && $dbType !== \Pop\Db\Sql::SQLITE && $dbType !== \Pop\Db\Sql::ORACLE) {
if ($dbType == \Pop\Db\Sql::MYSQL || $dbType == \Pop\Db\Sql::SQLSRV) {
$v = '?';
} else {
if ($dbType == \Pop\Db\Sql::PGSQL && !$this->sql->getDb()->isPdo()) {
$v = '$' . $paramCount;
$paramCount++;
}
}
}
$vals[$k] = null === $v ? 'NULL' : $this->sql->quote($v);
}
$format = str_replace('%' . ($i + 1), implode(', ', $vals), $format);
} else {
if ($predicate['values'][$i] instanceof \Pop\Db\Sql) {
$val = (string) $predicate['values'][$i];
} else {
$val = null === $predicate['values'][$i] ? 'NULL' : $this->sql->quote($predicate['values'][$i]);
}
$predValue = strpos($predicate['values'][0], '.') !== false ? substr($predicate['values'][0], strpos($predicate['values'][0], '.') + 1) : $predicate['values'][0];
// Check for named parameters
if (':' . $predValue == substr($val, 0, strlen(':' . $predValue)) && $dbType !== \Pop\Db\Sql::SQLITE && $dbType !== \Pop\Db\Sql::ORACLE) {
if ($dbType == \Pop\Db\Sql::MYSQL || $dbType == \Pop\Db\Sql::SQLSRV) {
$val = '?';
} else {
if ($dbType == \Pop\Db\Sql::PGSQL && !$this->sql->getDb()->isPdo()) {
$val = '$' . $paramCount;
$paramCount++;
}
}
}
$format = str_replace('%' . ($i + 1), $val, $format);
}
}
}
$curWhere .= $format . ')';
if ($key == 0) {
$where .= $curWhere;
} else {
$where .= ' ' . $predicate['combine'] . ' ' . $curWhere;
}
}
}
return $where;
}
<?php
require_once '../../bootstrap.php';
use Pop\Db\Db;
use Pop\Db\Sql;
try {
// Define DB credentials
$creds = array('database' => 'helloworld', 'host' => 'localhost', 'username' => 'hello', 'password' => '12world34');
$db = Db::factory('Mysqli', $creds);
// Create a non-prepared statement, escaping the value
$sql = new Sql($db, 'users');
$sql->select()->where()->greaterThanOrEqualTo('id', $db->adapter()->escape(5));
$sql->select()->limit(4)->offset(1);
echo $sql . '<br />' . PHP_EOL;
$db->adapter()->query($sql);
while ($row = $db->adapter()->fetch()) {
print_r($row);
}
} catch (\Exception $e) {
echo $e->getMessage() . PHP_EOL . PHP_EOL;
}
public function testIsNotNull()
{
$p = new Predicate(Sql::factory(Db::factory('Sqlite', array('database' => __DIR__ . '/../tmp/test.sqlite')), 'users'));
$p->isNotNull('email');
$this->assertEquals('("email" IS NOT NULL)', (string) $p);
}
<?php
require_once '../../bootstrap.php';
use Pop\Db\Db;
use Pop\Db\Sql;
try {
// Define DB credentials
$creds = array('database' => 'helloworld', 'host' => 'localhost', 'username' => 'hello', 'password' => '12world34');
$db = Db::factory('Mysqli', $creds);
// Create a prepared statement
$sql = new Sql($db, 'users');
$sql->select()->where()->greaterThanOrEqualTo('id', '?');
$sql->select()->limit(4)->offset(1);
echo $sql . '<br />' . PHP_EOL;
// Prepare the statement, bind the parameters and execute
$db->adapter()->prepare($sql);
$db->adapter()->bindParams(array('id' => 5));
$db->adapter()->execute();
foreach ($db->adapter()->fetchResult() as $row) {
print_r($row);
}
} catch (\Exception $e) {
echo $e->getMessage() . PHP_EOL . PHP_EOL;
}
public function testDelete()
{
$s = new Sql(Db::factory('Sqlite', array('database' => __DIR__ . '/../tmp/test.sqlite')), 'users');
$s->delete()->orderBy('id')->limit(1);
$this->assertEquals('DELETE FROM "users" ORDER BY "id" ASC LIMIT 1', $s->render(true));
}
/**
* Method to clear all stored values from cache.
*
* @return void
*/
public function clear()
{
$this->sqlite->adapter()->query('DELETE FROM "' . $this->table . '"');
}
