Работа с БД MySQL

KOPOJI · Регистрация: 12.06.2012

Author24 — интернет-сервис помощи студентам

Работа с БД MySQL в PHP

Вступление

Приветствую. В последнее время у новичков, только начинающих изучать mysql и php стали возникать вопросы "Как работать с mysql в php" и вопросы об ошибках, в которых на самом деле четко и ясно описывается где и в чем ошибка. Я лично (да и не только я) не советую использовать расширение mysql для работы с БД MySQL - лучше использовать PDO (лучший вариант на мой взгляд) ну или хотя бы mysqli - mysql уже давно объявлена как устаревшая, и ее не убирают только из-за частого использования новичками. Я попробую немного раскрыть эту тему, чтобы вам было от чего отталкиваться.
Итак, начнем.

Содержание #

Работа с БД MySQL через расширение mysql

1. Соединение, выбор базы для работы и установка кодировки
2. Основы SQL-синтаксиса
3. PHP и MySQL
4. Ошибки при работе с БД

Работа с БД MySQL через расширение mysqli

1. Процедурный подход
- Соединение с сервером MySQL
- Выполнение запросов
2. Объектно-ориентированный подход

Подведение итогов

Работа с БД MySQL через расширение mysql

Коротко о главном. Расширение mysql уже давно считается устаревшим и не рекомендуется к использованию. На оффициальном сайте http://php.net уже давно грозятся убрать это расширение. Этого до сих пор еще не сделали только по той причине, что многие его до сих пор используют. Я рекомендую использовать для работы с БД MySQL расширение mysqli, или, что еще лучше на мой взгляд - PDO. Но до сих пор у многих возникают вопросы по поводу работы с базами данных через расширение mysql. Поэтому я решил несколько раскрыть основы работы в php и с расширением mysql, и с расширением mysqli

1. Соединение, выбор базы для работы и установка кодировки ▲

Подключение к серверу MySQL ▲

Для начала работы с mysql необходимо подключиться к этому самому серверу БД MySQL. Делается это следующей командой:

PHP

1	$link = mysql_connect('host','user','pass');

где соответственно host - имя хоста (по умолчанию localhost), user - имя пользователя (по умолчанию root) и pass - пароль (по умолчанию пустая строка - '').

Выбор базы данных для работы ▲
Далее необходимо выбрать собственно саму базу, с которой мы будем работать, это делается функцией

PHP

1	mysql_select_db('bd_name');

Где мы указываем нашу базу соответственно.

Установка кодировки для работы ▲

Далее, для правильной кодировки при работе с данными - чтобы не выводились "кракозябры" вместо русских букв нужно установить кодировку для соединения и работы:
В старых версиях mysql (если память не изменяет, до MySQL 5.0) вместо этого писалось

PHP

1	mysql_query("SET NAMES 'utf8'");

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

PHP

1	mysql_set_charset('utf8');

я советую использовать кодировку utf8 (collation-сравнение в базе выбирать utf8_general_ci).

Внимание, важный момент! Все скрипты, таблицы, и сама база должны быть в одной кодировке!!!
Иначе вы получите головную боль при использовании русских символов!

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

Идем дальше. Мы соединились с сервером, выбрали базу данных для работы, установили нужную нам кодировку.
Теперь нам необходимо произвести необходимые нам в данном случае операции с данными, которые у нас хранятся в нашей базе данных.
Запросы выполяются с помощью функции mysql_query():

PHP

1	mysql_query("Текст запроса");

2. Основы SQL-синтаксиса ▲

Основные и самые часто используемые запросы - это SELECT (выбор), INSERT (вставка) и UPDATE (обновление). Теперь несколько подробнее разберем каждый.

2.1 SELECT ▲

допустим, у нас есть таблица фрукты (fruit). Выберем все записи из этой таблицы. SQL-запрос будет выглядеть так:

SQL

1	SELECT * FROM `fruit`

разберем его получше. * означает все поля. Т.е. у нас получается запрос "ВЫБРАТЬ все записи ИЗ `фруктов`"
Если нам не нужно выбирать все поля из таблицы - то можно перечислять необходимые поля через запятую (вернее, не можно, а даже нужно).
И тогда запрос будет выглядеть уже следующим образом:

SQL

1	SELECT `sort`,`category`,`ves` FROM `fruit`

Вы спросите, зачем я название таблицы заключил в косые апострофы? А вот зачем - если название поля или таблицы содержит пробелы, знаки минусов или еще какие-то "левые" - то произойдет ошибка в запросе.
То есть, я советую при запросе всегда указывать mysql-ные названия таблиц и полей в обратных косых апострофах (клавиша тильда, под ESC - буква ё на англ. раскладке). Пример необходимости указания этих апострофов: у нас есть поле e-mail в таблице и мы хотим извлечь e-mail. Составляем запрос:

SQL

1	SELECT e-mail FROM fruits

и все бы ничего, но в mysql минус - это именно минус, т.е. арифметическое действие! И наш запрос будет с ошибкой. Но если указать в обратных косых апострофах название поля, то все нормально выполнится

SQL

1	SELECT `e-mail` FROM fruits

Далее. В запросе можно дополнительно указывать нужные критерии поиска. Это осуществляется ключевым словом WHERE (где). Представим, что нам нужно выбрать все яблоки из фруктов. Запрос будет выглядеть следующим образом:

SQL

1	SELECT * FROM `fruits` WHERE `category`='яблоки'

Обратите внимание, само значение поиска я указал в одинарных апострофах (не косых!!!). И вам я также советую, по аналогичной причине с косыми - если будет минус или пробел стоять, получится ошибка, поэтому лучше сразу привыкайте так делать.
Можно указывать несколько критериев с помощью AND (и), OR (или).

SQL

1	SELECT * FROM `fruits` WHERE `category`='яблоки' OR `category`='груши'

И по аналогии AND

Использование лимита при выборке данных ▲

Также существует возможность использовать лимит (предел). Ключевое слово - LIMIT. После него ставятся либо одна цифра, либо две через запятую. Разберем каждый вариант.
1. Одна цифра:

SQL

1	SELECT * FROM `fruits` LIMIT 5

- выберет только первые пять записей по запросу
2. Две цифры:

SQL

1	SELECT * FROM `fruits` LIMIT 5,10

- выберет записи с 5 по 15 включительно.

2.2 INSERT ▲

Идем далее. INSERT (вставка данных). Существует два варианта записи, мы разберем только один:

SQL

1	INSERT INTO `table_name`(`field1`,`field2`,`field3`) VALUES ('value1','value2','value3')

Разберем что здесь творится

.
Переводим: "ВСТАВИТЬ В имя_таблицы с полями поле1, поле2 и поле3 соответствующие значение1, значение2 и значение3"
Представим что мы хотим вставить новый тип фрукта в нашу таблицу с фруктами (киви, к примеру). Запрос будет выглядеть так:

SQL

1	INSERT INTO `fruits`(`category`) VALUES('киви')

Ну больше тут особо нечего рассказать, идем дальше.

2.3 UPDATE ▲

UPDATE (обновление данных).

SQL

1	UPDATE `table_name` SET `field1`='value1',`field2`='value2',`field3`='value3'

Дословно: "ОБНОВИТЬ `имя_таблицы` УСТАНОВИТЬ `поле1`='значение1',`поле2`='значение2',`поле3`='значение3'"
Но! Мы не указали критерий какую именно запись(строку) обновлять!!! И в результате у нас обновятся ВСЕ записи в таблице.
Нужно указать критерий, используя уже знакомое ключевое слово WHERE. Думаю, с этим проблем не должно возникнуть, не будем останавливаться на этом и перейдем собственно, к самому php.

3. PHP и MySQL ▲

Итак, как же все таки работать в PHP с БД MySQL? Давайте разберем с вами это на примерах

Выполнение запросов в php ▲

как я уже говорил, запросы выполняются с помощью функции mysql_query(). То есть выглядит это так:

PHP

//выбор записей
mysql_query(" SELECT * FROM `fruits` ",$link);
//вставка данных
mysql_query(" INSERT INTO `fruits` (`category`) VALUES ('киви') ",$link);
//обновление записей
mysql_query(" UPDATE `fruits` SET `category`='груши' WHERE `id`='10' ",$link);

проверить способов есть вполне достаточно, самый распространенный - дописать вывод ошибки и прерывание дальнейшей работы скрипта:

PHP

1	mysql_query("запрос",$link) or die("ERROR: ".mysql_error());

Разберем чуть подробнее саму функцию mysql_query() и что она возвращает.
Вы спросите, зачем я писал $link после запроса. Это указатель на нужное соединение. Он не обязательный, если его не указать, то функция будет пытаться использовать последнее открытое соединение.

3.1 Возвращаемые значения ▲

Теперь о возвращаемых значениях. Для запросов SELECT, SHOW, EXPLAIN и DESCRIBE функция возвращает указатель на результат запроса при успехе, при неудаче - возвращает FALSE. Для всех остальных запросов (INSERT,UPDATE, и т.д.) функция возвращает TRUE в случае успеха и FALSE в случае ошибки.

3.2 Обработка полученного ресурса ▲

Для вывода полученных данных при запросе SELECT, SHOW, EXPLAIN или DESCRIBE необходимо обработать этот самый указатель
для этого используют fetch-функции. Две самые распространенные из них - это mysql_fetch_assoc() и mysql_fetch_array().
Первая возвращает ассоциативный массив, вторая несколько массивов сразу (если не нужны числовые индексы и т.п., то рекомендую использовать первую).

Типы возвращаемых массивов mysql_fetch_array() ▲

mysql_fetch_array() по умолчанию возвращает несколько массивов сразу, как числовой, так и ассоциативный. Можно указать какой именно нужен
всего есть три типа:

MYSQL_NUM	возвращается массив с числовыми индексами
MYSQL_ASSOC	возвращается ассоциативный массив (со строковыми индексами)
MYSQL_BOTH	возвращаются оба массива - по умолчанию

Если у нас результат запроса указывает только на одну запись, то достаточно просто

PHP

1
2
3

$res = mysql_query("SELECT * FROM `test` LIMIT 1");
$row = mysql_fetch_assoc($res);
print_r($row);

и в массиве $row у нас будут содержаться значения из нашей таблицы. Если их несколько должно быть (записей), то необходимо выводить их в цикле, потому что fetch-функции считывают построчно. Есть несколько способов вывода в цикле, через for, do-while, foreach.. На мой взгляд, лучший вариант - цикл while.
Внимание!!! Не используйте цикл do - while для вывода, если этого жестко не требуют условия!!

PHP

$res = mysql_query("SELECT * FROM `test`");
while($row = mysql_fetch_assoc($res)) {
    print_r($row);
}

и здесь на каждой итерации цикла (на каждом новом проходе цикла) переменной $row будет присваиваться массив с новыми значениями.

4. Ошибки ▲

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

Частые ошибки ▲

Теперь немного поговорим по поводу ошибок. Вспомним, что возвращает в случае неудачи mysql_query() и подойдем к, наверное, самой распространенной ошибке. Выглядит она так:

Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in...

FALSE/TRUE - это булев тип, логический, т.е. boolean. И означает эта ошибка как раз что одна из используемых функций выше вернула false вместо результата. как раз вывод ошибки с прекращением дальнейшего выполнения скрипта (я писал об этом выше) и выдаст ошибку, в которой выведет что не так:

No database selected - не выбрана база данных

table test.fruits doesn't exists - таблица fruits в базе данных test не существует

column count doesn't match value count at row 1 - число перечисленных полей не соответствует числу перечисленных значений

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line ... - проверьте, не пропустили ли вы где то апостроф, запятую или другой символ лишний поставили - в общем, пунктуацию

Access denied for user 'root'@'localhost' (using password: NO) - отказано в доступе для пользователя root через localhost с пустым паролем

MYSQL Error: Access denied for user 'ODBC'@'localhost' (using password: NO) - отказано в доступе для пользователя ODBC на локалхост с пустым паролем - вы не указали mysql_connect() или ее без аргументов написали: по умолчанию, mysql пытается соединиться под пользователем ODBC на localhost с пустым паролем.

Еще несколько слов об ошибках ▲

Решил добавить еще несколько слов по поводу ошибок при запросах к БД

Самое главное: НИКОГДА при отладке программы не используйте так называемые "собаки" - @ Вы от этого не выиграете, а только потеряете. Даже наоборот, при отладке программы, я настойчиво рекомендую в начале скрипта прописывать эти две строчки

PHP

1 2	ini_set('display_errors','On'); error_reporting(E_ALL\|E_STRICT);

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

"В настоящий момент сервер недоступен. Приносим извинения за причиненные неудобства"

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

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

У нас есть форма с полями ввода логина и пароля и кнопкой - по нажатию мы соответственно пытаемся найти этого пользователя у нас в базе. Естественно, переменные мы должны очищать и проверять перед запросом.
НИКОГДА не доверяйте любым данным, которые получены от пользователя и всегда их проверяйте и очищайте! Поверьте, от этого еще никогда хуже не было.

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

Отладка ▲

1. Нужно проверить все переменные, пустые они или нет (и вообще, инициализированы ли они) - функция

PHP

1	var_dump($имя_переменной);

неинициализированная переменная в php - это NULL. при выводе его через echo или print/print_r мы ничего не увидим. Но эта функция выдаст нам всю информацию - то есть выведет это самое NULL

2. Если вам лень это делать (как часто мне бывает) - можно перед запросом непосредственно к БД вывести сам этот запрос на экран, и посмотреть что у нас уходит в базу, какой запрос.

PHP

$login = $_POST['login']; //к примеру admin
$pass = $_POST['pass']; //к примеру 12345
$query = "SELECT count(`id`) FROM `users` WHERE `login`='$loggin' AND `pass`='$pass'";
echo $query;exit;
//дальше выполняется запрос

И например если мы случайно ошиблись в имени переменной (допустим, логин как в примере) и уровень ошибок по умолчанию (не писали в начале скрипта вывод всех ошибок) - то мы увидим в итоге такой запрос:

SQL

1	SELECT COUNT(`id`) FROM `users` WHERE `login`='' AND `pass`='12345'

И уже сразу будет видно, где и в чем ошибка.

3. Если все нормально, и все переменные хорошо видно - можно зайти в PHPMyAdmin и вручную (на вкладке Выполнить SQL-запрос) попробовать выполнить этот запрос с подставленными нужными значениями.

4. Если запрос успешно выполняется - то либо дописать к запросу перед точкой с запятой как я уже писал

PHP

1	or die(mysql_error())

либо, что еще лучше - написать что-то наподобие такого:

PHP

$res = mysql_query($query); 
if($res) { //или if(TRUE === $res) { .. - так будет правильнее. Но никак не в апострофах!
    // запрос успешно выполнен
    echo 'Ваш запрос успешно выполнен';
}
else
    die('Ошибка в запросе! Текст ошибки: ".mysql_error());

и тогда мы увидим ошибки, одну из которых я, скорее всего, уже перечислил выше (как и то, что она означает).

Работа с БД MySQL через расширение mysqli ▲

Собственно, в продолжение темы. В самом начале я написал о том, что не рекомендуется использовать расширение mysql для работы с MySQL.
Поэтому я решил написать немного о других расширениях для работы с MySQL.
В php есть возможность работать (помимо mysql) еще через mysqli и PDO. поэтому здесь я вкратце расскажу о mysqli (mysqlimproved).
Mysqli по синтаксису схожа с mysql - это, в общем-то, улучшенное расширение mysql, как видно из перевода.
Итак, продолжим.

1. Процедурный подход ▲
Mysqli, в отличии от mysql, поддерживает как процедурный, так и ООП подход.
Начнем с процедурного.

1.1. Соединение с сервером ▲

PHP

$link = mysqli_connect('имя_хоста','имя_пользователя','пароль','имя_базы_данных');
//проверка соединения:
if(mysqli_connect_errno())
    die('Ошибка соединения: '.mysqli_connect_error()); //или if(!$link) {..
else { //если успешно
    //здесь выполняем весь остальной код
}

В mysqli, в отличии от mysql обязательно указывать первым параметром идентификатор соединения

1.2. Выполнение запросов ▲

Как я уже говорил, синтаксис очень схожий

PHP

$res = mysqli_query($link,"SELECT * FROM `table_name`");
if($res) { //если запрос успешный
    while($row = mysqli_fetch_assoc($res)) {
        //выводим как нам надо
        print_r($row);
    }
    mysqli_free_result($res); //очищаем занятую память - она уже не нужна
}
mysqli_close($link);

Вот, в-принципе, и все.. Также при использовании mysqli доступен ООП подход

2. ООП подход ▲

1.1. Соединение с сервером ▲

PHP

$link = new mysqli('имя_хоста','имя_пользователя','пароль','имя_базы_данных');
if(mysqli_connect_errno())
    die('Ошибка соединения: '.mysqli_connect_error());
else { //если успешно
    //здесь выполняем весь остальной код
}

1.2. Выполнение запросов ▲

PHP

$res = $link->query("SELECT * FROM `table_name`");
if($res) { //если успешный
    while($row = $res->fetch_assoc()) {
        print_r($row);
    }
    $res->close(); //очищаем занятую память - она уже не нужна
}
$link->close();

3. Плюсы mysqli ▲

Ну, а теперь о главном. Вы наверное спросите, а в чем же плюс у mysqli или PDO перед mysql?

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

Во-вторых, поддержка нескольких одновременных запросов

И, в-третьих, наверное, самое главное - возможность подготавливания выражений к запросу без кучи всевозможных очисток, которые при использовании mysql просто необходимы.

3.1 Подготовленные выражения для запроса ▲

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

PHP

$login = " ' or 1=1 -- f "; //это то, что нам ввели в запросе - пример sql-инъекции
$pass = '12345';
$link = new mysqli('имя_хоста','имя_пользователя','пароль','имя_базы_данных');
//проверка соединения:
if(mysqli_connect_errno())
    die('Ошибка соединения: '.mysqli_connect_error()); //или if(!$link) {..
else { //если успешно
    //здесь выполняем весь остальной код
    //подготавливаем выражение для запроса
    $query = $link->prepare("SELECT * FROM `table_name` WHERE `login`=? AND `pass`=?");
    //указываем, что подставлять в выражение
    /* здесь каждый следующий символ - следующая переменная, типы:
    i - целочисленный, d- double и float, b- BLOB и s - все остальное, что нам сейчас и нужно: */
    $query->bind_param('ss',$login,$pass); //указываем переменные для подстановки
    $query->execute(); //выполняем запрос
    $res = $query->get_result(); //получаем данные запроса и выводим соответственно..
    while($row = $res->fetch_assoc()) {
        print_r($row);
    }
}

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

Поэтому тот логин не пройдет, пока не введешь правильный. И возможность sql-инъекций сводится к нулю.
P.S. тоже самое возможно и на процедурном подходе, просто мне лично удобнее такой синтаксис

Подведение итогов ▲

На этом пока что все, мы разобрали основы синтаксиса, посмотрели почему лучше использовать другие расширения для работы с базами данных сервера MySQL. На всякий случай еще раз повторяю - даже на официальном сайте написано о том, что данное расширение (mysql) считается устаревшей и в любой момент может быть отключена, т.е. помечена как deprecated и вы не сможете ее использовать:

Предлагаемые альтернативы

Не рекомендуется использовать это расширение. Используйте вместо него MySQLi или PDO_MySQL. Смотрите также инструкцию MySQL: выбор API и соответствующий FAQ для получения более подробной информации. Альтернативы для данной функции:

mysqli_query()
PDO::query()

Ну, на этом вроде все..
Основы синтаксиса, основы взаимодействия MySQL с php через расширения mysql и mysqli, и самые часто встречающиеся ошибки мы разобрали..

Желаю успехов в программировании

@Kerry_Jr · 25.02.2017, 23:22

KOPOJI, предлагаю часть про mysql_... полностью исключить из данного гайда.

KOPOJI · 26.02.2017, 00:20 **[ТС]**

Темы со старым мускулом еще встречаются. А главное, что из-за ограничения на количество символов просто отредактировать не получится, надо будет разбивать по постам, а мне лень..

@Kerry_Jr 3106 / 2591 / 1219 Регистрация: 14.05.2014 Сообщений: 7,236 Записей в блоге: 1
	25.02.2017, 23:22	2
	KOPOJI, предлагаю часть про `mysql_...` полностью исключить из данного гайда. 0

KOPOJI Почетный модератор 16844 / 6723 / 880 Регистрация: 12.06.2012 Сообщений: 19,967
	26.02.2017, 00:20 [ТС]	3
	Темы со старым мускулом еще встречаются. А главное, что из-за ограничения на количество символов просто отредактировать не получится, надо будет разбивать по постам, а мне лень.. 0