Взаимодействие с СУБД Лекция 6. Роль баз данных в Web В мире всемирной паутины большинство проектов так или иначе использует базы данных. Любая крупная. - презентация

1 Взаимодействие с СУБД Лекция 6

2 Роль баз данных в Web В мире всемирной паутины большинство проектов так или иначе использует базы данных. Любая крупная система управления контентом использует БД для хранения данных: vBulletin, WordPress, Joomla, Drupal, 1С-Битрикс, Moodle, Wikimedia и многие другие.

3 Интерфейсы доступа к базам данных Программный интерфейс (API) доступа к базам данных – промежуточное звено для связи СУБД и языка программирования. В языке PHP существуют как универсальные, так и специализированные интерфейсы доступа к БД.

4 Базы данных и PHP Поддерживаемые интерфейсы СУБД: MySQLMySQLiSQLite PostgreSQLOracle (OCI8)Oracle Microsoft SQL ServerSybaseODBC mSQLIBM DB2Informix Lotus NotesDB++DBM dBaseDBXFrontBase Ingres IISESAMFirebird / InterBase Paradox File AccessMaxDBPHP Data Objects (PDO)

5 Доминирующая роль MySQL

6 Способы доступа к MySQL в PHP Для доступа к СУБД MySQL из PHP существует три стандартных решения: Стандартная библиотека MySQL (устарела) Объектно-ориентированная библиотека MySQLi (громоздка) Универсальное расширение PHP Data Objects (PDO) (в самый раз!).

7 Стандартная библиотека MySQL $id = $_GET['id']; $sql="SELECT * FROM forum_messages WHERE id=".$id." ORDER BY name"; $query=mysql_query($sql); $rs=mysql_fetch_array($query);

8 Интерфейс PHP Data Objects PHP Data Objects (PDO) расширение для PHP, предоставляющее разработчику универсальный интерфейс для доступа к множеству баз данных, в т.ч. PostgreSQL, MySQL, Oracle (OCI8), SQLite. Чтобы узнать, для каких СУБД установлены драйверы в PDO, необходимо выполнить: print_r(PDO::getAvailableDrivers());

9 Имя источника данных (DSN) Имя источника данных (Data Source Name, DSN) – структура данных (строка), используемая для описания соединения к источнику данных. Используется во многих библиотеках, включая PDO и ODBC. DSN включает: – Имя источника данных – Месторасположение источника данных – Имя драйвера для доступа к источнику данных – Идентификатор пользователя для доступа к данным (если требуется) – Пароль для доступа к данным (если требуется)

10 Соединение с базой данных через PHP Data Objects $host = "localhost"; // сервер баз данных $db_name = "smiig"; // имя базы данных $charset = "utf-8"; // кодировка базы данных $user = "pavel"; // имя пользователя $pass = "qwerty"; // пароль $dsn = "mysql:host=$host;dbname=$db_name;charset=$charset"; // Дополнительные опции $opt = array( PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC ); // указатель на соединение $dbh = new PDO($dsn, $user, $pass, $opt); PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC ); // указатель на соединение $dbh = new PDO($dsn, $user, $pass, $opt);">

11 Соединение с базой данных через PHP Data Objects try { # MS SQL Server и Sybase через PDO_DBLIB $dbh = new PDO("mssql:host=$host;dbname=$db_name", $user, $pass); $dbh = new PDO("sybase:host=$host;dbname=$db_name", $user, $pass); # MySQL через PDO_MYSQL $dbh = new PDO("mysql:host=$host;dbname=$db_name", $user, $pass); # SQLite $dbh = new PDO("sqlite:my/database/path/database.db"); } catch(PDOException $e) { echo $e->getMessage(); } getMessage(); }">

12 Обработка исключений try { $dbh = new PDO($dsn, $user, $password); } catch (PDOException $e) { die('Подключение не удалось: '. $e->getMessage()); }

13 Этапы выполнения запроса Каждый запрос должен быть выполнен в 3 (или 4 этапа) – prepare() – подготовка SQL-запроса – bindValue() / bindParam() – привязка данных к запросу (необязательно) – execute() – выполнение запроса (и привязка данных) – fetch() и аналоги – получение данных. ПодготовкаПривязкаВыполнениеПолучение

prepare("UPDATE folks SET name = 'Пушкин' "); $query_delete = $dbh->prepare("DELETE FROM folks WHERE n" title="Вставка, обновление и удаление записей $query_insert = $dbh->prepare("INSERT INTO folks ( first_name ) values ( 'Пушкин' )"); $query_update = $dbh->prepare("UPDATE folks SET name = 'Пушкин' "); $query_delete = $dbh->prepare("DELETE FROM folks WHERE n" class="link_thumb"> 14 Вставка, обновление и удаление записей $query_insert = $dbh->prepare("INSERT INTO folks ( first_name ) values ( 'Пушкин' )"); $query_update = $dbh->prepare("UPDATE folks SET name = 'Пушкин' "); $query_delete = $dbh->prepare("DELETE FROM folks WHERE name = 'Пушкин' "); $query_insert->execute(); $query_update->execute(); $query_delete->execute(); prepare("UPDATE folks SET name = 'Пушкин' "); $query_delete = $dbh->prepare("DELETE FROM folks WHERE n"> prepare("UPDATE folks SET name = 'Пушкин' "); $query_delete = $dbh->prepare("DELETE FROM folks WHERE name = 'Пушкин' "); $query_insert->execute(); $query_update->execute(); $query_delete->execute();"> prepare("UPDATE folks SET name = 'Пушкин' "); $query_delete = $dbh->prepare("DELETE FROM folks WHERE n" title="Вставка, обновление и удаление записей $query_insert = $dbh->prepare("INSERT INTO folks ( first_name ) values ( 'Пушкин' )"); $query_update = $dbh->prepare("UPDATE folks SET name = 'Пушкин' "); $query_delete = $dbh->prepare("DELETE FROM folks WHERE n">

15 Подготовленные выражения Подготовленные выражения (Prepared statements) – заранее скомпилированное выражение, которое может быть многократно выполнено путем отправки серверу лишь различных наборов данных. Каждое скалярное значение, подставляемое в запрос динамически, должно быть представлено именованным (:name) или обычным placeholderом.

prepare("INSERT INTO folks (name," title="Примеры подготовленных выражений # без placeholders - дверь SQL-инъекциям открыта! $query = $dbh->prepare("INSERT INTO folks (name, addr, city) values ($name, $addr, $city)"); # безымянные placeholders $query = $dbh->prepare("INSERT INTO folks (name," class="link_thumb"> 16 Примеры подготовленных выражений # без placeholders - дверь SQL-инъекциям открыта! $query = $dbh->prepare("INSERT INTO folks (name, addr, city) values ($name, $addr, $city)"); # безымянные placeholders $query = $dbh->prepare("INSERT INTO folks (name, addr, city) values (?, ?, ?)"); # именные placeholders $query = $dbh->prepare("INSERT INTO folks (name, addr, city) values (:name, :addr, :city)"); prepare("INSERT INTO folks (name,"> prepare("INSERT INTO folks (name, addr, city) values (?, ?, ?)"); # именные placeholders $query = $dbh->prepare("INSERT INTO folks (name, addr, city) values (:name, :addr, :city)");"> prepare("INSERT INTO folks (name," title="Примеры подготовленных выражений # без placeholders - дверь SQL-инъекциям открыта! $query = $dbh->prepare("INSERT INTO folks (name, addr, city) values ($name, $addr, $city)"); # безымянные placeholders $query = $dbh->prepare("INSERT INTO folks (name,">

bindParam(1, $name); $query->bindParam(2, $ad" title="Безымянные placeholderы # безымянные placeholders $query = $dbh->prepare("INSERT INTO folks (name, addr, city) values (?, ?, ?)"); # назначаем переменные каждому placeholder, с индексами от 1 до 3 $query->bindParam(1, $name); $query->bindParam(2, $ad" class="link_thumb"> 17 Безымянные placeholderы # безымянные placeholders $query = $dbh->prepare("INSERT INTO folks (name, addr, city) values (?, ?, ?)"); # назначаем переменные каждому placeholder, с индексами от 1 до 3 $query->bindParam(1, $name); $query->bindParam(2, $addr); $query->bindParam(3, $city); # вставляем одну строку $name = "Александр Пушкин"; $addr = "набережная реки Мойки, д. 12"; $city = "Санкт-Петербург"; $query->execute(); # вставляем еще одну строку, уже с другими данными $name = "Михаил Лермонтов"; $addr = "набережная реки Фонтанки, д. 14"; $city = "Санкт-Петербург"; $query->execute(); bindParam(1, $name); $query->bindParam(2, $ad"> bindParam(1, $name); $query->bindParam(2, $addr); $query->bindParam(3, $city); # вставляем одну строку $name = "Александр Пушкин"; $addr = "набережная реки Мойки, д. 12"; $city = "Санкт-Петербург"; $query->execute(); # вставляем еще одну строку, уже с другими данными $name = "Михаил Лермонтов"; $addr = "набережная реки Фонтанки, д. 14"; $city = "Санкт-Петербург"; $query->execute();"> bindParam(1, $name); $query->bindParam(2, $ad" title="Безымянные placeholderы # безымянные placeholders $query = $dbh->prepare("INSERT INTO folks (name, addr, city) values (?, ?, ?)"); # назначаем переменные каждому placeholder, с индексами от 1 до 3 $query->bindParam(1, $name); $query->bindParam(2, $ad">

18 Безымянные placeholderы Если в запросе слишком много параметров, можно задать их в виде одного массива вместо нескольких переменных. # набор данных, которые мы будем вставлять $data = array('Александр Пушкин', 'набережная реки Мойки, д. 12', 'Санкт-Петербург'); $query = $dbh->prepare("INSERT INTO folks (name, addr, city) values (?, ?, ?)"); $query->execute($data); execute($data);">

19 Именные placeholderы # первым аргументом является имя placeholderа # его принято начинать с двоеточия # хотя работает и без них $query->bindParam(':name', $name); $query->bindParam(':addr', $addr); $query->bindParam(':city', $city);

20 Именные placeholderы # здесь тоже можно передавать массив, но он должен # быть ассоциативным в роли ключей выступают # placeholderы # данные, которые мы вставляем $data = array( 'name' => Александр Пушкин', 'addr' => 'набережная реки Мойки, д.12', 'city' => Санкт-Петербург' ); $query = $dbh->prepare("INSERT INTO folks (name, addr, city) VALUES (:name, :addr, :city)"); $query->execute($data); execute($data);">

21 Именные placeholderы # класс для простенького объекта class person { public $name; public $addr; public $city; function __construct($n,$a,$c) { $this->name = $n; $this->addr = $a; $this->city = $c; } # так далее... } $pushkin = new person('Александр Пушкин', 'набережная реки Мойки, д.12', 'Санкт-Петербург'); # а тут самое интересное $query = $dbh->prepare("INSERT INTO folks (name, addr, city) values (:name, :addr, :city)"); $query->execute((array)$pushkin); execute((array)$pushkin);">

22 Выборка данных Данные могут быть получены методом ->fetch. Существует несколько констант для установки режимов получения данных: PDO::FETCH_ASSOC: возвращает массив с названиями столбцов в виде ключей PDO::FETCH_NUM: возвращает массив с ключами в виде порядковых номеров столбцов PDO::FETCH_BOTH (по умолчанию): возвращает массив с индексами как в виде названий столбцов, так и их порядковых номеров. PDO::FETCH_OBJ: возвращает анонимный объект со свойствами, соответствующими именам столбцов PDO::FETCH_CLASS: присваивает значения столбцов соответствующим свойствам указанного класса. Если для какого-то столбца свойства нет, оно будет создано И некоторые другие Для установки режима получения данных существует следующий синтаксис: $query->setFetchMode(PDO::FETCH_ASSOC); Объекты PDO Массивы

23 Режим FETCH_ASSOC # поскольку это обычный запрос без placeholderов, # можно сразу использовать метод query() $query = $dbh->query('SELECT name, addr, city FROM folks'); # устанавливаем режим выборки $query->setFetchMode(PDO::FETCH_ASSOC); while($row = $query->fetch()) { echo $row['name']. "\n"; echo $row['addr']. "\n"; echo $row['city']. "\n"; }

addr. "\" title="Режим FETCH_OBJ # создаем запрос $query = $dbh->query('SELECT name, addr, city from folks'); # выбираем режим выборки $query->setFetchMode(PDO::FETCH_OBJ); # выводим результат while($row = $query->fetch()) { echo $row->name. "\n"; echo $row->addr. "\" class="link_thumb"> 24 Режим FETCH_OBJ # создаем запрос $query = $dbh->query('SELECT name, addr, city from folks'); # выбираем режим выборки $query->setFetchMode(PDO::FETCH_OBJ); # выводим результат while($row = $query->fetch()) { echo $row->name. "\n"; echo $row->addr. "\n"; echo $row->city. "\n"; } addr. "\"> addr. "\n"; echo $row->city. "\n"; }"> addr. "\" title="Режим FETCH_OBJ # создаем запрос $query = $dbh->query('SELECT name, addr, city from folks'); # выбираем режим выборки $query->setFetchMode(PDO::FETCH_OBJ); # выводим результат while($row = $query->fetch()) { echo $row->name. "\n"; echo $row->addr. "\">

25 Режим FETCH_CLASS class secret_person { public $name; public $addr; public $city; public $other_data; function __construct($other = '') { $this->addr = preg_replace('/[a-z]/', 'x', $this->addr); $this->other_data = $other; }

26 Режим FETCH_CLASS $query = $dbh->query('SELECT name, addr, city from folks'); $query->setFetchMode(PDO::FETCH_CLASS, 'secret_person'); while($obj = $query->fetch()) { echo $obj->addr; } # Если в базе данных адрес выглядит как 5 Rosebud, то на выходе получится 5 Rxxxxxx.

27 PDO и оператор LIMIT В режиме эмуляции данные, которые были переданы напрямую в execute(), форматируются как строки. То есть, экранируются кавычками. Поэтому LIMIT ?,? превращается в LIMIT '10', '10' и очевидным образом вызывает ошибку синтаксиса. $query = $dbh->prepare('SELECT * FROM table LIMIT ?, ?'); $query->bindParam(1, $limit_from, PDO::PARAM_INT); $query->bindParam(2, $per_page, PDO::PARAM_INT); $query->execute(); $data = $query->fetchAll();

28 Метод lastInsertID() Метод ->lastInsertId() возвращает id последней вставленной записи. Стоит заметить, что он всегда вызывается у объекта базы данных (у нас это $dbh), а не объекта с выражением ($query). $query = $dbh->prepare("INSERT INTO folks ( first_name ) values ( 'Пушкин' )"); $query->execute(); # Выведет последний ID, вставленный в БД echo $dbh->lastInsertId(); execute(); # Выведет последний ID, вставленный в БД echo $dbh->lastInsertId();">

exec("DELETE FROM folks WHERE addr LIKE '%набере" title="Метод exec() Запускает SQL запрос на выполнение и возвращает количество строк, задействованых в ходе его выполнения /* Удалить все строки, где присутствует слово "набережная" в адресе */ $count = $dbh->exec("DELETE FROM folks WHERE addr LIKE '%набере" class="link_thumb"> 29 Метод exec() Запускает SQL запрос на выполнение и возвращает количество строк, задействованых в ходе его выполнения /* Удалить все строки, где присутствует слово "набережная" в адресе */ $count = $dbh->exec("DELETE FROM folks WHERE addr LIKE '%набережная%'"); /* Возвращает количество удалённых строк */ print("Удалено $count строк.\n"); exec("DELETE FROM folks WHERE addr LIKE '%набере"> exec("DELETE FROM folks WHERE addr LIKE '%набережная%'"); /* Возвращает количество удалённых строк */ print("Удалено $count строк.\n");"> exec("DELETE FROM folks WHERE addr LIKE '%набере" title="Метод exec() Запускает SQL запрос на выполнение и возвращает количество строк, задействованых в ходе его выполнения /* Удалить все строки, где присутствует слово "набережная" в адресе */ $count = $dbh->exec("DELETE FROM folks WHERE addr LIKE '%набере">

30 Метод rowCount() Возвращает количество строк, модифицированных последним SQL запросом.

query($sql)) { # проверяем количество записей if " title="Подсчёт строк в SELECT без rowCount() # В версиях PHP до 5.1.6 rowCount не срабатывал для SELECT. # Рекомендуется использовать следующее решение $sql = "SELECT COUNT(*) FROM folks"; if ($query = $DBH->query($sql)) { # проверяем количество записей if " class="link_thumb"> 31 Подсчёт строк в SELECT без rowCount() # В версиях PHP до rowCount не срабатывал для SELECT. # Рекомендуется использовать следующее решение $sql = "SELECT COUNT(*) FROM folks"; if ($query = $DBH->query($sql)) { # проверяем количество записей if ($query->fetchColumn() > 0) { # делаем здесь полноценную выборку, потому что данные найдены! } else { # выводим сообщение о том, что удовлетворяющих запросу данных не найдено } query($sql)) { # проверяем количество записей if "> query($sql)) { # проверяем количество записей if ($query->fetchColumn() > 0) { # делаем здесь полноценную выборку, потому что данные найдены! } else { # выводим сообщение о том, что удовлетворяющих запросу данных не найдено }"> query($sql)) { # проверяем количество записей if " title="Подсчёт строк в SELECT без rowCount() # В версиях PHP до 5.1.6 rowCount не срабатывал для SELECT. # Рекомендуется использовать следующее решение $sql = "SELECT COUNT(*) FROM folks"; if ($query = $DBH->query($sql)) { # проверяем количество записей if ">

32 Закрытие соединения # закрывает подключение $dbh = null;

33 Администрирование баз данных MySQL phpmyadmin ( ) Команда mysql из командной строки операционной системы

34 Ссылки по теме s.php s.php -stacks-market-share-january-2014/ -stacks-market-share-january-2014/

35 Лабораторная работа Создать имя пользователя и базу данных в системе: Придумать базу данных с двумя связанными между собой таблицами и создать их через phpMyAdmin. У первой таблицы должны быть следующие поля: наименование, дата и другие по смыслу. Создать скрипты add.php, delete.php и show.php, добавляющие записи в первую таблицу БД, удаляющие их и просматривающие всю таблицу целиком.