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;
}
