Ćwiczenia SQL i PHP

Ćwiczenie 1: Tworzenie bazy danych

Zadanie: Stwórz bazę danych o nazwie sklep i tabelę produkty zawierającą kolumny:

id (INT, klucz główny, auto-increment)
nazwa (VARCHAR(100))
cena (DECIMAL(10, 2))

Rozwiązanie

CREATE DATABASE sklep;

USE sklep;

CREATE TABLE produkty (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nazwa VARCHAR(100) NOT NULL,
    cena DECIMAL(10, 2) NOT NULL
);

Ćwiczenie 2: Wstawianie danych do tabeli

Zadanie: Dodaj do tabeli produkty trzy przykładowe rekordy:

Produkt: "Mysz komputerowa", Cena: 49.99
Produkt: "Klawiatura", Cena: 89.99
Produkt: "Monitor", Cena: 599.99

Rozwiązanie

INSERT INTO produkty (nazwa, cena) VALUES
    ('Mysz komputerowa', 49.99),
    ('Klawiatura', 89.99),
    ('Monitor', 599.99);

Ćwiczenie 3: Pobieranie danych z bazy w PHP

Zadanie: Napisz skrypt PHP, który połączy się z bazą sklep i wyświetli wszystkie produkty w tabeli HTML.

Rozwiązanie

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "sklep";

$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {
    die("Błąd połączenia: " . $conn->connect_error);
}

$sql = "SELECT * FROM produkty";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    echo "";
    while ($row = $result->fetch_assoc()) {
        echo "";
    }
    echo "ID Nazwa Cena
" . $row['id'] . " " . $row['nazwa'] . " " . $row['cena'] . "";
} else {
    echo "Brak produktów";
}

$conn->close();
?>

ID	Nazwa	Cena
" . $row['id'] . "	" . $row['nazwa'] . "	" . $row['cena'] . "

Ćwiczenie 4: Modyfikacja danych w tabeli

Zadanie: Zaktualizuj cenę produktu "Klawiatura" na 99.99 w tabeli produkty.

Rozwiązanie

UPDATE produkty
        SET cena = 99.99
        WHERE nazwa = 'Klawiatura';

Ćwiczenie 5: Usuwanie danych z tabeli

Zadanie: Usuń produkt "Monitor" z tabeli produkty.

Rozwiązanie

DELETE FROM produkty
        WHERE nazwa = 'Monitor';

Ćwiczenie 6: Selekcja danych z warunkiem

Zadanie: Wybierz produkty, których cena jest wyższa niż 100.

Rozwiązanie

SELECT * FROM produkty
        WHERE cena > 100;

Ćwiczenie 7: Łączenie tabel

Zadanie: Załóżmy, że masz tabelę zamowienia z kolumnami id, produkt_id, data. Połącz tabelę zamowienia z tabelą produkty na podstawie produkt_id i wybierz nazwę produktu oraz datę zamówienia.

Rozwiązanie

SELECT produkty.nazwa, zamowienia.data
        FROM zamowienia
        JOIN produkty ON zamowienia.produkt_id = produkty.id;

Ćwiczenie 8: Agregacja danych (Funkcje agregujące)

Zadanie: Oblicz średnią cenę produktów w tabeli produkty.

Rozwiązanie

SELECT AVG(cena) AS srednia_cena
        FROM produkty;

Ćwiczenie 9: Grupowanie danych

Zadanie: Pogrupuj produkty w tabeli produkty po cenach, licząc ile produktów znajduje się w danej grupie cenowej.

Rozwiązanie

SELECT cena, COUNT(*) AS liczba_produktow
        FROM produkty
        GROUP BY cena;

Ćwiczenie 10: Tworzenie widoku

Zadanie: Stwórz widok, który będzie zawierał tylko produkty o cenie powyżej 100.

Rozwiązanie

CREATE VIEW drogie_produkty AS
        SELECT * FROM produkty
        WHERE cena > 100;

Ćwiczenie 11: Użycie indeksu

Zadanie: Utwórz indeks na kolumnie nazwa w tabeli produkty w celu przyspieszenia wyszukiwania.

Rozwiązanie

CREATE INDEX idx_nazwa ON produkty(nazwa);

Ćwiczenie 12: Procedura składowana

Zadanie: Stwórz procedurę, która zwróci wszystkie produkty, których cena jest większa niż podana wartość.

Rozwiązanie

DELIMITER //
        CREATE PROCEDURE GetProductsByPrice(IN price DECIMAL(10,2))
        BEGIN
            SELECT * FROM produkty WHERE cena > price;
        END //
        DELIMITER ;

Ćwiczenie 13: Wyzwalacz

Zadanie: Stwórz wyzwalacz, który będzie aktualizował datę modyfikacji w tabeli produkty, gdy cena produktu zostanie zmieniona.

Rozwiązanie

CREATE TRIGGER UpdatePriceTimestamp
        BEFORE UPDATE ON produkty
        FOR EACH ROW
        SET NEW.updated_at = NOW();

Ćwiczenie 14: Tworzenie formularza w PHP

Zadanie: Stwórz formularz w PHP, który pozwala na dodanie nowego produktu do tabeli produkty z poziomu przeglądarki.

Rozwiązanie

<form method="POST" action="add_product.php">
            Nazwa: <input type="text" name="nazwa"><br>
            Cena: <input type="text" name="cena"><br>
            <input type="submit" value="Dodaj Produkt">
        </form>
        
        <?php
        if ($_SERVER['REQUEST_METHOD'] == 'POST') {
            $nazwa = $_POST['nazwa'];
            $cena = $_POST['cena'];
        
            $conn = new mysqli($servername, $username, $password, $dbname);
            $stmt = $conn->prepare("INSERT INTO produkty (nazwa, cena) VALUES (?, ?)");
            $stmt->bind_param("sd", $nazwa, $cena);
            $stmt->execute();
            echo "Produkt dodany!";
        }
        ?>

Ćwiczenie 15: Filtrowanie wyników w PHP

Zadanie: Napisz skrypt PHP, który pozwala na filtrowanie produktów po nazwie.

Rozwiązanie

<form method="GET" action="filter.php">
            Filtruj po nazwie: <input type="text" name="nazwa"><br>
            <input type="submit" value="Filtruj">
        </form>
        
        <?php
        if (isset($_GET['nazwa'])) {
            $nazwa = $_GET['nazwa'];
        
            $conn = new mysqli($servername, $username, $password, $dbname);
            $sql = "SELECT * FROM produkty WHERE nazwa LIKE '%$nazwa%'";
            $result = $conn->query($sql);
            while ($row = $result->fetch_assoc()) {
                echo $row['nazwa'] . " - " . $row['cena'] . "<br>";
            }
        }
        ?>

Ćwiczenie 16: Wyszukiwanie z użyciem LIKE

Zadanie: Zastosuj operator LIKE w zapytaniu SQL, aby znaleźć produkty, których nazwa zawiera słowo "Mysz".

Rozwiązanie

SELECT * FROM produkty
        WHERE nazwa LIKE '%Mysz%';

Ćwiczenie 17: Podstawy sesji w PHP

Zadanie: Zastosuj sesję w PHP, aby przechować informację o zalogowanym użytkowniku. Po zalogowaniu, wyświetl komunikat powitalny.

Rozwiązanie

<?php
session_start();

if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    $_SESSION['username'] = $_POST['username'];
}

if (isset($_SESSION['username'])) {
    echo "Witaj, " . $_SESSION['username'] . "!";
} else {
    echo "Zaloguj się, aby kontynuować.";
}
?>

<form method="POST" action="login.php">
    Nazwa użytkownika: <input type="text" name="username"><br>
    <input type="submit" value="Zaloguj">
</form>

Ćwiczenie 18: Formularz do rejestracji użytkownika

Zadanie: Napisz formularz rejestracji, który zapisuje dane użytkownika (imię, nazwisko, email) w bazie danych.

Rozwiązanie

<form method="POST" action="register.php">
    Imię: <input type="text" name="first_name"><br>
    Nazwisko: <input type="text" name="last_name"><br>
    Email: <input type="email" name="email"><br>
    <input type="submit" value="Zarejestruj">
</form>

<?php
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    $first_name = $_POST['first_name'];
    $last_name = $_POST['last_name'];
    $email = $_POST['email'];

    $conn = new mysqli($servername, $username, $password, $dbname);
    $stmt = $conn->prepare("INSERT INTO users (first_name, last_name, email) VALUES (?, ?, ?)");
    $stmt->bind_param("sss", $first_name, $last_name, $email);
    $stmt->execute();
    echo "Użytkownik zarejestrowany!";
}
?>

Ćwiczenie 19: Logowanie i sprawdzanie danych użytkownika

Zadanie: Stwórz formularz logowania, który sprawdza dane w bazie i pozwala na zalogowanie się użytkownika.

Rozwiązanie

<form method="POST" action="login.php">
    Email: <input type="email" name="email"><br>
    Hasło: <input type="password" name="password"><br>
    <input type="submit" value="Zaloguj">
</form>

<?php
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    $email = $_POST['email'];
    $password = $_POST['password'];

    $conn = new mysqli($servername, $username, $password, $dbname);
    $sql = "SELECT * FROM users WHERE email = ?";
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("s", $email);
    $stmt->execute();
    $result = $stmt->get_result();
    if ($result->num_rows > 0) {
        $user = $result->fetch_assoc();
        if (password_verify($password, $user['password'])) {
            echo "Zalogowano pomyślnie!";
        } else {
            echo "Niepoprawne hasło.";
        }
    } else {
        echo "Użytkownik nie istnieje.";
    }
}
?>

Ćwiczenie 20: Użycie AJAX w PHP

Zadanie: Napisz skrypt w PHP i JavaScript, który umożliwia dodawanie nowych produktów do bazy danych bez przeładowywania strony.

Rozwiązanie

<form id="productForm">
    Nazwa: <input type="text" id="nazwa"><br>
    Cena: <input type="text" id="cena"><br>
    <button type="button" onclick="addProduct()">Dodaj Produkt</button>
</form>

<script>
function addProduct() {
    var nazwa = document.getElementById('nazwa').value;
    var cena = document.getElementById('cena').value;

    var xhr = new XMLHttpRequest();
    xhr.open('POST', 'add_product.php', true);
    xhr.setRequestHeader('Content-Type', 'application/x-www-form-urlencoded');
    xhr.onreadystatechange = function() {
        if (xhr.readyState == 4 && xhr.status == 200) {
            alert('Produkt dodany');
        }
    };
    xhr.send('nazwa=' + nazwa + '&cena=' + cena);
}
</script>

<?php
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    $nazwa = $_POST['nazwa'];
    $cena = $_POST['cena'];

    $conn = new mysqli($servername, $username, $password, $dbname);
    $stmt = $conn->prepare("INSERT INTO produkty (nazwa, cena) VALUES (?, ?)");
    $stmt->bind_param("sd", $nazwa, $cena);
    $stmt->execute();
}
?>