Php и mysqli. примеры запросов
Содержание:
- Начало работы
- Delete Data From a MySQL Table Using MySQLi and PDO
- Поддержка баз данных
- Тестируем работоспособность
- Именованные параметры
- Безопасность
- Technical Details
- Отлавливаем ошибки базы данных
- Объектное отображение
- Функция mysqli connect: соединение с MySQL
- Как достать результат
- Устанавливаем соединение с БД
- PHP MySQLi Functions
- Задачи на PhpMyAdmin
Начало работы
Создадим класс, через который будем выполнять запросы:
Здесь ничего сложного — объявляем класс-обертку над основным классом mysqli, через который и будет выполняться все работа с базой данных. В конструктор мы передаем данные доступа и пробуем подключиться, если что-то не так — бросаем Exception.
Выполним инициализацию класса и проверим, все ли хорошо:
Если в результате видим наше название класса — поздравляем, все прошло успешно и можно двигаться дальше!
Простой SELECT-запрос
Рассмотрим следующий пример SELECT-запроса:
В $selectQuery описываем нужный SQL-запрос. После этого — выполняем запрос к базе данных с помощью метода query объекта mysqli. Если ошибок нету — получим объект mysqli_result, который сохраняется в переменной $resultQuery.
Следует обратить внимание, что объект mysqli_result возвращается только для запросов вида SELECT, SHOW, DESCRIBE и EXPLAIN, для других типов запросов метод query вернет TRUE в случае успешного выполнения. Если же в SQL-запросе присутствуют ошибки — метод query вернет FALSE, который нужно обработать с помощью Exception
Обработка результатов
Результаты выборки из объекта $resultQuery мы можем получить с помощью различных методов:
Выбрать для себя удобный способ обработки результатов выборки Вы всегда можете в официальном PHP-мануале.
Простой SELECT-запрос с экранированием данных
Ни в коем случае нельзя подставлять в запросы данные от пользователя в чистом виде, поскольку можно словить SQL-injection. Для экранирования данных используется функция real_escape_string. Запрос может выглядеть следующим образом:
Подготовленный SELECT-запрос
В чем удобство подготовленных SELECT-запросов — на базу данных можно возложить ответственность за экранирование данных. Механизм приблизительно следующий:
Пояснение к примеру. Если подготовка запроса проходит успешно (нет ошибок в SQL-запросе) — тогда с помощью метода bind_param подвязываются переменные. Первый параметр содержит строку с типами переменных, последующие параметры — сами переменные, которые нужно «подставить» в запрос.
В нашем примере использовалась одна переменная, тип переменной представлен буквой i, что означает целое число. Всего можно подвязать 4 типа переменных:
- i — целое число.
- d — дробное число.
- s — строковые данные.
- b — двоичный объект (blob).
Примечание: метод $mysqliStmt->get_result() не будет работать, если не подключен mysqlnd-драйвер. Не все хостинги сайтов на сегодняшний день конфигурируют mysqlnd в минимальные пакеты, поэтому нужно быть внимательным!
Подготовленный SELECT-запрос с произвольным набором переменных для экранирования
Рано или поздно возникает ситуация, когда количество данных для экранирования может быть динамическим, то есть изменяется под влиянием каких-то условий пользовательского интерфейса.
Пример такого SELECT-запроса:
Выполнение INSERT, UPDATE и DELETE запросов
Запросы на обновление данных (INSERT, UPDATE, REPLACE, DELETE) выполняются аналогично запросам SELECT. Например:
Delete Data From a MySQL Table Using MySQLi and PDO
The DELETE statement is used to delete records from a table:
DELETE FROM table_name
WHERE some_column = some_value
Notice the WHERE clause in the DELETE syntax: The WHERE clause
specifies which record or records that should be deleted. If you omit the WHERE
clause, all records will be deleted!
To learn more about SQL, please visit our SQL tutorial.
Let’s look at the «MyGuests» table:
id | firstname | lastname | reg_date | |
---|---|---|---|---|
1 | John | Doe | john@example.com | 2014-10-22 14:26:15 |
2 | Mary | Moe | mary@example.com | 2014-10-23 10:22:30 |
3 | Julie | Dooley | julie@example.com | 2014-10-26 10:48:23 |
The following examples delete the record with id=3 in the «MyGuests» table:
Example (MySQLi Object-oriented)
<?php$servername = «localhost»;$username = «username»;$password = «password»;$dbname = «myDB»;// Create connection$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die(«Connection failed: » . $conn->connect_error);}
// sql to delete a record
$sql = «DELETE FROM MyGuests WHERE id=3»;if ($conn->query($sql) === TRUE) { echo «Record deleted successfully»;} else {
echo «Error deleting record: » . $conn->error;}$conn->close();
?>
Example (MySQLi Procedural)
<?php$servername = «localhost»;$username = «username»;$password = «password»;$dbname = «myDB»;// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
die(«Connection failed: » . mysqli_connect_error());}// sql to delete a record
$sql = «DELETE FROM MyGuests WHERE id=3»;if (mysqli_query($conn, $sql)) { echo «Record deleted successfully»;} else {
echo «Error deleting record: » . mysqli_error($conn);}mysqli_close($conn);
?>
Example (PDO)
<?php$servername = «localhost»;$username = «username»;
$password = «password»;$dbname = «myDBPDO»;try { $conn = new PDO(«mysql:host=$servername;dbname=$dbname», $username, $password);
// set the PDO error mode to exception $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// sql to delete a record
$sql = «DELETE FROM MyGuests WHERE id=3»; // use exec() because no results are returned $conn->exec($sql);
echo «Record deleted successfully»;} catch(PDOException $e) { echo $sql . «<br>» . $e->getMessage();
}$conn = null;
?>
After the record is deleted, the table will look like this:
id | firstname | lastname | reg_date | |
---|---|---|---|---|
1 | John | Doe | john@example.com | 2014-10-22 14:26:15 |
2 | Mary | Moe | mary@example.com | 2014-10-23 10:22:30 |
Поддержка баз данных
Ключевым преимуществом PDO перед MySQLi является его могучая поддержка различных баз данных. На момент написания урока PDO может использовать 12 драйверов. А MySQLi — поддерживает только MySQL.
Чтобы распечатать список поддерживаемых драйверов PDO можно использовать следующий код:
var_dump(PDO::getAvailableDrivers());
Что означает разница в количестве поддерживаемых баз данных? Для ситуаций, когда в проекте надо перейти на использование другой базы данных, PDO позволит сделать процесс прозрачным. Все, что нужно будет сделать — изменить строку соединения и несколько запросов, если какие-либо методы не поддерживаются новой базой данных. В случае с MySQLi придется переписывать весь код, включая запросы.
Тестируем работоспособность
Прежде чем начать работу, следует протестировать работоспособность —
попробуем сделать какой-нибудь запрос к нашей базе.
Просто скопируйте этот код и запустите его у себя:
Если var_dump($result) вернет resource,
то все работает, если же null – то возникли какие-то проблемы.
в новых версиях PHP в $result будет лежать объект с данными (всегда будет непустой).
Если обработать его через mysqli_fetch_assoc будет или результат или null (про mysqli_fetch_assoc
см ниже или видео https://www.youtube.com/watch?v=J8GFuyA_k_8 ,
которое уже было выше в пункте «Устанавливаем соединение»)
В таком случае проверьте все еще раз,
уберите последовательно все ошибки PHP, если таковые есть.
Именованные параметры
Другой важной отличительной особенностью PDO являются именованные параметры, которые делают процедуру привязывания существенно проще:
$params = array(':username' => 'test', ':email' => $mail, ':last_login' => time() - 3600); $pdo->prepare(' SELECT * FROM users WHERE username = :username AND email = :email AND last_login > :last_login'); $pdo->execute($params);
Сравните с MySQLi:
$query = $mysqli->prepare(' SELECT * FROM users WHERE username = ? AND email = ? AND last_login > ?'); $query->bind_param('sss', 'test', $mail, time() - 3600); $query->execute();
Знак вопроса может показаться короче, чем символьные имена. Однако, теряется гибкость и устойчивость к ошибкам, так как разработчику нужно помнить о порядке следования параметров, что в большинстве случаев представляет собой зону с очень высокой вероятностью трудно распознаваемых ошибок.
К сожалению, MySQLi не поддерживает именованные параметры.
Безопасность
Обе библиотеки предоставляют средства для защиты от SQL инъекций, которые разработчики могут использовать по своему усмотрению.
Допустим, злодей пытается встроить вредный запрос SQL через параметр ‘username’ HTTP запроса (GET):
$_GET = "'; DELETE FROM users; /*"
Если пропустить такое выражение, то оно будет включено в запрос в том виде, как есть – запрос удаляет все строки из таблицы (и PDO и MySQLi поддерживают множественные запросы).
// PDO, "ручная" зачистка параметра $username = PDO::quote($_GET); $pdo->query("SELECT * FROM users WHERE username = $username"); // MySQLi, "ручная" зачистка параметра $username = mysqli_real_escape_string($_GET); $mysqli->query("SELECT * FROM users WHERE username = '$username'");
Метод не только отбрасывает лишние символы в строке, но и заключает ее в кавычки. А функция только отбрасывает лишние символы в строке, а в кавычки ее надо будет помещать вручную.
Другой метод защиты:
// PDO, подготовленные выражения $pdo->prepare('SELECT * FROM users WHERE username = :username'); $pdo->execute(array(':username' => $_GET)); // mysqli, подготовленные выражения $query = $mysqli->prepare('SELECT * FROM users WHERE username = ?'); $query->bind_param('s', $_GET); $query->execute();
Лучше использовать подготовленные выражения, чем метод и функцию .
Technical Details
Return Value: | For successful SELECT, SHOW, DESCRIBE, or EXPLAIN queries it will return a mysqli_result object. For other successful queries it will return TRUE. FALSE on failure |
---|---|
PHP Version: | 5+ |
PHP Changelog: | PHP 5.3.0 added the ability for async queries |
Example — Procedural style
Perform query against a database:
<?php
$con = mysqli_connect(«localhost»,»my_user»,»my_password»,»my_db»);if (mysqli_connect_errno()) {
echo «Failed to connect to MySQL: » . mysqli_connect_error();
exit();
}// Perform query if ($result = mysqli_query($con, «SELECT * FROM Persons»))
{ echo «Returned rows are: » . mysqli_num_rows($result);
// Free result set mysqli_free_result($result);}
mysqli_close($con);
?>
Отлавливаем ошибки базы данных
Многие начинающие зачастую не умеют отлавливать ошибки,
которые вернула база данных.
Поэтому при работе с БД у них постоянно возникают сложности. Что-то не работает, а что — не понятно,
так как ошибок они не видят, так как
PHP не выводит ошибки mySQL, если ему об этом не сказать
Чтобы вывести ошибки, следует пользоваться конструкцией or die ( mysqli_error($link) ),
которую необходимо добавлять к каждому запросу к БД.
Смотрите пример: mysqli_query($link, $query) or die( mysqli_error($link) );
Таким образом вы сразу будете получать сообщения об ошибках синтаксиса SQL.
Обратите внимание на то, что на рабочем сайте эти конструкции следует удалять,
чтобы пользователи и тем более хакеры не видели ошибок БД.
Объектное отображение
И PDO и MySQLi могут отображать результаты как объекты. Данная особенность может быть полезна в том случае, если вы не хотите использовать уровень абстракций базы данных и желаете реализовать модель объектно-реляционного отображения. Допустим, у нас есть класс с некоторыми свойствами, которые соответствуют именам полей в базе данных:
class User { public $id; public $first_name; public $last_name; public function info() { return '#'.$this->id.': '.$this->first_name.' '.$this->last_name; } }
Без объектного отображения нужно будет заполнять значениями каждое поле (либо вручную, либо с помощью конструктора) прежде, чем можно будет использовать метод корректно.
Но объектное отображение позволяет предопределять свойства даже до того, как объект будет построен. Например:
$query = "SELECT id, first_name, last_name FROM users"; // PDO $result = $pdo->query($query); $result->setFetchMode(PDO::FETCH_CLASS, 'User'); while ($user = $result->fetch()) { echo $user->info()."\n"; } // MySQLI, процедурная часть if ($result = mysqli_query($mysqli, $query)) { while ($user = mysqli_fetch_object($result, 'User')) { echo $user->info()."\n"; } } // MySQLi, ООП if ($result = $mysqli->query($query)) { while ($user = $result->fetch_object('User')) { echo $user->info()."\n"; } }
Функция mysqli connect: соединение с MySQL
Перед началом работы с данными внутри MySQL, нужно открыть соединение с сервером СУБД.
В PHP это делается с помощью стандартной функции . Функция возвращает результат — ресурс соединения. Данный ресурс используется для всех следующих операций с MySQL.
Но чтобы выполнить соединение с сервером, необходимо знать как минимум три параметра:
- Адрес сервера СУБД;
- Логин;
- Пароль.
Если вы следовали стандартной процедуре установки MySQL или используете OpenServer, то адресом сервера будет , логином — . При использовании OpenServer пароль для подключения — это пустая строка ‘’, а при самостоятельной установке MySQL пароль вы задавали в одном из шагов мастера установки.
Базовый синтаксис функции :
Проверка соединения
Первое, что нужно сделать после соединения с СУБД — это выполнить проверку, что оно было успешным.
Эта проверка нужна, чтобы исключить ошибку при подключении к БД. Неверные параметры подключения, неправильная настройка или высокая нагрузка заставит MySQL отвеграть новые подключения. Все эти ситуации приведут к невозможности соединения, поэтому программист должен проверить успешность подключения к серверу, прежде чем выполнять следующие действия.
Соединение с MySQL устанавливается один раз в сценарии, а затем используется при всех запросах к БД.
Результатом выполнения функции будет значение специального типа — ресурс.
Если подключение к MySQL не удалось, то функция вместо ресурса вернет логическое значение типа «ложь» — .
Хорошей практикой будет всегда проверять значение результа выполнения этой функции и сравнивать его с ложью.
Соединение с MySQL и проверка на ошибки:
Функция просто возвращает текстовое описание последней ошибки MySQL.
Установка кодировки
Первым делом после установки соединения крайне желательно явно задать кодировку, которая будет использоваться при обмене данными с MySQL. Если этого не сделать, то вместо записей со значениями, написанными кириллицей, можно получить последовательность из знаков вопроса: ‘?????????????????’.
Вызови эту функцию сразу после успешной установки соединения:
Как достать результат
После того, как мы сделали запрос к базе, в переменной $result
будет лежать результат этого действия.
Однако лежит он не в той форме, которая нам нужна в PHP, а в той форме,
в которой его прислала нам база.
Достать результат в нормальном виде (в массиве)
можно с помощью следующего кода:
Как работает последняя строка?
Функция mysqli_fetch_assoc считывает последовательно каждую строку результата,
который прислала нам база.
В цикле for мы считываем построчно результат из базы.
Когда цикл дойдет до последней строки — mysqli_fetch_assoc
вернет false и цикл for закончит свою работу.
А результат из БД будет лежать в нормальном виде в массиве $data.
Устанавливаем соединение с БД
Данный материал есть также в виде видео:
Прежде, чем начать работать с базой данных из PHP, нужно установить соединение с сервером,
на котором эта база находится.
Делается это с помощью функции PHP mysql_connect, которая принимает 3 параметра:
имя хоста (сервера), имя пользователя,
под которым мы работаем с базой и пароль для этого пользователя.
Если вы работаете на своем компьютере то это будут ‘localhost’, ‘root’ и
пароль в виде пустой строки (на некоторых серверах он тоже может быть root).
Если ваша база данных в интернете — то эти данные дает вам хостер.
Давайте установим соединение с базой данных:
PHP MySQLi Functions
Function | Description |
---|---|
affected_rows() | Returns the number of affected rows in the previous MySQL operation |
autocommit() | Turns on or off auto-committing database modifications |
begin_transaction() | Starts a transaction |
change_user() | Changes the user of the specified database connection |
character_set_name() | Returns the default character set for the database connection |
close() | Closes a previously opened database connection |
commit() | Commits the current transaction |
connect() | Opens a new connection to the MySQL server |
connect_errno() | Returns the error code from the last connection error |
connect_error() | Returns the error description from the last connection error |
data_seek() | Adjusts the result pointer to an arbitrary row in the result-set |
debug() | Performs debugging operations |
dump_debug_info() | Dumps debugging info into the log |
errno() | Returns the last error code for the most recent function call |
error() | Returns the last error description for the most recent function call |
error_list() | Returns a list of errors for the most recent function call |
fetch_all() | Fetches all result rows as an associative array, a numeric array, or both |
fetch_array() | Fetches a result row as an associative, a numeric array, or both |
fetch_assoc() | Fetches a result row as an associative array |
fetch_field() | Returns the next field in the result-set, as an object |
fetch_field_direct() | Returns meta-data for a single field in the result-set, as an object |
fetch_fields() | Returns an array of objects that represent the fields in a result-set |
fetch_lengths() | Returns the lengths of the columns of the current row in the result-set |
fetch_object() | Returns the current row of a result-set, as an object |
fetch_row() | Fetches one row from a result-set and returns it as an enumerated array |
field_count() | Returns the number of columns for the most recent query |
field_seek() | Sets the field cursor to the given field offset |
get_charset() | Returns a character set object |
get_client_info() | Returns the MySQL client library version |
get_client_stats() | Returns statistics about client per-process |
get_client_version() | Returns the MySQL client library version as an integer |
get_connection_stats() | Returns statistics about the client connection |
get_host_info() | Returns the MySQL server hostname and the connection type |
get_proto_info() | Returns the MySQL protocol version |
get_server_info() | Returns the MySQL server version |
get_server_version() | Returns the MySQL server version as an integer |
info() | Returns information about the last executed query |
init() | Initializes MySQLi and returns a resource for use with real_connect() |
insert_id() | Returns the auto-generated id from the last query |
kill() | Asks the server to kill a MySQL thread |
more_results() | Checks if there are more results from a multi query |
multi_query() | Performs one or more queries on the database |
next_result() | Prepares the next result-set from multi_query() |
options() | Sets extra connect options and affect behavior for a connection |
ping() | Pings a server connection, or tries to reconnect if the connection has gone down |
poll() | Polls connections |
prepare() | Prepares an SQL statement for execution |
query() | Performs a query against a database |
real_connect() | Opens a new connection to the MySQL server |
real_escape_string() | Escapes special characters in a string for use in an SQL statement |
real_query() | Executes a single SQL query |
reap_async_query() | Returns result from an async SQL query |
refresh() | Refreshes/flushes tables or caches, or resets the replication server information |
rollback() | Rolls back the current transaction for the database |
select_db() | Select the default database for database queries |
set_charset() | Sets the default client character set |
set_local_infile_default() | Unsets user defined handler for load local infile command |
set_local_infile_handler() | Set callback function for LOAD DATA LOCAL INFILE command |
sqlstate() | Returns the SQLSTATE error code for the error |
ssl_set() | Used to establish secure connections using SSL |
stat() | Returns the current system status |
stmt_init() | Initializes a statement and returns an object for use with stmt_prepare() |
store_result() | Transfers a result-set from the last query |
thread_id() | Returns the thread ID for the current connection |
thread_safe() | Returns whether the client library is compiled as thread-safe |
use_result() | Initiates the retrieval of a result-set from the last query executed |
warning_count() | Returns the number of warnings from the last query in the connection |
Задачи на PhpMyAdmin
Давайте откроем PhpMyAdmin и приступим к изучению его интерфейса.
Чтобы вам было проще с ним освоится, сделайте следующие практические задачи:
- Создайте базу данных test.
- В ней создайте таблицу users.
-
В этой таблице сделайте 4 поля (столбца):
- id (для него нужно поставить галочку AUTO_INCREMENT или A.I.), тип integer,
- name, тип varchar, 32 символа,
- age, тип integer,
- birthday (англ. день рождения), тип date.
-
Найдите вкладку ‘вставить’ и с ее помощью вставьте несколько строк в эту таблицу
При этом поле id заполнять не нужно! Оно заполнится автоматически! - Поредактируйте какую-нибудь запись.
- Удалите какую-нибудь запись.
- Поменяйте кодировку для таблицы и для всей базы данных (на utf8_general_ci).
- Переименуйте таблицу.
- Переименуйте базу данных.