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