public function get_table_data_json($_post, $_table_config) { $query_factory = new QueryFactory('mysql'); #total records count $select = $query_factory->newSelect(); $select->cols(array('COUNT(' . $_table_config['primary_column'] . ') AS total'))->from($_table_config['db_tablename']); $result_set = $this->getReadConnection()->query($select->getStatement()); $result_set->setFetchMode(Phalcon\Db::FETCH_ASSOC); $total_records = $result_set->fetchArray($result_set)['total']; #filtered records count if (strlen($_post['search']['value']) > 1) { $search_string = strtoupper($_post['search']['value']); foreach ($_table_config['columns'] as $c) { $select->orWhere("UCASE(" . $c['db_field'] . ") LIKE '%" . $search_string . "%'"); } $result_set = $this->getReadConnection()->query($select->getStatement()); $result_set->setFetchMode(Phalcon\Db::FETCH_ASSOC); $total_filtered_records = $result_set->fetchArray($result_set)['total']; } else { $total_filtered_records = $total_records; } #return data $ret = array('draw' => $_post['draw'], 'recordsTotal' => $total_records, 'recordsFiltered' => $total_filtered_records, 'data' => array()); #page records $cols = []; foreach ($_table_config['columns'] as $c) { $cols[] = $c['db_field'] . ' AS f' . $c['i']; } $select = $query_factory->newSelect(); $select->cols($cols)->from($_table_config['db_tablename'])->orderBy(array($_table_config['columns'][$_post['order'][0]['column']]['db_field'] . " " . $_post['order'][0]['dir']))->limit($_post['length'])->offset($_post['start']); if (strlen($_post['search']['value']) > 1) { foreach ($_table_config['columns'] as $c) { $select->orWhere("UCASE(" . $c['db_field'] . ") LIKE '%" . $search_string . "%'"); } } $result_set = $this->getReadConnection()->query($select->getStatement()); $result_set->setFetchMode(Phalcon\Db::FETCH_ASSOC); $data = $result_set->fetchAll(); foreach ($data as $row) { $data_row = []; foreach ($_table_config['columns'] as $c) { $data_row[] = $row['f' . $c['i']]; } $ret['data'][] = $data_row; } return json_encode($ret); }
/** * Returns a new SELECT object. * @param string $tableName * @param array $cols * @return Common\SelectInterface */ public function newSelect($tableName = null, $cols = ['*']) { $select = parent::newSelect(); if (null !== $tableName) { $select->from($tableName); } if (null !== $cols) { $select->cols($cols); } return $select; }
/** * Remove relationship. * * @param \Percy\Entity\EntityInterface $entity * @param array $rels * @param array $map * * @return void */ public function deleteRelationship(EntityInterface $entity, array $rels, array $map) { $this->dbal->beginTransaction(); foreach ($rels as $rel) { $delete = $this->query->newDelete(); $delete->from($map['defined_in']['table']); $delete->where(sprintf('%s = :%s', $map['defined_in']['primary'], $map['defined_in']['entity'])); $delete->where(sprintf('%s = :%s', $map['target']['relationship'], 'relationship')); $delete->limit(1); $delete->bindValue('uuid', $entity[$map['defined_in']['entity']]); $delete->bindValue('relationship', $rel); $this->dbal->perform($delete->getStatement(), $delete->getBindValues()); } $this->dbal->commit(); }
/** * Saves a set of data to the table * This function will either insert or update, depending on if the entity passed already has an identifier set. The * generated/passed ID will be returned. * * @param object|array $data Data to save * @param string $table Table to save to * @param string $identifierColumn Identifier column to work against * @return int|string */ public function save($data, $table, $identifierColumn = 'id') { $data = $this->convertToArray($data); if (!empty($data[$identifierColumn])) { $update = $this->queryHandler->newUpdate(); $update->table($table)->cols(array_keys($data))->where($identifierColumn . ' = :' . $identifierColumn)->bindValues($data); $this->db->perform($update->__toString(), $update->getBindValues()); return $data[$identifierColumn]; } else { $insert = $this->queryHandler->newInsert(); $insert->into($table)->cols(array_keys($data))->bindValues($data); $this->db->perform($insert->__toString(), $insert->getBindValues()); $name = $insert->getLastInsertIdName($identifierColumn); return $this->db->lastInsertId($name); } }
/** * @return Aura\SqlQuery\Common\DeleteInterface */ public static function newDelete() { return self::$queryFactory->newDelete(); }
/** * {@inheritDoc} */ public function newSeqBindPrefix() { return parent::newSeqBindPrefix(); }
<?php require 'config.php'; use Aura\SqlQuery\QueryFactory; use Aura\Sql\ExtendedPdo; $query_factory = new QueryFactory('mysql'); $insert = $query_factory->newInsert(); $update = $query_factory->newUpdate(); $delete = $query_factory->newDelete(); $pdo = new ExtendedPdo('mysql:host=' . $mysql_host . ';dbname=' . $mysql_db, $mysql_user, $mysql_pass, array(), array()); function pdoSelect($select) { global $pdo; $sth = $pdo->prepare($select->getStatement()); $sth->execute($select->getBindValues()); return $sth->fetchAll(PDO::FETCH_ASSOC); } function pdoInsert($table, $data) { global $pdo, $insert; $insert->into($table)->cols(array_keys($data))->bindValues($data); $sth = $pdo->prepare($insert->getStatement()); $sth->execute($insert->getBindValues()); return $pdo->lastInsertId(); } function pdoUpdate($table, $id, $data) { global $pdo, $update; $update->table($table)->where('id = ?', $id)->cols(array_keys($data))->bindValues($data); $sth = $pdo->prepare($update->getStatement()); return $sth->execute($update->getBindValues());
public function select(string $table, $columns = ['*']) { $select = $this->query_factory->newSelect(); $select->from($table)->cols($columns); return $select; }
} catch (Assert\LazyAssertionException $e) { /** @var \Assert\InvalidArgumentException[] $errorExceptions */ $errorExceptions = $e->getErrorExceptions(); foreach ($errorExceptions as $errorException) { $flash->addMessage('error', $errorException->getMessage()); } return $response->withStatus(302)->withHeader('Location', $this->router->pathFor('getForm') . '?' . http_build_query(['email' => $postParams['email']])); } catch (\Exception $e) { $flash->addMessage('error', $e->getMessage()); return $response->withStatus(302)->withHeader('Location', $this->router->pathFor('getForm') . '?' . http_build_query(['email' => $postParams['email']])); } /* * Save results and redirect */ try { $qf = new QueryFactory('mysql'); /** @var SqlQuery\Mysql\Update $update */ $update = $qf->newUpdate(); $update->table('indiegogo')->where('Email = :email')->bindValue('email', $postParams['email']); if (!empty($postParams['shirtType'])) { $update->set('shirtType', ':shirtType')->bindValue('shirtType', $postParams['shirtType']); } if (!empty($postParams['shirtSize'])) { $update->set('shirtSize', ':shirtSize')->bindValue('shirtSize', $postParams['shirtSize']); } if (!empty($postParams['hoodieSize'])) { $update->set('hoodieSize', ':hoodieSize')->bindValue('hoodieSize', $postParams['hoodieSize']); } /** @var ExtendedPdo $pdo */ $pdo = $this->get('pdo'); $stm = $update->getStatement();
/** * return insert object * * @return \Aura\SqlQuery\Common\InsertInterface */ public function insert() { return $this->_factory->newInsert(); }
public function composeCountQuery() { $queryFactory = new QueryFactory('mysql'); $select = $queryFactory->newSelect(); $matchString = $this->getMatchString(); $select->from($this->table)->cols(['COUNT(*)'])->where($matchString); $select = $this->addWhereConditions($select); return (string) $select; }