<?php
require '../autoloader.php';
$db = new Yampee_Db_Manager(new Yampee_Db_Dsn(Yampee_Db_Dsn::DRIVER_MYSQL, 'test'), 'root', '');
/*
* Select
*/
$results = $db->query('SELECT * FROM test');
foreach ($results as $result) {
echo $result->getFirstField();
echo $result->getDateField()->format('d/m/Y H:i');
}
/*
* Insert
*/
$record = new Yampee_Db_Record();
// You can use camelCased magic methods ...
$record->setFirstField('127.0.0.1');
$record->setSecondField('127.0.0.1');
$record->setDateField(new DateTime());
// Or real methods
$record->set('firstField', '127.0.0.1');
$record->set('secondField', '127.0.0.1');
$record->set('dateField', new DateTime());
$db->insert('table_name', $record);
/*
* Query builder
*/
$records = $db->createQueryBuilder()->select('t.field, t.otherField, ot.foreignField')->from('table t')->leftJoin('otherTable ot ON ot.table_id = t.id')->where('t.id = :id')->setParameter('id', 4)->limit(5)->execute();
$db->createQueryBuilder()->insert('table t')->set('t.firstField', $firstValue)->set('t.secondField', $secondValue)->execute();
$db->createQueryBuilder()->update('table t')->set('t.firstField', $firstValue)->set('t.secondField', $secondValue)->where('t.id = :id')->setParameter('id', 4)->execute();
/**
* Execute the query and return the statement.
* @return array
* @throws LogicException
*/
public function execute()
{
$query = false;
if (!empty($this->select)) {
$query = 'SELECT ';
$query .= $this->select;
if (!empty($this->from)) {
$query .= ' FROM ' . $this->from;
}
if (!empty($this->innerJoin)) {
$query .= ' INNER JOIN ' . $this->innerJoin;
}
if (!empty($this->leftJoin)) {
$query .= ' LEFT JOIN ' . $this->leftJoin;
}
if (!empty($this->where)) {
$query .= ' WHERE ' . $this->where;
}
if (!empty($this->groupBy)) {
$query .= ' GROUP BY ' . $this->groupBy;
}
if (!empty($this->having)) {
$query .= ' HAVING ' . $this->having;
}
if (!empty($this->orderBy)) {
$query .= ' ORDER BY ' . $this->orderBy;
}
if (!empty($this->limit)) {
$query .= ' LIMIT ' . $this->limit;
}
if (!empty($this->offset)) {
$query .= ' OFFSET ' . $this->limit;
}
} elseif (!empty($this->insert)) {
$query = 'INSERT INTO ';
$query .= $this->insert;
$query .= ' SET ';
foreach ($this->set as $name => $value) {
$query .= $name . ' = :' . $name . ', ';
$this->setParameter($name, $value);
}
$query = substr($query, 0, -2);
} elseif (!empty($this->update)) {
$query = 'UPDATE ';
$query .= $this->update;
$query .= ' SET ';
foreach ($this->set as $name => $value) {
$query .= $name . ' = :' . $name . ', ';
$this->setParameter($name, $value);
}
$query = substr($query, 0, -2);
if (!empty($this->where)) {
$query .= ' WHERE ' . $this->where;
}
if (!empty($this->having)) {
$query .= ' HAVING ' . $this->having;
}
} elseif (!empty($this->delete)) {
$query = 'DELETE FROM ';
$query .= $this->from;
if (!empty($this->where)) {
$query .= ' WHERE ' . $this->where;
}
if (!empty($this->having)) {
$query .= ' HAVING ' . $this->having;
}
}
if (!$query) {
throw new LogicException('Query type can not ne found in QueryBuilder::execute()');
}
return $this->manager->query($query, $this->parameters);
}