/**
* Method to load a value from cache.
*
* @param string $id
* @param string $time
* @return mixed
*/
public function load($id, $time)
{
$value = false;
// Retrieve the value.
$this->sqlite->select()->where()->equalTo('id', ':id');
$this->sqlite->adapter()->prepare($this->sqlite->render(true))->bindParams(array('id' => sha1($id)))->execute();
$rows = $this->sqlite->adapter()->fetchResult();
// If the value is found, check expiration and return.
if (count($rows) > 0) {
$data = $rows[0]['value'];
$dbTime = $rows[0]['time'];
if (time() - $dbTime <= $time) {
$value = unserialize($data);
}
}
return $value;
}
/**
* 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);
}
<?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;
}
<?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 testSelectDistinct()
{
$s = new Sql(Db::factory('Sqlite', array('database' => __DIR__ . '/../tmp/test.sqlite')), 'users');
$s->select('username')->distinct();
$this->assertEquals('SELECT DISTINCT "username" FROM "users"', $s->render(true));
}
