Урок 19. Использование базы данных MySQL

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

Использование MySQL

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

В PHP 5 добавили новое расширение — mysqli, в котором поддерживаются новые возможности, начиная с версии MySQL 4.1 и выше. Кроме того, его можно использовать в объектно-ориентированном стиле. Здесь мы рассматриваем классическое расширение mysql. Оно все еще популярно на большинстве Web-серверов и поддерживается PHP 5.

Иногда нужно использовать mysqli вместо mysql. Для этого нужно заменить префикс mysql на mysqli. Но в большинстве случаев они ведут себя одинаково.

Соединение с базой данных MySQL

Чтобы соединиться с базой данных MySQL, используется функция mysql_connect. На вход нужно подать три параметра: адрес сервера, имя пользователя и пароль. В большинстве случаев сервер MySQL работает на том же сервере, где и PHP. Поэтому для адреса сервера подходит параметр localhost. Обычно выражение mysql_connect выглядит так:

$db = mysql_connect(“localhost”, “chris”, “mypasswod”);

Адрес сервера базы данных. MySQL использует аутентификацию на основе адреса сервера. Поэтому нужно задать адрес, с которым разрешено соединение. Например, MySQL-сервер запущен на www.domen.com, а соединение разрешено только для адреса localhost.
Если MySQL-сервер находится на том же сервере, что и PHP, в большинстве случаев подойдет значение localhost.

Функция mysql_connect возвращает идентификатор соединения с базой данных, который присваивается переменной $db в примере выше. Идентификатор используется как аргумент при вызове других функций MySQL.

Отметим, что в параметрах соединения для mysql_connect нет имени базы данных. Просто выбор базы является вторым шагом после соединения с MySQL-сервером и выполняется с помощью функции mysql_select_db. Например, в выражении ниже выбирается база данных mydb:

mysql_select_db(“mydb”, $db);

Идентификатор соединения. Аргумент $db является необязательным для my_select_db и других функций MySQL. Если его опустить, PHP использует последнее соединение с MySQL. Но для понятности кода лучше добавлять идентификатор соединения в функции MySQL.

После вызова mysql_select_db все последующие операции SQL выполняются с выбранной базой данных.

После окончания работы с MySQL в сценарии нужно закрыть соединение и освободить занятые ресурсы с помощью функции mysql_close:

mysql_lose($db);

Выполнение SQL-запросов

Чтобы выполнить SQL-выражение в MySQL, нужно передать на вход функции mysql_query два аргумента: строку запроса и необязательный идентификатор соединения.

В примере ниже выполняется SQL-запрос CREATE TABLE в базе данных MySQL для идентификатора $db:

$sql = “CREATE TABLE mytable (col1 INT, col2 VARCHAR(10))”;
mysql_query($sql, $conn);

Если запустить этот сценарий в браузере и проверить содержимое базы данных MySQL, можно увидеть, что появилась новая таблица mytable.

Все запросы к базе данных выполняются с помощью mysql_query. Не важно, нужно ли внести изменение в данные или получить несколько строк.

Команды, которые изменяют базу данных

Выше в уроке приводился пример с выражением CREATE TABLE. Другие выражения, определяющие структуру базы (DDL-выражения в SQL), выполняются так же: если нет ошибок, сообщение не выводится. Про обработку ошибок рассказывается ниже в этом уроке.

Запросы с DELETE, INSERT или UPDATE (DML-выражения в SQL) позволяют манипулировать данными в строках таблицы. Для того чтобы узнать, сколько строк изменилось, используется функция mysql_affected_rows. Ниже показывается, как это работает для запроса с оператором UPDATE:

$sql = “UPDATE mytable SET col2 = 'newvalue' WHERE col1 > 5”;
mysql_query($sql, $conn);
echo “Было обновлено ” . mysql_affected_rows($conn) . “ строк(а)”;

Функция mysql_affected_rows требует идентификатор соединения с базой данных и возвращает количество измененных последним запросом строк. Количество затронутых строк в UPDATE может не совпадать с количеством строк, выделенных выражением WHERE. MySQL не обновляет строку, если новое значение совпадает со старым.

Удаление всех строк. Если выполнить запрос DELETE без условия WHERE, функция mysql_affected_rows возвращает нулевое значение, независимо от количества удаленных строк. MySQL просто очищает всю таблицу вместо поочередного удаления всех строк. Поэтому никакого подсчета не происходит.

Извлечение полученных данных

Выражение SELECT возвращает одну или больше строк из базы данных, а PHP предоставляет набор функций для того, чтобы получить доступ к этим данным. Для того чтобы работать с выбранными данными, нужно получить идентификатор ресурса. Его возвращает функция mysql_query:

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

Нельзя непосредственно работать с данными в $res. Для этого нужно передать это значение функциям для извлечения данных.

Можно использовать функцию mysql_result, чтобы обратиться к элементу данных из определенной строки и столбца в полученном результате. Это очень удобно, если запрос возвращает только одно значение, например результат объединяющих функций.

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

$sql = “SELECT SUM(col1) FROM mytable”;
$res = mysql_query($sql, $conn);
echo mysql_result($res, 0, 0);

Функции mysql_result нужно передать три аргумента: идентификатор результата, номер строки и номер столбца. Нумерация столбцов и строк начинается с нуля. Поэтому в примере выше ищется первая строчка в первом столбце по всему множеству значений. Особенность объединяющих функций в том, что они обязательно возвращают одно значение, даже если таблица пуста. Поэтому на выходе всегда получим строку и столбец с результатом. Попытка получить доступ к несуществующей строке или столбцу вызовет сообщение об ошибке.

Функция mysql_num_rows возвращает количество строк, полученных в запросе. Это значение можно использовать в цикле с функцией mysql_result, которая позволяет получить каждую строку в результате. Ниже приводится реализация этого механизма:

$sql = “SELECT col1, col2 FROM mytable”;
$res = mysql_query($sql, $db);
for ($i=0; $i < mysql_num_rows($es); $i++) {
    echo “сol1 = ” . mysql_result($res, $i, 0);
    echo “сol2 = ” . mysql_result($res, $i, 1) . “<br>”;
}

В этом примере известно положение столбцов col1 и col2, поэтому можно воспользоваться mysql_result с числовыми значениями, чтобы установить их по очереди.

Название полей. В функции mysql_result можно использовать строку как аргумент номера столбца. В этом случае нужно указать имя столбца. Это очень удобно для запросов SELECT *, где не всегда известен порядок следования столбцов, а также для запросов, где неудобно работать с числовыми значениями столбцов.

Получение всей строки из данных

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

Последовательно вызывая mysql_fetch_array, можно получить все строки запроса. После извлечения всех строк функция возвращает FALSE.

С помощью функции mysql_fetch_array можно создать удобную циклическую структур, как показывается ниже:

$sql = “SELECT col1, col2 FROM mytable”;
$res = mysql_query($sql, $conn);
while ($row = mysql_fetch_array($res)) {
    echo “col1 = ” . $row[“col1”];
    echo “, col2 = ” . $row[“col2”] . “<br>”;
}

Все строки данных извлекаются по очереди при очередной итерации цикла. Результирующая строка данных заносится в массив и не требует вызова других функций.

Полученный массив содержит данные с цифровыми и ассоциированными индексами. В этом примере известно, что col1 – первый столбец, поэтому $row[“col1”] и $row[0] содержат одинаковое значение.

Такой механизм позволяет получить последовательный доступ ко всем строкам в полученном результате. Кроме того, произвольный доступ реализуется с помощью функции mysql_data_seek. Для этого нужно указать номер строки и перейти к ней перед вызовом mysql_fetch_array.

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

mysql_data_seek($res, 9);

Если после этого нужно перейти в начало набора данных, нужно найти нулевую строку:

mysql_data_seek($res, 0);

Если вызвать mysql_data_seek с номером строки, который превышает самый большой из полученных данных, появится сообщение об ошибке. Нужно сначала узнать количество полученных строк с помощью mysql_num_rows, чтобы найти точное значение.

Поиск. Чтобы перейти к последней строке результата, нужно вызвать mysql_data_seek($res, mysql_num_rows($res)-1), потому что номер последней строки на единицу меньше общего количества строк в результате. Это намного проще реализовать с помощью оператора изменения порядка сортировки ORDER BY в SQL-запросе. Теперь вместо последнего нужно выбрать первый элемент.

Отладка SQL

Иногда при работе с MySQL случаются ошибки. Но сообщения об ошибках могут не содержать полную информацию о причине. В следующем разделе рассматривается способ реализации улучшенного механизма уведомления об ошибках. Это позволит быстро отлаживать слой логики базы данных.

Ошибки SQL

Если происходит ошибка в SQL-выражении, она не выводится сразу. Чтобы определить ошибку, нужно проверить возвращаемое значение mysql_query. Значение NULL возвращается, если происходит ошибка. Это относится как к DDL- так и DML-выражениям, включая SELECT.

Ниже делается попытка выполнить неправильное SQL-выражение (пропущено название таблицы в команде DELETE):

$sql = “DELETE FROM”;
$res = mysql_query($sql, $db);
if (!$res) {
    echo “Обнаружена ошибка в SQL”;
    exit;
}

Если нужно найти, почему не удался вызов mysql_query, необходимо использовать функции mysql_error и mysql_errno. Они возвращают, соответственно, сообщение об ошибке MySQL и номер кода ошибки. Необязательный аргумент идентификатора соединения нужно указывать, если в сценарии открыто несколько MySQL-соединений:

if (!$res) {
    echo “Ошибка ” . mysql_errno() . “ в SQL”;
    echo “<:PRE>$sql</PRE>”;
    echo mysql_error();
    exit;
}

Отладка SQL. При отладке SQL, очень удобно видеть приведший к ошибке запрос. Особенно если в нем используется подстановка переменных. Это легко сделать, если запрос содержится в переменной, аналогично $sql в примерах выше. Поэтому лучше не задавать его напрямую в mysql_query.

Если в сценарии не обнаружено ошибок SQL, PHP продолжает выполнение. Оно может прерваться, если подставить ложный идентификатор ресурса. При вызове mysql_result с неправильным $res появится следующее сообщение об ошибке:

Warning: mysql_result(): supplied argument is not a valid MySQL result resource in /home/chris/mysql.php on line 8

В этом сообщении нет информации о причине ошибки. Номер строки относится к mysql_result, а не mysql_query. Чтобы найти причину проблемы, нужно просмотреть сценарий выше.

Ошибки соединения

Если при соединении с базой данных MySQL происходит ошибка, PHP выводит сообщения об этом. Ниже показан пример, где неправильно задан пароль и имя домена.

Warning: mysql_connect(): Access denied for user 'root@localhost' (using password: YES) in /home/chris/connect.php on line 3

Warning: mysql_connect(): Unknown MySQL server host 'local-host' (1) in /home/chris/connect.php on line 3

Эти предупреждения генерируются PHP и позволяют выявить причину. Если нужно, можно увидеть реальное сообщение об ошибке и ее код с помощью mysql_error и mysql_errno.

Например, если запрещен вывод ошибок на экран, то удобно записывать информацию об ошибке в файл журнала. Можно определить, что соединение не удалось, если идентификатор соединения равен NULL.

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

$db = mysql_connect(“localhost”, “chris”, “mypassword”);
if (!$db) {
    echo “При соединении произошла следующая ошибка ” . mysql_errno() . “<br>”;
    echo “Сообщение об ошибке: ” . mysql_error();
    exit;
}

Пароли. Если указать неправильный пароль для соединения ни PHP, ни mysql_error не выводят его на эран.

Резюме

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