public function run(Request $request, Response $response, array $args)
{
$db = new Db();
$db->install();
$config = new Config();
return new RedirectResponse($config->baseUrl());
}
public function run()
{
$config = new Config();
$this->write("Upgrading " . $config->siteTitle() . " . . . ");
$db = new Db();
$db->install();
$this->write("Upgrade complete");
}
public static function findByKey($key)
{
$db = new Db();
$query = 'SELECT * FROM ' . static::TABLE . ' WHERE col1=:key OR col2=:key';
$options = [':key' => $key];
if (false !== $db->query($query, static::class, $options)) {
echo 'Вывод записи по ключу: ';
var_dump($db->query($query, static::class, $options));
} else {
echo 'findByKey не работает<br/>';
}
}
/**
* Get this user's default group.
*
* @return StdClass with attributes: 'id', 'name'.
*/
public function getDefaultGroup()
{
$defaultGroupId = isset($this->data->default_group) ? $this->data->default_group : self::GROUP_PUBLIC;
$sql = "SELECT * FROM groups WHERE id = :id";
$group = $this->db->query($sql, ['id' => $defaultGroupId])->fetch();
return $group;
}
public function insert()
{
if (!$this->isNew()) {
return;
}
$columns = [];
$values = [];
foreach ($this as $k => $v) {
if ('id' == $k) {
continue;
}
$columns[] = $k;
$values[':' . $k] = $v;
}
var_dump($values);
$sql = '
INSERT INTO ' . static::TABLE . '
(' . implode(',', $columns) . ')
VALUES
(' . implode(',', array_keys($values)) . ')
';
$db = Db::instance();
$db->execute($sql, $values);
}
public function getReadGroup()
{
$groupId = isset($this->data->read_group) ? $this->data->read_group : User::GROUP_PUBLIC;
$readGroup = $this->db->query("SELECT * FROM groups WHERE id=:id", ['id' => $groupId])->fetch();
//dump($readGroup);exit();
return $readGroup;
}
public function delete()
{
if ($this->isNew()) {
return false;
}
$sql = 'DELETE FROM ' . static::TABLE . ' WHERE ' . static::PK . ' = ' . $this->getPk();
$db = Db::instance();
$db->execute($sql);
}
public function setUp()
{
$this->db = new Db();
$this->db->query("SET FOREIGN_KEY_CHECKS=0");
$this->db->query("DROP TABLE IF EXISTS `keywords`");
$this->db->query("DROP TABLE IF EXISTS `items`");
$this->db->query("DROP TABLE IF EXISTS `users`");
$this->db->query("DROP TABLE IF EXISTS `groups`");
$this->db->query("DROP TABLE IF EXISTS `user_groups`");
$this->db->query("SET FOREIGN_KEY_CHECKS=1");
$this->db->install();
if (!is_dir($this->dataDir())) {
mkdir($this->dataDir());
}
}
public static function getLastNews($count = 3)
{
$db = Db::instance();
$query = 'SELECT * FROM ' . static::TABLE . ' ' . 'ORDER BY date_create DESC ' . 'LIMIT ' . (int) $count;
$result = $db->query($query, [], static::class);
if (!empty($result)) {
return $result;
}
return false;
}
/**
* Метод удаляет запись из таблицы
*/
public function delete()
{
if ($this->isNew() || $this->isDeleted()) {
return false;
}
$sql = 'DELETE FROM ' . static::TABLE . ' WHERE id=:id;';
$data = [':id' => $this->id];
$db = \App\Db::instance();
$res = $db->execute($sql, $data);
if ($res) {
$this->isDeleted = true;
}
return $res;
}
<?php require_once __DIR__ . '/../../index.php'; use App\Db; $db = new Db(); $table = App\Models\User::TABLE; $classUser = '******'; $sql = 'INSERT INTO `' . $table . '` (`name`, `email`) VALUES (:name, :email);'; $args = [':name' => 'Авдеева Лидия', ':email' => '*****@*****.**']; echo 'Добавление нового пользователя<br>'; $res = $db->execute($sql, $args); echo '<pre>'; var_dump($res); echo '</pre><hr>'; $sql_2 = 'INSERT INTO `' . $table . '` (`name`, `email`) VALUES (:name, :email);'; $args_2 = [':name' => 'Авдеева Ксения', ':email' => '*****@*****.**']; echo 'Добавление нового пользователя<br>'; $res = $db->execute($sql_2, $args_2); echo '<pre>'; var_dump($res); echo '</pre><hr>'; echo 'Проверка добавления пользователя, выборка всех пользователей<br>'; $sql_3 = 'SELECT * FROM `' . $table . '`;'; $res = $db->query($classUser, $sql_3); echo '<pre>'; var_dump($res); echo '</pre><hr>'; $sql_4 = 'UPDATE `' . $table . '` SET `name`=:name WHERE id=:id;'; $args_4 = [':name' => 'Борисова Наталья', ':id' => 5]; echo 'Изменение имени пользователя с указанным id<br>'; $res = $db->execute($sql_4, $args_4);
/**
* Created by PhpStorm.
* User: Пользователь
* Date: 03.02.2016
* Time: 17:13
*/
?>
<h1>Добавьте к методам execute() и query() еще по одному аргументу - массив подстановок.<br>
Протестируйте работу новых методов.</h1>
<?php
require __DIR__ . '/../autoload.php';
use App\Db;
use tests\tcDz1_2;
$db = new Db();
// работоспособность метода execute() без подстановок
$res1 = $db->execute('CREATE TABLE `dz1_2` (
`id` SERIAL,
`art` CHAR(10) UNIQUE,
`name` VARCHAR(255));');
var_dump($res1);
//bool(true) первый раз; создана таблица dz1_2 в базе данных
// работоспособность метода execute() с подстановками
$param2 = [':art' => '1358064', ':name' => 'Стол'];
$res2 = $db->execute('INSERT INTO dz1_2 (art, name) VALUES (:art, :name);', $param2);
var_dump($res2);
// bool(true), в таблицу dz1_2 вставлена строка - артикул 1358064, название Стол
$param3 = [':art' => '69791459', ':name' => 'Стул'];
$res3 = $db->execute('INSERT INTO dz1_2 (art, name) VALUES (:art, :name);', $param3);
var_dump($res3);
<?php require_once __DIR__ . '/index.php'; use App\Db; $db = new Db(); $table = App\Models\User::TABLE; $table_1 = App\Models\News::TABLE; $class = 'App\\Models\\User'; $class_1 = 'App\\Models\\News'; $sql_1 = ' SELECT * FROM ' . $table . ' WHERE name = :name AND email = :email; '; $sql_2 = ' SELECT * FROM ' . $table . ' WHERE name = :name; '; $sql_3 = ' SELECT * FROM ' . $table . ' WHERE name like :name; '; $sql_4 = ' SELECT * FROM ' . $table . ' WHERE id = :id; '; $sql_5 = ' SELECT * FROM ' . $table_1 . ' ORDER BY id DESC LIMIT 3; '; $sql_6 = ' SELECT * FROM ' . $table_1 . ' WHERE id = :id; '; $arg_1 = ['name' => 'Иванов Иван', 'email' => '*****@*****.**']; $arg_2 = ['name' => 'Иванов Сергей']; $arg_3 = ['name' => '%Иванов %']; $arg_4 = ['id' => 2]; echo '<hr>'; echo 'Поиск пользователя по Имени и Email (есть ли такой)<br>'; $res = $db->execute($sql_1, $arg_1); echo '<pre>'; var_dump($res); echo '</pre><hr>'; echo 'Выборка данных пользователя по Имени и Email<br>'; $result = $db->query($sql_1, $class, $arg_1); echo '<pre>'; var_dump($result); echo '</pre><hr>'; echo 'Выборка данных пользователя по Имени<br>';
<?php
require __DIR__ . '/../tests.php';
$db = \App\Db::instance();
/**
* Check execute method if it returns false (row with id does not need to exist in database)
*/
echo check($db->execute("UPDATE users SET name = 'Петр Петров' WHERE id = 7"), 'Db->execute method without data params');
echo check($db->execute("UPDATE users SET name = 'Петр Петров' WHERE id = :id", ['id' => 7]), 'Db->execute method with data params');
/**
* Check query method if it returns non zero array (row with id should exists in database table)
*/
echo check($db->query("SELECT * FROM users WHERE id = 1", '\\App\\Models\\User'), 'Db->query method without data params');
echo check($db->query("SELECT * FROM users WHERE id = :id", '\\App\\Models\\User', ['id' => 1]), 'Db->query method with data params');