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