Prepares and executes an SQL query and returns the first row of the result
as an associative array.
public static function fetchByRoot(Connection $conn, $id, MembershipEntityInterface $root) { if ($root instanceof Area) { $data = $conn->fetchAssoc('SELECT * FROM `' . EdkTables::MESSAGE_TBL . '` WHERE `id` = :id AND `areaId` = :rootId', [':id' => $id, ':rootId' => $root->getId()]); } elseif ($root instanceof Group) { $data = $conn->fetchAssoc('SELECT m.* FROM `' . EdkTables::MESSAGE_TBL . '` m ' . 'INNER JOIN `' . CoreTables::AREA_TBL . '` a ON a.`id` = m.`areaId` ' . 'WHERE m.`id` = :id AND a.`groupId` = :rootId', [':id' => $id, ':rootId' => $root->getId()]); } elseif ($root instanceof Project) { $data = $conn->fetchAssoc('SELECT m.* FROM `' . EdkTables::MESSAGE_TBL . '` m ' . 'INNER JOIN `' . CoreTables::AREA_TBL . '` a ON a.`id` = m.`areaId` ' . 'WHERE m.`id` = :id AND a.`projectId` = :rootId', [':id' => $id, ':rootId' => $root->getId()]); } if (false === $data) { return false; } $item = self::fromArray($data); if ($root instanceof Area) { $item->area = $root; } elseif ($root instanceof Group) { $item->area = Area::fetchByGroup($conn, $data['areaId'], $root); } elseif ($root instanceof Project) { $item->area = Area::fetchByProject($conn, $data['areaId'], $root); } if (!empty($data['responderId'])) { $item->responder = User::fetchByCriteria($conn, QueryClause::clause('u.id = :id', ':id', $data['responderId'])); } return $item; }
/** * Dispatches magic methods (findBy[Property]()) * * @param string $methodName The name of the magic method * @param string $arguments The arguments of the magic method * @throws \Exception * @return mixed * @api */ public function __call($methodName, $arguments) { if (substr($methodName, 0, 6) === 'findBy' && strlen($methodName) > 7) { $propertyName = lcfirst(substr($methodName, 6)); $sql = "select * from " . $this->tableName . " where " . $propertyName . " = :propertyValue order by id desc"; $result = $this->db->fetchAll($sql, array('propertyValue' => $arguments[0])); // Convert query result to an array of objects $objectList = array(); foreach ($result as $row) { $id = $row['id']; $objectList[$id] = $this->buildDomainObject($row); } return $objectList; } elseif (substr($methodName, 0, 9) === 'findOneBy' && strlen($methodName) > 10) { $propertyName = lcfirst(substr($methodName, 9)); $sql = "select * from " . $this->tableName . " where id = :id"; $row = $this->db->fetchAssoc($sql, array('id' => $arguments[0])); if ($row) { return $this->buildDomainObject($row); } else { throw new \Exception("No " . $this->objectName . " matching " . $propertyName . " " . $arguments[0]); } } elseif (substr($methodName, 0, 7) === 'countBy' && strlen($methodName) > 8) { $propertyName = lcfirst(substr($methodName, 7)); $sql = "select COUNT(id) from " . $this->tableName . " where " . $propertyName . " = :propertyValue order by id desc"; $result = current($this->db->fetchAssoc($sql, array('propertyValue' => $arguments[0]))); return $result; } throw new \Exception('The method "' . $methodName . '" is not supported by the repository.', 1233180480); }
public function findOne($id) { $sql = "SELECT * FROM articles WHERE id = ?"; $result = $this->db->fetchAssoc($sql, array((int) $id)); $article = $this->buildArticle($result[0]); return $article; }
public function findByNameAndPassword($admin) { $name = $admin->getName(); $password = $admin->getPassword(); $adminData = $this->db->fetchAssoc('SELECT * FROM admin WHERE name = ? and password = ?', array($name, $password)); return $adminData ? $this->buildAdmin($adminData) : FALSE; }
public function count() { $qb = $this->dbal->createQueryBuilder(); $qb->select('count(*) c')->from('(' . $this->qb->getSQL() . ')', 'xxx'); $row = $this->dbal->fetchAssoc($qb->getSQL()); return $this->count = intval($row['c']); }
public function findByUriValid($uri) { $sql = "SELECT * FROM {$this->entityTable} WHERE uri = ? AND compromised=0"; $row = $this->db->fetchAssoc($sql, array((string) $uri)); if ($row) { return $this->createEntity($row); } }
public function find($id) { $record = $this->conn->fetchAssoc("SELECT id, message, author, parent FROM messages WHERE id = :id", [':id' => $id]); if (!$record) { throw new ObjectNotFoundException("Message not found for ID: {$id}"); } return $record; }
public function getCredentialChangeRequest($id, User $currentUser) { $data = $this->conn->fetchAssoc('SELECT * FROM `' . CoreTables::CREDENTIAL_CHANGE_TBL . '` WHERE `id` = :id', [':id' => $id]); if (empty($data) || $data['userId'] != $currentUser->getId()) { throw new ModelException('The specified credential change request does not exist.'); } return CredentialChangeRequest::fromArray($currentUser, $data); }
/** * @param string $workflowId * @return array|null */ public function getFlowchartConfig($workflowId) { $flowchartConfig = $this->connection->fetchAssoc("SELECT * FROM {$this->flowchartConfigTable} WHERE workflow_id = :id", ['id' => $workflowId]); if (!$flowchartConfig) { return null; } return ['workflow_id' => $flowchartConfig['workflow_id'], 'config' => json_decode($flowchartConfig['config']), 'last_updated_at' => $flowchartConfig['last_updated_at']]; }
public function findByUsername($username) { $record = $this->conn->fetchAssoc("SELECT id, username, age FROM users WHERE username = :username", [':username' => $username]); if (!$record) { throw new ObjectNotFoundException("User not found for name: {$username}"); } return $record; }
/** * @return string */ public function getVersion() { $result = $this->connection->fetchAssoc('SELECT value AS version FROM pref WHERE prop="version"'); if ($result !== false) { return $result['version']; } return VersionRepository::DEFAULT_VERSION; }
/** * @return string */ public function getVersion() { $result = $this->connection->fetchAssoc('SELECT version FROM sitepreff'); if ($result !== false) { return $result['version']; } return VersionRepository::DEFAULT_VERSION; }
/** * @param string $id * @return array|null */ public function find($id) { $handlerData = $this->connection->fetchAssoc('SELECT * FROM ' . Tables::MESSAGE_HANDLER . ' WHERE id = :id', ['id' => $id]); if (empty($handlerData)) { return null; } $this->fromDatabase($handlerData); return $handlerData; }
public function fetchVocabularyByName($name) { $vocabulary = $this->dbal->fetchAssoc('SELECT * FROM taxonomyVocabulary WHERE name = :name', ['name' => $name]); if ($vocabulary) { $vocabulary = $this->vocabularyHydrator->hydrate($vocabulary, new Vocabulary()); return $vocabulary; } throw new VocabularyNotFoundException('Could not find vocabulary ' . $name); }
/** * @param string $sku * @return Product * @throws QueryException */ public function getBySku(string $sku) : Product { $qb = $this->connection->createQueryBuilder(); $qb->select('*')->from('dumplie_inventory_product')->where('sku = :sku')->setParameter('sku', $sku); $productData = $this->connection->fetchAssoc($qb->getSQL(), $qb->getParameters()); if (empty($productData)) { throw QueryException::productNotFound($sku); } return new Product($productData['sku'], $productData['price_amount'] / $productData['price_precision'], $productData['price_currency'], (bool) $productData['is_in_stock'], $this->mao->getBy([Metadata::FIELD_SKU => $productData['sku']])); }
/** * @param SKU $sku * @return Product * @throws ProductNotFoundException */ public function getBySku(SKU $sku) : Product { $queryBuilder = $this->connection->createQueryBuilder(); $queryBuilder->select('sku', 'is_in_stock', 'price_amount', 'price_currency', 'price_precision')->from('dumplie_inventory_product')->where('sku = :sku')->setParameter('sku', (string) $sku); $result = $this->connection->fetchAssoc($queryBuilder->getSQL(), $queryBuilder->getParameters()); if (!$result) { throw ProductNotFoundException::bySku($sku); } return new Product(new SKU($result['sku']), new Price((int) $result['price_amount'], $result['price_currency'], (int) $result['price_precision']), (bool) $result['is_in_stock']); }
/** * @param string $sku * @param int $quantity * * @return CartItem * @throws QueryException */ private function getItemBySku(string $sku, int $quantity) : CartItem { $qb = $this->connection->createQueryBuilder(); $qb->select('*')->from('dumplie_inventory_product')->where('sku = :sku')->setParameter('sku', $sku); $itemData = $this->connection->fetchAssoc($qb->getSQL(), $qb->getParameters()); if (empty($itemData)) { throw QueryException::cartItemNotFound($sku); } return new CartItem($itemData['sku'], $quantity, $itemData['price_amount'] / $itemData['price_precision'], $itemData['price_currency'], $this->mao->getBy([Metadata::FIELD_SKU => $itemData['sku']])); }
/** * @return Project */ public function getItem($id) { $this->transaction->requestTransaction(); $data = $this->conn->fetchAssoc('SELECT * FROM `' . CoreTables::PROJECT_TBL . '` WHERE `id` = :id', [':id' => $id]); if (null === $data) { $this->transaction->requestRollback(); throw new ItemNotFoundException('The specified item has not been found.', $id); } return Project::fromArray($data); }
/** * {@inheritdoc} */ public function shouldBeRun() { $schemaManager = $this->connection->getSchemaManager(); if (!$schemaManager->tablesExist('tl_member')) { return false; } $sql = $this->connection->getDatabasePlatform()->getListTableIndexesSQL('tl_member', $this->connection->getDatabase()); $index = $this->connection->fetchAssoc($sql . " AND INDEX_NAME = 'username'"); return '0' !== $index['Non_Unique']; }
public function testDb1() { $result = self::$db->fetchAssoc('SELECT * FROM "user" LIMIT 1'); $this->assertArrayHasKey('user_id', $result); $q = self::$db->createQueryBuilder(); /** @var \Doctrine\DBAL\Driver\Statement $stmt */ $stmt = $q->select('*')->from('"user"', 't')->setMaxResults(50)->execute(); $result = $stmt->rowCount(); $this->assertEquals(50, $result); }
public function getCourseByIdentifier($id) { $sql = "SELECT * FROM vakken WHERE id=:id"; $params = ['id' => $id]; $data = $this->conn->fetchAssoc($sql, $params); if ($data) { return $this->hydrateCourse($data); } return null; }
public function fetchUserByGithubUid($uid) { $user = $this->dbal->fetchAssoc('SELECT * FROM users WHERE githubUid = :githubUid', ['githubUid' => $uid]); if ($user) { $user = $this->hydrator->hydrate($user, new User()); $this->in_memory_users[$user->getId()] = $user; return $user; } throw new UserNotFoundException('Could not find user with a UID of ' . $uid); }
/** * @param $id * @return Conversation */ public function findById($id) { if (array_key_exists($id, $this->in_memory_convos)) { return $this->in_memory_convos[$id]; } $convo_data = $this->dbal->fetchAssoc('SELECT * FROM conversations WHERE id = :conversation_id', ['conversation_id' => $id]); if (!$convo_data) { throw new ConversationNotFoundException(sprintf('Could not find conversation with ID of %s', $id)); } return $this->hydrateConversation($convo_data); }
/** * gets a user by its identifier * * @param $id * @return null|User */ public function getUserByIdentifier($id) { $sql = "SELECT * FROM users WHERE id=:id LIMIT 0,1"; $params = ['id' => $id]; $data = $this->conn->fetchAssoc($sql, $params); if ($data) { $user = $this->hydrateUser($data); return $user; } return null; }
/** * Authenticates the user from API * * @param array $phoneBook */ public function authenticate(array $responseData, $app) { $userData = $this->db->fetchAssoc('SELECT * FROM users WHERE username = ? and password = ?', array($responseData["username"], sha1($responseData["password"]))); if ($userData) { //$_session["username"] = $responseData['username']; $csrf = uniqid(); $app['session']->set('csrf', $csrf); return array("csrf" => $csrf); } else { return false; } }
/** * {@inheritdoc} */ public function get($id) { $query = sprintf('SELECT * FROM %s WHERE %s = ?', $this->connection->quoteIdentifier($this->tableName), $this->connection->quoteIdentifier(self::COLUMN_ID)); try { $row = $this->connection->fetchAssoc($query, [$id]); } catch (DBALException $e) { throw DatabaseException::fromDBALException($e); } if ($row === false) { return null; } return $this->rowToData($row); }
private function getMetadata($place) { $tpl = $this->getActualPart($place); $locale = $this->getLocalePart($place); if (!empty($this->cachedMetadata[$place])) { return $this->cachedMetadata[$place]; } $metadata = $this->conn->fetchAssoc('SELECT `id`, `subject`, `lastUpdate` FROM `' . CoreTables::MAIL_TBL . '` WHERE `place` = :place AND `locale` = :locale', [':place' => $tpl, ':locale' => $locale]); if (empty($metadata)) { throw new MailException('No such mail template: ' . $place . ' with locale ' . $this->locale); } return $this->cachedMetadata[$place] = $metadata; }
public function findOneById($id) { if (!is_int($id)) { throw new \InvalidArgumentException("l\\'id est sensé etre un integer. vous avez donné: " . $id, 500); } $result = $this->db->fetchAssoc("select * from where id = :id;", array(':id' => int_val($id))); if ($result == null || empty($result)) { return null; } $event = new GenericEvent($result); $event->setArgument("id", $id); $this->eventDispatcher->dispatch("post-findonebyid-" . $this->entityName, $event); return $event->getSubject(); }
public function isInitialised(Connection $dbConn) { $query = "\n SELECT\n SCHEMA_NAME\n FROM\n INFORMATION_SCHEMA.SCHEMATA\n WHERE\n SCHEMA_NAME = 'RollerRevisions'"; $results = $dbConn->fetchAssoc($query); if (!$results) { return false; } $query = "\n SELECT\n TABLE_NAME\n FROM\n INFORMATION_SCHEMA.TABLES\n WHERE\n TABLE_SCHEMA = 'RollerRevisions'\n AND\n TABLE_NAME = 'Revision'"; $results = $dbConn->fetchAssoc($query); if (!$results) { return false; } return true; }
public function editAction(Request $request, $id) { if ($request->isMethod('POST')) { // var_dump($request->request->all()); // die(); $this->db->update('post', ['published' => $request->request->has('published'), 'heading' => $request->request->get('heading'), 'content' => $request->request->get('content'), 'created_at' => $request->request->get('created_at')], ['id' => $id]); $url = $this->generateUrl('admin_post_edit', ['id' => $id]); return $this->app->redirect($url, 302); } else { // var_dump($request->query->all()); //For Get } // var_dump($request->server->all()); //For _SERVER $post = $this->db->fetchAssoc('SELECT * FROM post WHERE id = ?', [$id]); return $this->twig->render('/admin/post/edit.twig', array('post' => $post)); }