Система управления базами данных MySQL и ее взаимодействие с PHP

1. Особенности реляционной базы данных MySQL

На сегодняшний день большинство сайтов, порталов, на которые вы заходите ежедневно, хранит свой контент в базах данных. Это намного более эффективно по сравнению с созданием отдельных HTML-страниц. Любой контент, который вы видите на сайтах (пункты меню, статьи, комментарии пользователей, фотографии, списки друзей в соцсетях и многое другое), хранится в удобном, структурированном виде.

Базы данных состоят из множества таблиц, которые связаны друг с другом. Запросы, которые выполняются к базам данных, отличаются лаконичностью, универсальностью (для большинства баз данных используется единый язык структурированных запросов SQL). По сравнению с обработкой файлов, SQL позволяет выполнять запросы гораздо быстрее, к тому же эффективно решается проблема одновременного доступа множества запросов к одним и тем же данным.

MySQL – это реляционная система управления базами данных. То есть данные в ее базах хранятся в виде логически связанных между собой таблиц, доступ к которым осуществляется с помощью языка запросов SQL. MySQL – свободно распространяемая система, т.е. платить за ее применение не нужно. Кроме того, это достаточно быстрая, надежная и, главное, простая в использовании СУБД, вполне подходящая для не слишком глобальных проектов. Связка «PHP и MySQL» достаточно прочно зарекомендовала себя при разработке небольших проектов.

Данные в базе структурированы в виде таблиц, модель представления которых называется реляционной (relation – от англ. отношение). Автором реляционной модели является Э. Кодд, который первым предложил использовать для обработки данных аппарат теории множеств (объединение, пересечение, разность, декартово произведение) и показал, что любое представление данных сводится к совокупности двумерных таблиц особого вида, известного в математике как отношение. Все таблицы в базе данных связаны между собой. Это позволяет устранить избыточность данных, дублирование.

Каждая запись в таблице должна быть уникальна, чтобы при проведении запросов обращение к записи было однозначным. Поле или набор полей, которые однозначно характеризуют запись, называют первичным ключом. Ключи также используются для связи между таблицами (например, чтобы получить связные данные из таблицы о том, какой пользователь какую оценку получил за тест, не нужно хранить все эти данные в одной таблице, все организуется через отношения).

Для удобства в качестве ключевого поля чаще всего задают не несколько полей, а отдельное поле, все значения которого генерируются по порядку. Таким образом, СУБД может организовать без участия разработчика уникальные идентификаторы записей. В MySQL мы чаще всего будем называть это поле id, от слова identification (анг.) – идентифицировать. Данное поле обозначается как первичный ключ (Primary key), и в качестве значений по умолчанию устанавливается AUTO_INCREMENT (счетчик), что означает, что каждая новая запись будет иметь последовательное значение. И даже если будут удалены какие-то записи, счетчик не собьется, а нумерация будет происходить последовательно с увеличением на единицу.

2. Программное обеспечение для работы с MySQL

Для удобства работы с MySQL разработано достаточно много удобных утилит, хотя с MySQL можно работать и из интерфейса обычной командной строки.

Самым распространенным решением для работы с MySQL является утилита phpMyAdmin, которая написано на PHP и является веб-приложением. PhpMyAdmin входит в состав Denwer, и для того чтобы запустить его, достаточно стартануть вебсервер и обратиться в браузере по адресу http://localhost/tools/phpmyadmin. Однако следует понимать, что веб-приложение всегда будет работать несколько медленнее десктопных приложений. Но для наших лабораторных работ данное программное обеспечение будет вполне подходящим.

Для всех утилит по работе с MySQL характерны общие возможности, такие как:

• создание, редактирование, удаление, копирование баз данных;
• создание, редактирование, удаление, копирование таблиц;
• запуск и отладка SQL-запросов;
• просмотр содержимого баз данных и таблиц;
• экспорт / импорт баз данных, таблиц и отдельных записей;
• управление пользователями и правами пользователей к доступу по управлению базами данных;
• и др. сервисные функции.

Интерфейс phpMyAdmin представлен на рисунке 1.

Рис. 1. Интерфейс phpMyAdmin

HeidiSQL – это бесплатное, настольное (десктопное) приложение для работы с базами данных MySQL, MariaDB, Microsoft SQL, PostgreSQL и SQLite. Помимо всех базовых возможностей по работе с базами данными и таблицами, преимуществом heidiSQL является то, что ПО позволяет подключаться к различным серверам баз данных в одном окне; скорость выполнения работы значительно быстрее, чем в phpMyAdmin, работает с триггерами, процедурами SQL; есть возможность сохранения SQL-запросов; выполняет очень удобный поиск по всей таблице; позволяет легко импортировать / экспортировать данные в текстовые и табличные форматы; возможность написания запросов SQL с подсветкой синтаксиса и многое другое. Если вы всерьез заинтересовались веб-разработкой, оцените все возможности heidiSQL. Интерфейс показан на рисунке 2.

Рис. 2. Интерфейс heidiSQL

SQLYOG – когда-то начатый как студенческий проект, который на сегодняшний день является достаточно профессиональным решением. Доступна как бесплатная, так и платная версии. Как и heidiSQL, SQLYOG является настольным (десктопным) приложением, следовательно, скорость его работы будет достаточно высокой. Главные функции SQLyog:

• удобный конструктор запросов;
• умное автозавершение работы;
• интеллектуальное дополнение кода;
• туннелирование HTTP и HTTPS;
• туннелирование SSH;
• возможность синхронизации данных;
• полноценная поддержка Юникода.

Интерфейс программы представлен на рисунке 3.

Рис. 3. Интерфейс SQLYOG

3. Создание базы данных, таблицы с использованием phpMyAdmin

Рассмотрим вопросы создания базы данных, таблиц баз данных с помощью утилиты phpMyAdmin. Проектирование архитектуры базы данных приложения чаще всего предшествует написанию кода на php. Действительно, важно детально погрузиться в предметную область, создать группу таблиц, заполнить их предварительными сведениями и только затем переходить к коду обработки содержимого баз данных на PHP.

Для создания базы данных достаточно указать ее название в специальное поле в phpMyAdmin по адресу http://localhost/tools/phpmyadmin/ (рисунок 4).

Рис. 4. Создание базы данных в phpMyAdmin

Имя базы данных может состоять из символов латинского алфавита (хотя в некоторых случаях допускается используется кириллицы, мы не рекомендуем это делать), цифр, знака доллара «$», знака нижнего подчеркивания «_». Запрещено использовать символ пробела « », точки «.», символы слешей «\», «/». Также ограничение накладывается на длину названия базы данных – она не должна превышать 64 символа.

Фактически база данных – это только имя, сами данные содержат таблицы баз данных. После создания базы данных в структуре каталогов вашего сервера формируется папка с названием вашей базы данных (рисунок 5).

Рис. 5. Каталог созданной базы данных

Для созданной базы данных можно создать пользователя, которому будут доступны определенные привилегии по управлению базой данных. К таким привилегиям относят: управление данными в таблицах (выборка, добавление, редактирование, удаление данных); управление структурой базы данных (добавление / удаление новых таблиц, индексов, уничтожение базы данных и др.); администрирование базы данных.

Создать пользователя можно на странице Привилегии для базы данных. Процедура не сложна: нужно придумать имя пользователя, пароль, подтвердить пароль, проверить или отметить галочкой базу данных, для которой назначаются привилегии и отметить галочкой те опции, которые пользователь сможет выполнять (рисунок 6).

Рис. 6. Создание пользователя и определение его привилегий

После этого можно перейти к созданию таблицы в базе данных. На странице с выбранной базой данных есть поле, где можно задать имя таблицы и установить количество полей (рисунок 7). Если заранее вы не можете знать, сколько полей в таблице у вас будет, то можно поставить любое число – лишнее phpMyAdmin уберет, а если нужно будет больше полей, то их можно дополнить.

Рис. 7. Создание таблицы в базе данных

После нажатия на кнопку Ok, появится форма с созданием новых полей, указанием их типов, длины и некоторых иных параметров. Для начала рассмотрим, какие типы полей есть в MySQL. Тип поля определяет ограничения на данные и операции, которые могут быть произведены с данными этого типа. Как и в системах программирования, в MySQL тип поля определяет внутренний формат представления данных. Так, например, тип int может содержать только целочисленный набор данных, varchar — строки ограниченной длины. И очевидно, что действия, которые можно производить с целыми числами – совсем не то же самое, что действия со строками.

К целочисленным типам данных в MySQL относят:

• TINYINT. Диапазон значений от –127 до 128, либо 0 до 255, в зависимости от того, может ли это поле быть отрицательным.
• SMALLINT. Диапазон значений: –32 768 до 32 767, либо от 0 до 65 535.
• MEDIUMINT. Диапазон значений: от –8 388 608 до 8 388 607, либо от 0 до 16 777 215.
• INT. Диапазон значений: от –2 147 483 648 до 2 147 483 647, либо от 0 до 4 294 967 295.
• BIGINT. Диапазон значений: от –9 223 372 036 854 775 808 до 9 223 372 036 854 775 807, либо от 0 до 18 446 744 073 709 551 615.

К вещественным типам данных относят:

• FLOAT. Точность одинарная, то есть число знаков после запятой может быть не более 24-х (для двоичного представления). Диапазон значений: от — 3,402823466E+38 до -1,175494351E-38, 0, и от 1,175494351E-38 до 3,402823466E+38.
• DOUBLE. Двойная точность. Количество знаков после запятой может составлять до 53-х (для двоичного представления). Допустимые значения: от — 1,7976931348623157E+308 до –2,2250738585072014E–308, 0, и от 2,2250738585072014E–308 до 1,7976931348623157E+308. Данный тип используется, если нужны действительно огромные числа.
• DECIMAL. Это число, похожее на тип DOUBLE, но хранится оно в виде строки. И, фактически, интервал допустимых значений определяется наличием знака «–» и «.». Если эти знаки отсутствуют, то допустимый интервал такой же, как и у DOUBLE.

К строковому типу данных относят:

• TEXT. Максимальная длина 65 535 символов. Самый используемый вариант при хранении текстовых данных.
• TINYTEXT. Текст с длиной от 0 до 255 символов.
• MEDIUMTEXT. Текст с длиной от 0 до 16 777 215 символов.
• LONGTEXT. Текст с длиной от 0 до 4 294 967 295 символов.
• VARCHAR. Текст переменной длины от 0 до 255 символов.
• CHAR. Длина фиксированная (независимо от количества переданных символов). Диапазон составляет от 0 до 255 символов. При передаче данных меньше 255 символов в конце к данным дописываются пробелы, чтобы длина строки достигла заданного размера.

К типу даты / времени относят:

• DATE. Хранит дату. Формат следующий: YYYY-MM-DD (год, месяц, день). Например, такое значение будет удовлетворять этому полю: 2021-05-02.
• DATETIME. Хранит дату и время. Формат следующий: YYYY-MM-DD HH:MM:SS (год-месяц-день час-минута-секунда). Например: 2021-04-21 09:41:22
• TIMESTAMP. Хранит дату и время. Имеет следующие форматы: YYYYMMDDHHMMSS, YYMMDDHHMMSS, YYYYMMDD, YYMMDD.
• TIME. Хранит время. Формат: HH:MM:SS. Например: 09:21:55.
• YEAR. Хранит дату (год). Форматы: YY, YYYY.

Самыми популярными типами данных из всех перечисленных выше являются int, float, varchar, text, date, datetime.

Некоторым полям можно задавать атрибуты, например числовое поле делать автоматическим счетчиком или определять, что значения определенного поля будут неотрицательными:

• Атрибут AUTO_INCREMENT – генерирует новое порядковое значение для строк;
• Атрибут UNSIGNED – данное числовое значение будет неотрицательным.

Определившись с типами данных, мы можем создать таблицу. Таблица articles будет содержать сведения о публикуемых статьях (подробнее, как создать функционирующий блог, мы рассмотрим в рамках лабораторного практикума).

Первое поле по традиции назовем id (от слова identify – идентифицировать). Оно предназначено для хранения уникального номера для каждой статьи. Тип этого поля INT, длиной 11 знаков (значит хранить можно максимально 11-значное десятичное число). Также ему добавляется атрибут AUTO_INCREMENT. Указывается, что поле является первичным ключом PRIMARY.

Второе поле предназначено для хранения названия статьи, назовем его title. Так как заголовок статьи вряд ли будет занимать больше двух строк текста, то его тип можно определить, как VARCHAR, длиной 255 символов.

Третье поле будет хранить краткий анонс статьи (описание), можно его назвать description. Его можно определить типа TEXT. Данное поле не имеет ограничений.

Четвертое поле будет хранить полный текст статьи text. Его тип также будет TEXT.

Пятое поле будет хранить ФИО автора статьи, будет называться author. Тип поля можно сделать VARCHAR, длиной до 255 символов.

Создание структуры таблицы articles показано на рисунке 8.

Рис. 8. Создание структуры таблицы articles

В любой момент можно внести изменения в структуру таблицы – добавить новые поля или удалить / отредактировать имеющиеся.

Мы можем добавить поле для хранения даты создания статьи. Назовем его date_created, оно будет типа DATETIME. Чтобы его внести, нужно перейти на вкладку Структура для нашей таблицы и поставить, после какого поля мы хотим добавить новое поле (рисунок 9). После этого поле заполняется аналогичным образом.

Рис. 9. Процедура добавления нового поля в таблицу

На вкладке Структура также можно любое поле отредактировать (нажать на карандаш ) или удалить (нажать на ).

Сейчас у нас есть таблица, но в ней нет данных. Не начинайте писать код на PHP не заполнив нужной таблицы. Как минимум две записи нужно внести в таблицу. Дело в том, что данные из таблиц будут формироваться в циклах на PHP (как это делается, мы рассмотрим позднее). Если у вас будет в таблице только одна запись, то вы не сможете протестировать работу такого цикла – одна запись может выводиться по одной выборке и без цикла. А вот если в таблице у вас две записи и при организации кода на PHP вы также получили две записи, то можно говорить, что все сделано правильно.

Чтобы вставить данные в таблицу, нужно перейти на вкладку Вставить. По умолчанию phpMyAdmin сразу предлагает вставить две записи. Заполняются только поля столбца Значения. Здесь следует отметить, что если мы установили для поля id атрибут AUTO_INCREMENT, то задавать его значения не нужно (оставляйте это поле пустым). На рисунке 28 показан процесс внесения данных одной строки таблицы articles.

Рис. 10. Процедура внесения строки в таблицу articles

Следует отметить, что работать с phpMyAdmin просто и интуитивно понятно, если вы уже знакомы с системами управления базами данных. Многие элементы подписаны, есть подсказки, главное – быть внимательным.

4. Оператор SELECT

Рассмотрим наиболее популярные запросы к базам данных.

Запрос на выборку SELECT позволяет выбрать данные из таблицы с учетом некоторого условия.

Общи вид запроса на выборку следующий:

SELECT <что_выбираем> FROM <имя_таблицы>

[WHERE <условие_для_выборки>]

[ORDER BY <название_поля> [ASC | DESC]] [LIMIT [offset,] rows]]

<что_выбираем> может быть перечнем полей, либо оператором *, который заменяет выбор всех полей.

Сравните:

SELECT id, title, description, text, author FROM articles

или

SELECT * FROM articles

Выражения WHERE, ORDER BY и LIMIT не являются обязательными в разделе.

Именно поэтому в нашем запросе они заключены в квадратные скобки.

Выражение WHERE позволяет задать условие выборки. Если нужно составить сложное условие, то могут быть использованы операторы AND (логическое «И»), OR (логическое «ИЛИ») и NOT (логическое «НЕ»).

Пример, запрос на выборку всех записей со значением поля id, равное 2, и поля author, содержащего ‘Расмус Лердорф’:

SELECT * FROM articles WHERE id = 2 AND author='Расмус Лердорф'

MySQL условие LIKE позволяет использовать шаблоны в операторе WHERE. Это позволяет выполнять сопоставление шаблонов. В сочетании с оператором LIKE используются два подстановочных знака:

• «%» – знак процента представляет ноль, один или несколько символов;
• «_» – знак подчеркивания представляет один символ.

Примеры работы с операторами представления находятся в таблице 1.

Таблица 1. Примеры операторов представления

LIKE Оператор	Описание
WHERE FieldName LIKE ‘a%’	Находит любые значения, которые начинаются с «a»
WHERE FieldName LIKE ‘%a’	Находит любые значения, которые заканчиваются «a»
WHERE FieldName LIKE ‘%or%’	Находит любые значения, которые имеют значение «or» в любом положении
WHERE FieldName LIKE ‘_r%’	Находит все значения, которые имеют значение «r» во второй позиции
WHERE FieldName LIKE ‘a_%_%’	Находит любые значения, которые начинаются с «a» и длиной не менее 3 символов
WHERE FieldName LIKE ‘a%o’	Находит любые значения, которые начинаются с «a» и заканчиваются на «o»

Выражение ORDER BY используется для сортировки записей в вашем результирующем наборе. Здесь обязательно указывается поле для сортировки и тип сортировки: по возрастанию (ASC) и по убыванию (DESC). Если атрибуты ASC или DESC не указаны в операторе ORDER BY, результаты будут отсортированы по полю в порядке возрастания. Это эквивалентно выражению ORDER BY <поле> ASC.

Например, следующий запрос отсортирует сначала в порядке убывания по автору, затем по названию в порядке возрастания:

SELECT * FROM articles ORDER BY author DESC, title

Выражение LIMIT может использоваться для ограничения количества строк, возвращенных командой SELECT. LIMIT принимает один или два числовых аргумента. Эти аргументы должны быть целочисленными константами. Если заданы два аргумента, то первый указывает на начало первой возвращаемой строки, а второй задает максимальное количество возвращаемых строк. При этом смещение начальной строки равно 0 (не 1).

SELECT * FROM articles LIMIT 5,10

возвращает строки 6–15.

Часто возникает необходимость выбрать из базы данных некоторые строки в случайном порядке и вывести только часть из них. Например, на сайте в фотогалерее требуется вывести 5 случайных фотографий. Для этого MySQL имеет встроенную функцию RAND(). Работает она следующим образом:

SELECT * FROM articles ORDER BY RAND() LIMIT 5

Здесь функция RAND() сгенерирует случайное число для каждой строки в таблице. Предложение ORDER BY сортирует все строки в таблице по случайному числу, сгенерированному функцией RAND(). Предложение LIMIT выбирает первые 5 строк в наборе результатов, отсортированных случайным образом. Если LIMIT не указан, то будут выведены все записи в случайном порядке. Данный способ окажется гораздо быстрее по скорости и лаконичнее по записи кода, чем если бы мы доверили ту же задачу PHP.

Если требуется отсортировать записи в некотором заранее известном порядке для разработчика (сортировка по определенной последовательности), то применяется функция FIELD(). В данной функции на первом месте указывается поле, по которому производится сортировка, затем через запятую перечисляется порядок, в котором должны выводиться записи по указанному полю.

SELECT * FROM articles ORDER BY FIELD(id, 4,2,1,3)

Данный запрос выведет первые записи из таблицы articles в порядке поля id – 4, 2, 1, 3 записи. Если в таблице есть другие записи, то заданная упорядоченность работать не будет. Если же значений в функции FIELD() больше, чем строк в таблице, то заданный порядок сохранится.

К запросу на выборку применимы также и некоторые агрегатные функции. К ним относят подсчет суммы, количества, минимального значения, максимального значения, среднего арифметического. Агрегатные функции SQL действуют в отношении значений столбца с целью получения единого результирующего значения. Проще говоря, используя эти функции, мы можем из множества значений столбца получить одно единственное. Такие же операции программист может сделать и с использованием языка программирования, однако MySQL выполняет подобные запросы на порядок быстрее.

Общий формат обращения к агрегатной функции в операторе SELECT следующий:

SELECT <функция>(<поле>) FROM <таблица> [WHERE …]

В качестве функций может выступать одно из следующих значений

• AVG – вычисляет среднее значение;
• SUM – вычисляет сумму значений;
• MIN – вычисляет наименьшее значение;
• MAX – вычисляет наибольшее значение;
• COUNT – вычисляет количество строк в запросе.

Пусть имеется таблица со списком статей articles. Чтобы подсчитать общее количество статей в таблице, можно написать запрос:

SELECT COUNT(id) FROM articles

В данном случае запрос вернет нам количество записей из таблицы articles. Можно записать этот запрос несколько иначе – вместо поля id использовать символ * (все поля). Результат получится одинаковым.

Пусть имеется таблица guest_book, хранящая в полях id, name, message, email, date_created информацию о сообщениях гостевой книги (подобную таблицу мы сделаем позже в рамках лабораторных работ). Проиллюстрируем запрос, который выведет даты самого раннего и самого позднего сообщений:

SELECT MIN(date_created),MAX(date_created) FROM guest_book

При проектировании баз данных, каждая таблица относится к описанию одной сущности. Большой задачей является нормализация данных при представлении в таблицах. В результате проектирования часто возникает необходимость отдельные сущности хранить в разных таблицах, но между таблицами при этом существуют связи.

Простым примером здесь могут служить сущность «Студенты» (ФИО, дата рождения, принадлежность группе) и сущность «Группы» (номер группы, факультет). Связь устанавливается через номер группы – понятно, что каждый студент учится в какой-то группе, которая в свою очередь находится на определенном факультете. Но в самой таблице «Студенты» вовсе не нужно для каждого студента прописывать принадлежность к факультету, достаточно установить связь, к какой группе он относится, и потом уже с помощью запросов особого типа получить сведения по группе.

Связи между таблицами MySQL выполняются в результате запроса JOIN. Пусть имеются две таблицы:

Students (id, fio, birthday, groupid);

Groups (id, name, faculty).

Связь устанавливается через соединение поля groupid таблицы Students и поля id таблицы Groups. Задача запроса – найти одинаковые значения в данных полях в обеих таблицах. Чтобы это произошло, значения id в таблице Groups должны быть уникальными, а вот значения groupid могут повторяться.

Таблица 2. Таблица Students

id	fio	birthday	groupid
1	Иванов Иван Иванович	01.01.1990	1
2	Петров Петр Петрович	02.02.1990	2
3	Сидорова Мария Владимировна	03.03.1989	1
4	Никитина Елена Викторовна	04.04.1990	3

Таблица 3. Таблица Groups

id	name	faculty
1	213-092-5-1	Математики, физики, информатики
2	409-053-5-1	Филологический
3	302-041-5-1	Исторический
4	511-066-5-1	Иностранных языков

Чтобы вывести, в какой группе и на каком факультете учится каждый студент, запрос MySQL будет выглядеть следующим образом:

SELECT * FROM Students, Groups WHERE Students.groupid = Groups.id

В результате запроса получим следующие сведения (рисунок 11).

Рис. 11. Результат запроса на соединение

Алгоритм здесь несложный: берётся первая запись из таблицы Students. Далее берётся её id и анализируются все записи из таблицы Groups, добавляя в результат те, у которых groupid равен id из таблицы Groups. Таким образом на первой итерации собираются все сведения у первого студента. На второй итерации собираются все сведения у второго студента и так далее.

Оператор JOIN выполняет полное объединение. Операторы JOIN, INNER JOIN и «,» (запятая) с указанием условий объединения дают пересечение для всех совпадающих значений из условия.

SELECT t1.*, t2.* FROM t1, t2 ON t1.i1 = t2.i2

Аналогично можно использовать и такую запись

SELECT t1.*, t2.* FROM t1 INNER JOIN t2 ON t1.i1 = t2.i2

Операторы LEFT JOIN, RIGHT JOIN выполняют, соответственно, левое и правое объединения таблиц.

При использовании оператора LEFT JOIN выбираются все значения из левой таблицы (первой указанной), после чего к ним подбираются соответствующие значения из правой, если таких нет, то все значения для правой таблицы в этой строке равны NULL.

SELECT t1.*, t2.* FROM t1 LEFT JOIN t2 ON t1.i1 = t2.i2

RIGHT JOIN противоположен LEFT JOIN: всё абсолютно также, но выбираются все значения для правой таблицы из выражения.

При указании любого из операторов JOIN можно использовать ключевое слово USING() вместо ON, если названия столбцов в обеих таблицах одинаковы.

5. Оператор INSERT

Запрос на вставку INSERT позволяет вставить новые записи в таблицу. Общий вид запроса:

INSERT INTO <имя_таблицы> (<поле1>, <поле2>, ... )

VALUES (<выражение1>, <выражение2>, ... )

Например,

INSERT INTO articles (id, title) VALUES (3, 'Синтаксис запроса INSERT')

вставит в таблицу articles строку с id равным 3 и заголовком «Синтаксис запроса INSERT». Остальные поля будут заданы значениями по умолчанию (чаще всего это NULL, 0 или пустая строка).

6. Оператор UPDATE

Запросы на обновление имеющихся записей UPDATE. Его синтаксис следующий

UPDATE <имя_таблицы>

SET <поле1> = <выражение1>, <поле2> = <выражение2>,

... [WHERE <условие_для_выборки>]

Здесь выражение WHERE не является обязательным, но если не задуматься над условием для выборки, то обновятся все записи в таблице.

Например, запрос

UPDATE articles SET date_created='2021-15-05 00:00:00', author='Oracle corporation' WHERE id = 1

обновит дату создания и имя автора для записи с id = 1. Не указанные в запросе поля останутся без изменения.

Заметим, что оператор UPDATE является небезопасным запросом, поэтому при его выполнении в последних версиях phpMyAdmin появится предупреждение. Однако необходимо всегда помнить, что без оператора WHERE будут заменены все записи. Лучше перед проведением таких запросов сделать копию таблицы.

7. Оператор DELETE

Запрос на удаление записей DELETE в общем виде выглядит так

DELETE FROM <имя_таблицы> [WHERE <условие_для_выборки>]

Например, удалит записи о статьях, созданные позже 15 мая 2021 года, запрос

DELETE FROM articles WHERE date_created > '2021-05-15'

Оператор DELETE так же, как и UPDATE, является небезопасным запросом, поэтому при его выполнении в последних версиях phpMyAdmin появится предупреждение. Однако необходимо всегда помнить, что без оператора WHERE будут удалены все записи. Лучше перед проведением таких запросов сделать копию таблицы.

8. Тестирование запросов в phpMyAdmin

С точки зрения PHP, запрос к базе данных на языке SQL – это обычная строка. Интерпретатор PHP не способен проверить правильность или неправильность написания запроса. Единственный тип ошибки, который PHP может выдать, если вы неправильно составили запрос, – это предупреждение, что в результате запроса PHP не получил никаких данных. При этом информации о том, что было записано в запросе неверно, у вас не будет.

Прежде чем формулировать запрос непосредственно в PHP, протестируйте его на правильность выполнения через панель SQL-запросов самого MySQL-сервера. Это в первую очередь касается «безопасных» запросов SELECT, которые не меняют содержимое таблиц базы данных. Рассмотрим, как это можно сделать с помощью phpMyAdmin.

Предположим, что у нас создана база данных my_db, в которой есть таблица со статьями для нашего блога articles. Для таблицы articles были созданы поля id (первичный ключ, уникальный идентификатор статьи), title (заголовок статьи), description (краткая аннотация статьи), text (полный текст статьи), date_created (дата создания статьи), author (автор статьи). Попробуем составить и протестировать запрос на получение всех записей из таблицы articles. SELECT * FROM articles

Чтобы выполнить этот запрос, нужно перейти в phpMyAdmin по адресу http://localhost/tools/phpMyAdmin, затем выбрать нужную базу данных – my_db, после этого перейти на вкладку SQL и вставить свой запрос (рисунок) и нажать на кнопку OK (рисунок 12).

Рис. 12. Тестирование запроса SQL в панели phpMyAdmin

Настоятельно рекомендуем вам сохранять свои запросы в каком-нибудь текстовом документе (подойдет Блокнот, Notepad++), потому что выполненный запрос система не сохраняет. Если запрос был выполнен успешно, то вы увидите таблицу со значениями (рисунок 13).

Рис. 13. Результат успешно выполненного запроса

Если же в запросе была допущена ошибка, то вы увидите сообщение, в котором несложно догадаться, где вы допустили ошибку. В результате выполнения запроса на рисунке 14 была допущена ошибка при наименовании поля. Правильно было бы обратиться к полю author, но в запросе обращение идет к полю avtor. phpMyAdmin сразу же выдал ошибку насчет этого. Правильный запрос должен выглядеть так:

SELECT id, title, author FROM articles

Рис. 14. Результат выполнения запроса с ошибкой

Аналогичным способом можно тестировать запросы на INSERT, UPDATE, DELETE, только нужно всегда помнить, что эти запросы меняют состояние таблиц. Если ошибочно написать в запросе UPDATE или DELETE без условия WHERE, вы рискуете изменить свои таблицы без возможности вернуть все назад. Поэтому важно перед проведением этих типов запросов сделать копию (бекап) нужной вам таблицы.

Сделать копию таблицы можно на вкладке Операции. Предварительно вы должны выбрать базу данных, таблицу, которую хотите скопировать. На вкладке Операции находится блок, в котором можно указать, как должна называться новая таблица, а также указать, что необходимо перенести при копировании: только структуру, структуру и данные или только данные. Ход копирования показан на рисунке 15.

Рис. 15. Ход выполнения копирования таблицы в phpMyAdmin

9. Организация взаимодействия PHP И MYSQL

После установки и настройки базы данных MySQL приступим к рассмотрению вопроса, каким образом PHP-скрипты соединяются с базой данных, каким образом происходит обращение к нужной таблице и как обрабатываются результаты, полученные из базы данных.

Самая первая функция, изучаемая нами в данном параграфе, производит инициализацию подключения к базе данных MySQL. Функция называется mysql_connect ($host, $user, $password). Данной функции на вход подается три параметра:

• $host – имя сервера, на котором находится база данных, в большинстве случаев используется локальный хост localhost;
• $user – имя пользователя, которого мы создавали для подключения к базе данных. Можно использовать root в качестве универсального пользователя, у которого нет пароля. Но делать это можно только в период тестирования.
• $password – пароль пользователя, который задавался при создании базы данных.

Результат функции mysql_connect сохраняется в некоторую переменную, например, $db, которая впоследствии может быть использована в запросах. Пример:

$db=mysql_connect("localhost","ivan","123456");

или для пользователя по умолчанию:

$db=mysql_connect("localhost","root","");

Функция mysql_close([$db_identifier]) закрывает соединение с сервером MySQL. Возвращает TRUE, в случае успешного завершения, или FALSE, в случае возникновения ошибки. Использование mysql_close() не обязательно для непостоянных соединений (они автоматически закрываются в конце скрипта).

Корректная установка соединения может выглядеть следующим образом:

<?

$db = mysql_connect("localhost","login","password"); If (!$db) {

exit("Невозможно установить соединение: ". mysql_error());} else

{echo "Соединение установлено"; mysql_close($db);}

?>

После установки соединения происходит выбор базы данных. mysql_select_db($database_name, [$db_identifier]) выбирает для работы указанную базу данных $database_name на сервере, на который ссылается переданный указатель $db_identifier. Если параметр указателя опущен, используется последнее открытое соединение. Если нет ни одного открытого соединения, функция попытается соединиться с сервером аналогично функции mysql_connect(), вызванной без параметров. Пример: mysql_select_db(«my_db»,$db);

Запрос к базе данных, поступающий из сценария PHP, по сути представляет собой команду MySQL, заключенную в функцию mysql_query(). Именно данная функция позволяет организовать основные операторы SQL – SELECT, INSERT, UPDATE, DELETE. Общий вид функции: mysql_query($query, [$db_identifier]), где $query – это строка с запросом MySQL, $db_identifier – указатель на соединение с базой данных.

Если указатель не указан, то используется последнее открытое соединение. Только для запросов SELECT mysql_query() возвращает указатель на результат запроса или FALSE, если запрос не был выполнен. В остальных случаях (INSERT, UPDATE, DELETE), mysql_query() возвращает TRUE, в случае успешного запроса, и FALSE, в случае ошибки. Значение не равное FALSE говорит о том, что запрос был выполнен успешно. О количестве затронутых или возвращённых рядов запрос не сообщает. Вполне возможна ситуация, когда успешный запрос не затронет ни одного ряда.

Пример обработки запроса – результат помещается в переменную $result для дальнейшей обработки:

$result = mysql_query("SELECT * FROM articles", $db);

В некоторых случаях, особенно когда добиться правильного написания sql-запроса сразу не удается, рекомендуется оформлять строку с запросом в виде отдельной переменной, например $query. В этом случае всегда можно распечатать запрос и протестировать его в панели phpMyAdmin.

$query = "SELECT id, title, author FROM articles"; print_r($query);

$result = mysql_query($query);

После получения информации о выполнении запроса необходимо обработать данные на стороне PHP. При этом удобно, когда данные, полученные по запросу, оформляются в виде массива.

Функция mysql_fetch_row – возвращает строку (результат запроса) в виде массива с числовым индексом.

Функция mysql_fetch_array – возвращает строку (результат запроса) в виде ассоциативного массива. Ключевыми индексами такого массива являются названия полей в таблице mysql.

Функция mysql_result – возвращает один элемент массива данных. Подходит для обработки агрегатных запросов.

Обработка всех данных, полученных по запросу, с использованием функций mysql_fetch_row() и mysql_fetch_array() осуществляется в циклических конструкциях. Можно использовать цикл while, do-while или for для этих целей. Далее приведем пример обработки запроса с помощью цикла while.

<?php

$sql = "SELECT * FROM articles";

$result = mysql_query($sql);

while ($myrow = mysql_fetch_array($result)) {

echo $myrow["id"] . " " . $myrow["title"] . " ".

$myrow["author"] . "<br>";

echo $myrow["desription"] . "<br>";

}

?>

Если планируете использовать цикл for, то важно понимать, как можно заранее получить общее количество записей по запросу. Сделать это можно с помощью функции mysql_num_rows().

Пример использования

<?

$db=mysql_connect("localhost","root",""); mysql_select_db("my_db",$db);

$sql = "select id, title, description, author, date_created from articles";

$result=mysql_query($sql,$db);

$count = mysql_num_rows($result); For ($i=0; $i<=$count; $i++) {

$myrow=mysql_fetch_array($result);

echo $myrow["id"] . " " . $myrow["title"] ." ". $myrow["author"] . "<br>";

echo $myrow["desription"]."<br>";

}

?>

Для удобства разработчиков были введены Функции возврата сообщений об ошибках из базы данных.

• mysql_error([$db_identifier]) – возвращает текст ошибки последней операции с MySQL.
• mysql_errno([$db_identifier]) – численное значение сообщения об ошибках от предыдущей операции MySQL.

По этому коду можно расшифровать тип возникающей ошибки. Так, код ошибки 1049 сообщает, что имя базы данных неизвестно (возможно, при написании кода была допущена ошибка в имени или на данном сервере в принципе нет такой базы данных); код 1146 – указанной таблицы не найдено в базе данных.

<?php

$db = mysql_connect("localhost", "root", ""); mysql_select_db("abcdefg", $db);

echo mysql_errno($db) . ": " . mysql_error($db). "<br>";

mysql_select_db("my_db", $db); mysql_query("SELECT * FROM abcdefg", $db);

echo mysql_errno($db) . ": " . mysql_error($db) . "<br>";

?>

Результатом выполнения данного примера будет что-то подобное:

1049: Unknown database 'abcdefg'

1146: Table 'my_db.abcdefg' doesn't exist