/** * 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)); }