static function execOptionsQuery(ezcQuerySelect $query, $keyName, $valueName, array $options = array()) { $defaultOptions = array('skip_empty' => false, 'allow_empty' => false, 'empty_text' => _('-- Selezionare --')); $opt = array_merge($defaultOptions, $options); $stmt = $query->prepare(); $stmt->execute(); $optionsList = array(); if ($opt['allow_empty']) { $optionsList[''] = $opt['empty_text']; } while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { if ($opt['skip_empty'] !== true || trim($row[$valueName]) != '') { $optionsList[$row[$keyName]] = $row[$valueName]; } } return $optionsList; }
/** * Handles preparing query. * * Overrides ezcQuery->prepare() * * Adds "FROM dual" to the select if no FROM clause specified * i.e. fixes queries like "SELECT 1+1" to work in Oracle. * * @return PDOStatement */ public function prepare() { if ($this->fromString == null || $this->fromString == '') { $this->from($this->getDummyTableName()); } return parent::prepare(); }
public function testSubSubSelect() { $name = 'IBM'; $name2 = 'company'; $q = new ezcQuerySelect(ezcDbInstance::get()); $q->expr->setValuesQuoting(false); // subselect $q2 = $q->subSelect(); // sub subselect $q3 = $q2->subSelect(); $q3->expr->setValuesQuoting(false); $q3->select('*')->from('query_test2')->where($q3->expr->in('company', 'IBM', 'eZ systems')); // bind values $q2->select('company')->from('query_test')->where($q2->expr->eq('company', $q2->bindParam($name)), ' id > 2 '); $q->select('*')->from('query_test')->where(' id >= 1 ', $q->expr->in('company', $q2->getQuery()))->orderBy('id'); $stmt = $q->prepare(); $stmt->execute(); $result = $stmt->fetchAll(); $this->assertEquals('IBM', $result[0]['company']); $this->assertEquals('Norway', $result[0]['section']); $this->assertEquals('IBM', $result[1]['company']); $this->assertEquals('Germany', $result[1]['section']); }
/** * Helper for {@see listVersions()} and {@see listVersionsForUser()} that filters duplicates * that are the result of the cartesian product performed by createVersionInfoFindQuery() * * @param \ezcQuerySelect $query * @return string[][] */ private function listVersionsHelper($query) { $query->orderBy($this->dbHandler->quoteColumn('id', 'ezcontentobject_version')); $statement = $query->prepare(); $statement->execute(); $results = array(); $previousId = null; foreach ($statement->fetchAll(\PDO::FETCH_ASSOC) as $row) { if ($row["ezcontentobject_version_id"] == $previousId) { continue; } $previousId = $row["ezcontentobject_version_id"]; $results[] = $row; } return $results; }
<?php require_once __DIR__ . '/tutorial_example_01.php'; $name = 'IBM'; $q = new ezcQuerySelect(ezcDbInstance::get()); // Creating subselect object $q2 = $q->subSelect(); // $q2 will build the subquery "SELECT company FROM query_test WHERE // company = :ezcValue1 AND id > 2". This query will be used inside the SQL for // $q. $q2->select('company')->from('query_test')->where($q2->expr->eq('company', $q2->bindParam($name)), 'id > 2'); // $q the resulting query. It produces the following SQL: // SELECT * FROM query_test // WHERE id >= 1 AND // company IN ( ( // SELECT company FROM query_test // WHERE company = :ezcValue1 AND id > 2 // ) ) $q->select('*')->from('query_test')->where(' id >= 1 ', $q->expr->in('company', $q2)); $stmt = $q->prepare(); echo $stmt->queryString; //$stmt->execute();
/** * Runs the filter and returns a status code when finished. * * @param ezcAuthenticationPasswordCredentials $credentials Authentication credentials * @return int */ public function run($credentials) { $db = $this->database; // see if username exists $query = new ezcQuerySelect($db->instance); $e = $query->expr; $query->select('COUNT( ' . $db->instance->quoteIdentifier($db->fields[0]) . ' )')->from($db->instance->quoteIdentifier($db->table))->where($e->eq($db->instance->quoteIdentifier($db->fields[0]), $query->bindValue($credentials->id))); $rows = $query->prepare(); $rows->execute(); $count = (int) $rows->fetchColumn(0); if ($count === 0) { return self::STATUS_USERNAME_INCORRECT; } $rows->closeCursor(); if (count($this->requestedData) > 0) { // fetch extra data from the database $query = new ezcQuerySelect($db->instance); $e = $query->expr; $params = array(); foreach ($this->requestedData as $param) { $params[] = $db->instance->quoteIdentifier($param); } $query->select(implode(', ', $params))->from($db->instance->quoteIdentifier($db->table))->where($e->lAnd($e->eq($db->instance->quoteIdentifier($db->fields[0]), $query->bindValue($credentials->id)))); $rows = $query->prepare(); $rows->execute(); $data = $rows->fetchAll(); $data = $data[0]; foreach ($this->requestedData as $attribute) { $this->data[$attribute] = array($data[$attribute]); } } return self::STATUS_OK; }