PHP-SQL

Какво ще разгледаме днес

Релационни бази от данни
Работа с phpMyAdmin
Работа с SQL от PHP

Релационни бази от данни

стара презентация

SQL

Език за работа с данни.

Той позволява:

Вмъкване на данни (Insert)
Извличане на данни (Select)
Редактиране на данни (Update)
Изтриване на данни (Delete)

Select

ER Diagram

SELECT * FROM MAJORS
SELECT NAME, ACADEMIC_DEGREE FROM MAJORS
SELECT * FROM MAJORS WHERE NAME = 'Software Engineering'
SELECT * FROM MAJORS WHERE START_YEAR = 2011

Insert

ER Diagram

INSERT INTO MAJORS (NAME, ACADEMIC_DEGREE) VALUES ('Software Eginnering', 'bachelor')

JOIN

         People                          Animals
========================        =========================
| id | name   | pet_id |        | id | name   | species |
| 1  | Pesho  | 2      |        | 1  | Sisi   | cat     |
| 2  | Gosho  | NULL   |        | 2  | Sharo  | dog     |
| 3  | Ivan   | 4      |        | 3  | Mecho  | dog     |
| 4  | Penka  | 1      |        | 4  | Koko   | parrot  |
========================        =========================

Ако искаме да намерим всички хора с домашни любимци, както и самите им любимци:

SELECT People.name, Animals.name
FROM People
INNER JOIN Animals
ON People.pet_id = Animals.id;

| People.name | Animals.name |
| Pesho       | Sharo        |
| Ivan        | Koko         |
| Penka       | Sisi         |

Видове JOIN

Има няколко вида `JOIN`. Основните са `INNER`, `LEFT`, `RIGHT`, `FULL`.

За този курс сигурно ще използвате само `INNER JOIN` ако въобще ви се наложи.

          INNER JOIN¿                        FULL JOIN¿
==============================      ==============================
| People.name | Animals.name |      | People.name | Animals.name |
| Pesho       | Sharo        |      | Pesho       | Sharo        |
| Ivan        | Koko         |      | Gosho       | NULL         |
| Penka       | Sisi         |      | Ivan        | Koko         |
==============================      | Penka       | Sisi         |
     хора и любимците им            | NULL        | Mecho        |
                                    ==============================
                                        всички хора и животни

          LEFT JOIN¿                        RIGHT JOIN¿
==============================      ==============================
| People.name | Animals.name |      | People.name | Animals.name |
| Pesho       | Sharo        |      | Pesho       | Sharo        |
| Gosho       | NULL         |      | Ivan        | Koko         |
| Ivan        | Koko         |      | Penka       | Sisi         |
| Penka       | Sisi         |      | NULL        | Mecho        |
==============================      ==============================
         всички хора                        всички животни

INNER JOIN - Връща всички редове, за които има съвпадение и в двете таблици.
FULL (OUTER) JOIN - Връща всички редове, за които има съвпадение в поне една от таблиците.
LEFT (OUTER) JOIN - Връща всички редове от лявата таблица и съвпадащите редове в дясната.
RIGHT (OUTER) JOIN - Връща всички редове от дясната таблица и съвпадащите редове в лявата.

phpMyAdmin

За създаване на базата phpMyAdmin е прост UI, който може да ви е полезен.

Има го и в xampp, в 000webhost и най-вероятно на всеки друг хостинг.

Следват 10тина слайда за него - ако искате ги пропуснете.

localhost/phpmyadmin
`Databases` -> `Create Database` -> `[db_name, utf8_general_ci]`
`db_name` -> `Create table` -> `[table_name, number_of_columns]`
На първата колона задавате `name` -> `id`, `index` -> `PRIMARY`, `A_I` -> checked
На останалите обикновено се интересувате само от `name`, `type` и `length` - type обикновено е
- `INT` за цели числа
- `VARCHAR` за текст с променлива дължина (обикновено с дължина до 255)
- `TEXT` за неограничен текст
- `DATE` за дата.

PHPMA - Start

PHPMA - Create DB

PHPMA - Enter DB

PHPMA - Create Table

PHPMA - Create Table

PHPMA - Table Actions

PHPMA - Insert (UI)

PHPMA - Insert (SQL)

PHPMA - Browse

PHPMA - Structure

Работа с SQL от PHP

Макар и да има 3 начина за свързване към `SQL` от `PHP` - `mysql`(deprecated), `PDO`, `mysqli`, много силно ви препоръчваме да използвате `PDO`

$conn  = new PDO('mysql:host=localhost;dbname=mydbname', 'user', 'password');
$sql   = "SELECT * FROM MAJORS";
//$sql = "SELECT * FROM MAJORS WHERE START_YEAR = $year"
            // Now lets say $year = "''; DROP DATABASE MAJORS;"

$query = $conn->query($sql) or die("failed!");
while ($row = $query->fetch(PDO::FETCH_ASSOC)) {
  echo $row['NAME'];
}

Стъпки

Свързване

$conn = new PDO($dsn, $user, $pass, $options); // ¿

Заявка

$stmt = $conn->query($sql);                     // direct
$stmt = $conn->prepare($sql)->execute($values); // prepared

Извличане на данни (при select)

$row = $stmt->fetch();        // single row

while ($row = $stmt->fetch()) // iterate rows

$rows = $stmt->fetchAll();    // direct array

В `$options` (опционално) - можем да зададем неща като `PDO::ATTR_(ERRMODE|DEFAULT_FETCH_MODE)` и други.

Prepared statements (indexed)

Вместо да разчитаме на `mysql_real_escape_string` (deprecated) имаме алтернатива:
Подготвяме заявката, а после я изпълняваме с желаните стойности =>
Така повишаваме скоростта и сигурността

$conn = new PDO('mysql:host=localhost;dbname=university;charset=utf8', 'root', '');
$stmt = $conn->prepare("SELECT * FROM MAJORS WHERE ACADEMIC_DEGREE = '?'");

$stmt->execute(['бакалавър']) // Array of all ? values
$rows = $stmt->fetchALL(PDO::FETCH_NUM)
// type is PDO::FETCH_(NUM|ASSOC|BOTH|OBJ|LAZY)
//         NUM ($r[0]), ASSOC ($r['key']), BOTH (default)
//         OBJ ($r->key), LAZY (BOTH + OBJ) and others

$stmt = $conn->prepare("INSERT INTO MAJORS (NAME, ACADEMIC_DEGREE) VALUES ('?', '?')");
$stmt->execute(['Биология', 'бакалавър']);

Prepared statements (named)

Както видяхте при резултатите имаме и обикновени и асоциативни масиви за резултати

По същия начин можем да задаваме и параметрите като асоциативен масив

$stmt = $conn->prepare("SELECT * FROM MAJORS
                        WHERE ACADEMIC_DEGREE = :degree");

$stmt->execute(['degree' => 'бакалавър']);

$stmt = $conn->prepare("
  INSERT INTO MAJORS (NAME, ACADEMIC_DEGREE)
  VALUES (:name, :degree)
");
$stmt->execute(['name' => 'Софтуерно Инженерство', 'degree' => 'бакалавър']);

$stmt->execute(['name' => 'Софтуерни Технологии', 'degree' => 'магистър']);

Съвет

За проектите няма да са нужни сложни заявки -> направете си някакви помощни функции/клас(ове), които ви правят базовите заявки.
Можете да си направите един db.php файл, който да комуникира с базата, а на останалите места използвайте него - утре може да решите, че предпочитате mongo.
Когато пишете код - ако забелязвате, че copy-paste-вате, то може би правите нещо грешно.
Coding style - (camelCase|snake_case), (spaces|tabs), (same|new line {) - choose one and stick with it.

Други неща

Транзакции, bind-ване на параметри - няма да ви трябват

$conn->beginTransaction();
...
$dbh->commit(); // or $conn->rollBack();

$stmt->bindParam(':name', $name);     // bind to variable
$stmt->bindValue(':degree', $degree); // bind to value
$stmt->execute();

Задача 1

Имате база от данни със следната таблица и данни:

CREATE TABLE electives (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(128),
  description VARCHAR(1024),
  lecturer VARCHAR(128)
);

INSERT INTO electives (title, description, lecturer)
VALUES
  ("Programming with Go", "Let's learn Go", "Nikolay Batchiyski"),
  ("AKDU", "Let's Graduate", "Svetlin Ivanov"),
  ("Web technologies", "Let's learn the web", "Milen Petrov");

Имплементирайте php страница с форма и валидация за добавяне на избираема дисциплина.

Добавете колона created_at на таблицата electives, коята да сочи момента на добавяне на реда.

Задача 2

Добавете функционалност за редактиране на избираема дисциплина.

HTTP GET на /electives.php/1 трябва да върне формата с попълнени текущи стойности.

HTTP POST на /electives.php/1 със съответните параметри трябва да промени избираемата с id 1.

Забележка - последното би трябвало да е PUT, а не POST, но с чист HTML без JS това не е възможно. Когато вземем JS ще видите правилния начин.

Въпроси

Moodle