public static function getAssociations(ezcDbHandler $db, $url)
{
$options = new ezcAuthenticationOpenidDbStoreOptions();
$table = $options->tableAssociations;
$query = new ezcQuerySelect($db);
$e = $query->expr;
$query->select('*')->from($db->quoteIdentifier($table['name']))->where($e->eq($db->quoteIdentifier($table['fields']['url']), $query->bindValue($url)));
$query = $query->prepare();
$query->execute();
$rows = $query->fetchAll();
if (count($rows) > 0) {
$rows = $rows[0];
$data = $rows[$table['fields']['association']];
return $data;
}
}
/**
* Adds all columns to be selected to $q.
*
* Adds the columns from all tables defined in $relations to the select
* query $q. Columns are selected using an alias to identify which relation
* they belong too, created using the tables alias. In addition, aliases
* are created in the query $q, for usage ease.
*
* @param ezcQuerySelect $q
* @param array(ezcPersistentRelationFindDefinition) $relations
*/
protected function createSelects(ezcQuerySelect $q, array $relations)
{
foreach ($relations as $tableAlias => $relation) {
$q->select($q->alias($this->getColumnName($tableAlias, $relation->definition->idProperty->columnName), $this->getColumnAlias($tableAlias, $relation->definition->idProperty->propertyName)));
$this->registerAlias($this->getColumnName($tableAlias, $relation->definition->idProperty->columnName), $this->getColumnAlias($tableAlias, $relation->definition->idProperty->propertyName, false));
foreach ($relation->definition->properties as $property) {
$q->select($q->alias($this->getColumnName($tableAlias, $property->columnName), $this->getColumnAlias($tableAlias, $property->propertyName)));
$this->registerAlias($this->getColumnName($tableAlias, $property->columnName), $this->getColumnAlias($tableAlias, $property->propertyName, false));
}
if ($relation->furtherRelations !== array()) {
$this->createSelects($q, $relation->furtherRelations);
}
}
}
/**
* Returns the unserialized association linked to the OpenID provider URL.
*
* Returns false if the association could not be retrieved or if it expired.
*
* @param string $url The URL of the OpenID provider
* @return ezcAuthenticationOpenidAssociation
*/
public function getAssociation($url)
{
$table = $this->options->tableAssociations;
$query = new ezcQuerySelect($this->instance);
$e = $query->expr;
$query->select('*')->from($this->instance->quoteIdentifier($table['name']))->where($e->eq($this->instance->quoteIdentifier($table['fields']['url']), $query->bindValue($url)));
$query = $query->prepare();
$query->execute();
$rows = $query->fetchAll();
if (count($rows) > 0) {
$rows = $rows[0];
$data = unserialize($rows[$table['fields']['association']]);
return $data;
}
// no association was found for $url
return false;
}
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']);
}
<?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;
}
