Skip to content

7. Використання SGBD та MySql

Ми напишемо скрипти PHP, використовуючи базу даних MySQL:

SGBD MySQL входить до складу пакета WampServer (див. розділ 2.1.1). Ми покажемо, як створити базу даних та користувача MySQL.

  • Після запуску WampServer можна керувати за допомогою іконки [1], розташованої внизу праворуч на панелі завдань.
  • У [2] запускається інструмент адміністрування MySQL

Створюємо базу даних [dbpersonnes]:

Image

Створюємо користувача [admpersonnes] з паролем [nobody]:

  • в [1], ім'я користувача
  • на [2], комп'ютер SGBD, на якому йому надано права
  • у [3] — його пароль
  • у [4] — те саме
  • у [5] — цьому користувачеві не надаються жодні права
  • у [6] — його створюють
  • в [7], повертаємося на головну сторінку phpMyAdmin
  • у [8] використовується посилання [Privileges] з цієї сторінки, щоб перейти до редагування даних користувачів [admpersonnes] та [9].

1011

Image

  • у [10] вказується, що користувачеві [admpersonnes] надаються права на базу даних [dbpersonnes]
  • у файлі [11] підтверджується цей вибір
  • за посиланням [12] У [Tout cocher] користувачеві [admpersonnes] надаються всі права на базу даних [dbpersonnes] [13]
  • підтверджуємо в [14]

Тепер ми маємо:

  • базу даних MySQL [dbpersonnes]
  • користувача [admpersonnes / nobody], який має всі права на цю базу даних

Ми напишемо скрипти PHP для роботи з базою даних. PHP має різні бібліотеки для управління базами даних. Ми будемо використовувати бібліотеку PDO (PHP Data Objects), яка забезпечує взаємодію між кодом PHP та SGBD:

Бібліотека PDO дозволяє скрипту PHP абстрагуватися від конкретного типу використовуваного SGBD. Таким чином, у наведеному вище прикладі SGBD MySQL можна замінити на SGBD Postgres із мінімальним впливом на код скрипта PHP. Ця бібліотека за замовчуванням недоступна. Перевірити її наявність можна таким чином:

  • 1: у меню адміністрування WampServer виберіть опцію [PHP / PHP extensions]
  • 2: можна побачити різні доступні розширення PDO та ті, що активні: [PHP_pdo_mysql] для SGBD MySQL, [PHP_pdo_sqlite] для SGBD, SQL Lite. Щоб активувати розширення, достатньо натиснути на нього. Після цього інтерпретатор PHP перезапускається з новим активованим розширенням.

7.1. Підключення до бази даних MySQL – 1 (mysql_01)

Підключення до SGBD здійснюється шляхом створення об’єкта PDO. Конструктор приймає різні параметри:

$dbh=new PDO($dsn,$user,$passwd,$driver_options)

Значення параметрів такі:

$dsn
(Data Source Name) — це рядок, що визначає тип SGBD та його розташування в Інтернеті. Рядок
«mysql:host=localhost» вказує, що мова йде про SGBD MySQL, що працює на локальному сервері. Цей рядок
може містити й інші параметри, зокрема порт, на якому SGBD приймає з’єднання, та ім’я бази даних, до якої
хочеться підключитися: «mysql:host=localhost:port=3306:dbname=dbpersonnes».
$user
ідентифікатор користувача, який підключається
$passwd
його пароль
$driver_options
масив параметрів для драйвера SGBD

Обов'язковим є лише перший параметр. Створений таким чином об'єкт буде потім слугувати основою для всіх операцій, що виконуються з базою даних, до якої було здійснено підключення. Якщо об'єкт PDO не вдалося створити, генерується виняток типу PDOException.

Ось приклад підключення:


<?php

// підключення до бази даних MySql
// ідентифікатор користувача — (admpersonnes,nobody)
$ID = "admpersonnes";
$PWD = "nobody";
$HOTE = "localhost";

try {
  // вхід
  $dbh = new PDO("mysql:host=$HOTE", $ID, $PWD);
  print "Connexion réussie\n";
  // закриття
  $dbh = null;
} catch (PDOException $e) {
  print "Erreur : " . $e->getMessage() . "\n";
  exit();
}

Результати:

Connexion réussie

Коментарі

  • рядок 11: підключення до SGBD здійснюється шляхом створення об’єкта PDO. Конструктор тут використовується з такими параметрами:
    • рядок, що вказує на тип SGBD та його розташування в Інтернеті. Рядок «mysql:host=localhost» вказує, що мова йде про SGBD MySQL, що працює на локальному сервері. Порт не вказано. У такому разі за замовчуванням використовується порт 3306. Назва бази даних також не вказана. У такому разі відбудеться підключення до SGBD MySQL, а вибір конкретної бази даних відбудеться пізніше.
    • ідентифікатор користувача
    • його пароль
  • рядок 14: закриття з’єднання відбувається шляхом знищення об’єкта PDO, створеного спочатку.
  • рядок 15: підключення до SGBD може завершитися невдачею. У цьому випадку генерується виняток типу PDOException.

7.2. Створення таблиці MySQL (mysql_02)


<?php

// підключення до бази даних MySql
// ідентифікатор користувача
$ID = "admpersonnes";
$PWD = "nobody";
// ідентифікатор бази даних
$DSN = "mysql:host=localhost;dbname=dbpersonnes";

// підключення
list($erreur, $connexion) = connecte($DSN, $ID, $PWD);
if ($erreur) {
  print "Erreur lors de la connexion à la base [$DSN] sous l'identité ($ID,$PWD) : $erreur\n";
  exit;
}

// видалення таблиці «особи», якщо вона існує
$requête = "drop table personnes";
$erreur = exécuteRequête($connexion, $requête);
//Чи сталася помилка?
if ($erreur)
  print "$requête : Erreur ($erreur)\n";
else
  print "$requête: Exécution réussie\n";
// створення таблиці «особи»
$requête = "create table personnes (prenom varchar(30) NOT NULL, nom varchar(30) NOT NULL, age integer NOT NULL, primary key(nom,prenom))";
$erreur = exécuteRequête($connexion, $requête);
//Чи сталася помилка?
if ($erreur)
  print "$requête : Erreur ($erreur)\n";
else
  print "$requête: Exécution réussie\n";
// відключення та вихід
déconnecte($connexion);
exit;

// ---------------------------------------------------------------------------------
function connecte($dsn, $login, $pwd) {
  // підключення ($login,$pwd) до бази даних $dsn
  // повертає ідентифікатор з’єднання та код помилки
  try {
    // підключення
    $dbh = new PDO($dsn, $login, $pwd);
    // повернення без помилки
    return array("", $dbh);
  } catch (PDOException $e) {
    // відповідь з помилкою
    return array($e->getMessage(), null);
  }
}

// ---------------------------------------------------------------------------------
function déconnecte($connexion) {
  // закриває з'єднання, ідентифіковане як $connexion
  $connexion = null;
}

// ---------------------------------------------------------------------------------
function exécuteRequête($connexion, $sql) {
  // виконує запит $sql через з'єднання $connexion
  try {
    $connexion->exec($sql);
    // повертається без помилок
    return "";
  } catch (PDOException $e) {
    // повернення з помилкою
    return $e->getMessage();
  }
}

Результати:

drop table personnes: Exécution réussie
create table personnes (prenom varchar(30) NOT NULL, nom varchar(30) NOT NULL, age integer NOT NULL, primary key(nom,prenom)): Exécution réussie

У PHPMyAdmin можна побачити наявність таблиці:

 

Коментарі

  • рядки 38–50: функція connecte встановлює з’єднання з SGBD. Вона повертає масив ($erreur, $connexion), де $connexion — це створене з’єднання, або null, якщо його не вдалося створити. В останньому випадку $erreur є повідомленням про помилку.
  • рядки 53–56: функція déconnecte закриває з’єднання
  • рядок 59: функція exécuteRequête дозволяє виконати команду SQL над з’єднанням. З’єднання є об’єктом PDO. Метод, що використовується для виконання команди SQL над об’єктом PDO, — це метод exec (рядок 63). Виконання запиту може запустити PDOException. Тому цей запит також обробляється. Функція повертає повідомлення про помилку в разі помилки, а в іншому випадку — порожній рядок.

7.3. Заповнення таблиці «особи» (mysql_03)

Наступний скрипт виконує команди SQL, що містяться у такому текстовому файлі [creation.txt]:

drop table personnes
create table personnes (prenom varchar(30) not null, nom varchar(30) not null, age integer not null, primary key (nom,prenom))
insert into personnes values('Paul','Langevin',48)
insert into personnes values ('Sylvie','Lefur',70)
insert into personnes values ('Pierre','Nicazou',35)
insert into personnes values ('Geraldine','Colou',26)
insert into personnes values ('Paulette','Girond',56)


<?php

// підключення до бази даних MySql
// ідентифікатор користувача
$ID = "admpersonnes";
$PWD = "nobody";
// ідентифікатор бази даних
$DSN = "mysql:host=localhost;dbname=dbpersonnes";
// ідентифікатор текстового файлу команд SQL, що підлягають виконанню
$TEXTE = "creation.txt";

// вхід
list($erreur, $connexion) = connecte($DSN, $ID, $PWD);
if ($erreur) {
  print "Erreur lors de la connexion à la base [$DSN] sous l'identité ($ID,$PWD) : $erreur\n";
  exit;
}

// створення та заповнення таблиці
$erreurs = exécuterCommandes($connexion, $TEXTE, 1, 0);
// виведення кількості помилок
print "il y a eu $erreurs[0] erreurs\n";
for ($i = 1; $i < count($erreurs); $i++)
  print "$erreurs[$i]\n";

// роз'єднання та вихід
déconnecte($connexion);
exit;

// ---------------------------------------------------------------------------------
function connecte($dsn, $login, $pwd) {
...
}

// ---------------------------------------------------------------------------------
function déconnecte($connexion) {
 ...
}

// ---------------------------------------------------------------------------------
function exécuteRequête($connexion, $sql) {
  // виконує запит $sql через з'єднання $connexion
  // повертає 1 повідомлення про помилку, якщо є помилка, і порожній рядок, якщо помилки немає
  ...
}

// ---------------------------------------------------------------------------------
function exécuterCommandes($connexion, $SQL, $suivi=0, $arrêt=1) {
  // використовує з'єднання $connexion
  // виконує команди SQL, що містяться у текстовому файлі $SQL
  // цей файл є файлом команд SQL, які слід виконувати по одній на рядок
  // якщо $suivi=1, то після кожного виконання команди SQL на екрані з’являється повідомлення про успіх або невдачу
  // якщо $arrêt=1, функція зупиняється при першій виявленій помилці, інакше вона виконує всі команди SQL
  // функція повертає масив (кількість помилок, помилка1, помилка2, ...)
  
  // перевіряється наявність файлу $SQL
  if (! file_exists($SQL))
    return array(1, "Le fichier $SQL n'existe pas");

  // виконання запитів SQL, що містяться у файлі $SQL
  // їх поміщають у масив
  $requêtes = file($SQL);
  // виконуємо їх — спочатку помилок немає
  $erreurs = array(0);
  for ($i = 0; $i < count($requêtes); $i++) {
    //чи є порожній запит
    if (preg_match("/^\s*$/", $requêtes[$i]))
      continue;
    // виконання запиту $i
    $erreur = exécuteRequête($connexion, $requêtes[$i]);
    //чи сталася помилка?
    if ($erreur) {
      // ще одна помилка
      $erreurs[0]++;
      // повідомлення про помилку
      $msg = "$requêtes[$i] : Erreur ($erreur)\n";
      $erreurs[] = $msg;
      // відстежувати на екрані чи ні?
      if ($suivi)
        print "$msg\n";
      // зупиняємося?
      if ($arrêt)
        return $erreurs;
    } else
    if ($suivi)
      print "$requêtes[$i] : Exécution réussie\n";
  }//для
  // повернення
  return $erreurs;
}

Результати на екрані:

drop table personnes : Exécution réussie
create table personnes (prenom varchar(30) not null, nom varchar(30) not null, age integer not null, primary key (nom,prenom)) : Exécution réussie
insert into personnes values('Paul','Langevin',48) : Exécution réussie
insert into personnes values ('Sylvie','Lefur',70) : Exécution réussie
insert into personnes values ('Pierre','Nicazou',35) : Exécution réussie
insert into personnes values ('Geraldine','Colou',26) : Exécution réussie
insert into personnes values ('Paulette','Girond',56) : Exécution réussie
il y a eu 0 erreurs

Вставлені елементи можна переглянути за допомогою PhpMyAdmin:

 

Коментарі

Нововведення полягає у функції exécuterCommandes у рядках 48–90. Ця функція виконує на з’єднанні $connexion команди SQL, знайдені у текстовому файлі з назвою $SQL. Вона повертає масив помилок ($nbErreurs, $msg1, $msg2, …) де $nbErreurs — кількість помилок, $msgi — повідомлення про помилку № i.. Якщо помилок немає, повертається масив array(0).

7.4. Виконання довільних запитів SQL (mysql_04)

Наступний скрипт демонструє виконання команд SQL із такого текстового файлу [sql.txt]:

select * from personnes
select nom,prenom from personnes order by nom asc, prenom desc
select * from personnes where age between 20 and 40 order by age desc, nom asc, prenom asc
insert into personnes values('Josette','Bruneau',46)
update personnes set age=47 where nom='Bruneau'
select * from personnes where nom='Bruneau'
delete from personnes where nom='Bruneau'
select * from personnes where nom='Bruneau'
xselect * from personnes where nom='Bruneau'

Серед цих команд SQL є команда select, яка повертає результати з бази даних, команди insert, update, delete, які змінюють базу даних, не повертаючи результатів, і, нарешті, помилкові команди, такі як остання (xselect).


<?php

// підключення до бази даних MySql
// ідентифікатор користувача
$ID = "admpersonnes";
$PWD = "nobody";
// ідентифікатор бази даних
$DSN = "mysql:host=localhost;dbname=dbpersonnes";
// ідентифікатор текстового файлу команд SQL, що підлягають виконанню
$TEXTE = "sql.txt";

// вхід
list($erreur, $connexion) = connecte($DSN, $ID, $PWD);
if ($erreur) {
  print "Erreur lors de la connexion à la base [$DSN] sous l'identité ($ID,$PWD) : $erreur\n";
  exit;
}

// виконання команд SQL
$erreurs = exécuterCommandes($connexion, $TEXTE, 1, 0);
// виведення кількості помилок
print "il y a eu $erreurs[0] erreur(s)\n";
for ($i = 1; $i < count($erreurs); $i++)
  print "$erreurs[$i]\n";

// відключення та вихід
déconnecte($connexion);
exit;

// ---------------------------------------------------------------------------------
function connecte($dsn, $login, $pwd) {
  // підключення ($login,$pwd) до бази $dsn
  // повертає ідентифікатор з'єднання та повідомлення про помилку
  ...
}

// ---------------------------------------------------------------------------------
function déconnecte($connexion) {
  ...
}

// ---------------------------------------------------------------------------------
function exécuteRequête($connexion, $sql) {
  // виконує запит $sql через з’єднання $connexion
  // повертає масив із 2 елементів ($erreur, $résultat)
  
  // визначається, чи це вибірка, чи ні
  $commande = "";
  if (preg_match("/^\s*(\S+)/", $sql, $champs)) {
    $commande = $champs[0];
  }
  // виконання команди
  try {
    if (strtolower($commande) == "select") {
      $res = $connexion->query($sql);
    } else {
      $res = $connexion->exec($sql);
      if($res===FALSE){
        $info=$connexion->errorInfo();
        return array($info[2],null);
      }
    }
    // повернення без помилки
    return array("", $res);
  } catch (PDOException $e) {
    // повернення з помилкою
    return array($e->getMessage(), null);
  }
}

// ---------------------------------------------------------------------------------
function exécuterCommandes($connexion, $SQL, $suivi=0, $arrêt=1) {
  // використовує з'єднання $connexion
  // виконує команди SQL, що містяться у текстовому файлі $SQL
  // цей файл є файлом команд SQL, які слід виконувати по одній на рядок
  // якщо $suivi=1, то після кожного виконання команди SQL на екрані з’являється повідомлення про успіх або невдачу
  // якщо $arrêt=1, функція зупиняється при першій виявленій помилці, інакше вона виконує всі команди SQL
  // функція повертає масив (кількість помилок, помилка1, помилка2, ...)
  // перевіряється наявність файлу $SQL
  if (!file_exists($SQL))
    return array(1, "Le fichier $SQL n'existe pas");

  // виконання запитів SQL, що містяться у $TEXTE
  // їх поміщають у масив
  $requêtes = file($SQL);
  // їх виконують — спочатку помилок немає
  $erreurs = array(0);
  for ($i = 0; $i < count($requêtes); $i++) {
    //чи запит порожній
    if (preg_match("/^\s*$/", $requêtes[$i]))
      continue;
    // виконання запиту $i
    list($erreur, $res) = exécuteRequête($connexion, $requêtes[$i]);
    //чи сталася помилка?
    if ($erreur) {
      // ще одна помилка
      $erreurs[0]++;
      // повідомлення про помилку
      $msg = "$requêtes[$i] : Erreur ($erreur)\n";
      $erreurs[] = $msg;
      // відстежувати на екрані чи ні?
      if ($suivi)
        print "$msg\n";
      // зупинитися?
      if ($arrêt)
        return $erreurs;
    } else
    if ($suivi) {
      print "$requêtes[$i] : Exécution réussie\n";
      // інформація про результат виконаного запиту
      afficherInfos($res);
    }
  }//for
  // повернення
  return $erreurs;
}

// ---------------------------------------------------------------------------------
function afficherInfos($résultat) {
  // виводить результат $résultat запиту SQL
  // чи це був запит SELECT?
  if ($résultat instanceof PDOStatement) {
    // відображаються назви полів
    $titre = "";
    $nbColonnes = $résultat->columnCount();
    for ($i = 0; $i < $nbColonnes; $i++) {
      $infos = $résultat->getColumnMeta($i);
      $titre.=$infos['name'] . ",";
    }
    // видаляється останній символ ,
    $titre = substr($titre, 0, strlen($titre) - 1);
    // виводиться список полів
    print "$titre\n";
    // розділювальний рядок
    $séparateurs = "";
    for ($i = 0; $i < strlen($titre); $i++) {
      $séparateurs.="-";
    }
    print "$séparateurs\n";
    // дані
    foreach ($résultat as $ligne) {
      $data = "";
      for ($i = 0; $i < $nbColonnes; $i++) {
        $data.=$ligne[$i] . ",";
      }

      // видаляється останній символ ,
      $data = substr($data, 0, strlen($data) - 1);
      // виводиться
      print "$data\n";
    }
  } else {
    // це не був вибір
    print " $résultat lignes(s) a (ont) été modifiée(s)\n";
  }
}

Результати на екрані:

select * from personnes
 : Exécution réussie
prenom,nom,age
--------------
Geraldine,Colou,26
Paulette,Girond,56
Paul,Langevin,48
Sylvie,Lefur,70
Pierre,Nicazou,35
select nom,prenom from personnes order by nom asc, prenom desc : Exécution réussie
nom,prenom
----------
Colou,Geraldine
Girond,Paulette
Langevin,Paul
Lefur,Sylvie
Nicazou,Pierre
select * from personnes where age between 20 and 40 order by age desc, nom asc, prenom asc : Exécution réussie
prenom,nom,age
--------------
Pierre,Nicazou,35
Geraldine,Colou,26
insert into personnes values('Josette','Bruneau',46) : Exécution réussie
 1 lignes(s) a (ont) été modifiée(s)
update personnes set age=47 where nom='Bruneau' : Exécution réussie
 1 lignes(s) a (ont) été modifiée(s)
select * from personnes where nom='Bruneau' : Exécution réussie
prenom,nom,age
--------------
Josette,Bruneau,47
delete from personnes where nom='Bruneau' : Exécution réussie
 1 lignes(s) a (ont) été modifiée(s)
select * from personnes where nom='Bruneau' : Exécution réussie
prenom,nom,age
--------------
xselect * from personnes where nom='Bruneau' : Erreur (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 'xselect * from personnes where nom='Bruneau'' at line 1)

il y a eu 1 erreur(s)
xselect * from personnes where nom='Bruneau' : Erreur (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 'xselect * from personnes where nom='Bruneau'' at line 1)

Коментарі

Кожна з команд текстового файлу [sql.txt] виконується функцією exécuteRequête із рядка 43.

  • рядок 43: двома параметрами функції є з’єднання ($connexion), на якому мають виконуватися команди SQL, та SQL-команда ($sql), яку потрібно виконати. Функція повертає масив із двох значень ($erreur, $résultat), де
    • $erreur — повідомлення про помилку, яке може бути порожнім, якщо помилки не сталося
    • $résultat — результат, отриманий у результаті виконання команди SQL. Цей результат відрізняється залежно від того, чи є замовлення замовленням select, чи замовленням insert, update, delete.
  • рядки 48–51: витягується перший елемент замовлення SQL, щоб визначити, чи маємо справу із замовленням select, чи із замовленням insert, update, delete.
  • рядок 55: у випадку команди select вона виконується за допомогою методу [PDO]->query("команда select"). Результатом є об’єкт типу PDOStatement.
  • рядок 57: у випадку команди insert, update, delete вона виконується за допомогою методу [PDO]->exec("команда SQL"). Результатом є кількість рядків, змінених командою SQL. Отже, якщо команда SQL delete видаляє два рядки, результатом є ціле число 2. Якщо під час виконання сталася помилка, результатом є логічне значення false. У цьому випадку метод [PDO]->errorinfo() надає інформацію про помилку у вигляді масиву значень. Елемент з індексом 2 цього масиву — це повідомлення про помилку.
  • рядки 58–60: обробка можливої помилки операції [PDO]->exec("запит SQL").
  • рядки 65–68: обробка можливого винятку
  • рядок 72: функція exécuterCommandes виконує на з’єднанні $connexion команди SQL, збережені у текстовому файлі $SQL. Цей код ми вже зустрічали, за винятком однієї дрібниці: рядка 111.
  • рядок 111: функція exécuteRequête повернула масив ($erreur, $résultat), або $résultat є результатом виконання команди SQL. Цей результат відрізняється залежно від того, чи була команда SQL командою select, чи командою insert, update, delete. Функція afficherInfos відображає інформацію про цей результат.
  • рядок 122: якщо наказ SQL був наказом select, результат має тип PDOStatement. Цей тип представляє таблицю, що складається з рядків і стовпців.
  • рядок 125: метод [PDOStatement]->getColumnCount() повертає кількість стовпців таблиці, отриманої в результаті виконання select
  • рядок 127: метод [PDOStatement]->getMeta(i) повертає словник з інформацією про стовпець № i таблиці, отриманої в результаті виконання select.. У цьому словнику значенням, пов'язаним із ключем «name», є назва стовпця.
  • рядки 127–129: імена стовпців таблиці результатів select об’єднані в один рядок.
  • рядки 141–145: об’єкт типу PDOStatement можна обходити за допомогою циклу foreach. На кожній ітерації отриманим елементом є рядок таблиці результатів select у вигляді масиву значень, що представляють значення різних стовпців цього рядка. Усі ці значення виводяться за допомогою циклу for.
  • рядок 154: результатом виконання команд insert, update, delete є кількість рядків, змінених цими командами.
https://stahe.github.io
Цей навчальний курс, написаний Сержем Тахе, надається у відкритий доступ відповідно до умов Creative Commons «Вказання авторства — Некомерційне використання — Поширення на тих самих умовах 3.0 без змін.