Php и mysqli. примеры запросов

Начало работы

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

Здесь ничего сложного — объявляем класс-обертку над основным классом 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 email 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 email 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 и приступим к изучению его интерфейса.

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

  1. Создайте базу данных test.
  2. В ней создайте таблицу users.
  3. В этой таблице сделайте 4 поля (столбца):

    • id (для него нужно поставить галочку AUTO_INCREMENT или A.I.), тип integer,
    • name, тип varchar, 32 символа,
    • age, тип integer,
    • birthday (англ. день рождения), тип date.
  4. Найдите вкладку ‘вставить’ и с ее помощью вставьте несколько строк в эту таблицу
    При этом поле id заполнять не нужно! Оно заполнится автоматически!
  5. Поредактируйте какую-нибудь запись.
  6. Удалите какую-нибудь запись.
  7. Поменяйте кодировку для таблицы и для всей базы данных (на utf8_general_ci).
  8. Переименуйте таблицу.
  9. Переименуйте базу данных.
Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *

Adblock
detector