<?php


$conn = new mysqli("localhost", "root", "", "contacts");
if ($conn){
    echo "Connected to the database";
} else {
    echo "error";
}



// Utwórz formularza (lista rozwijana) z nazwami kolumn w tabeli. Po przesłaniu danych z formularza
// wyświetla się tylko jedna kolumna z bazy. Na stronie nie mogą wyświetlać się błędy.

$sql = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'contacts'";
$result = $conn->query($sql);



?>

<p>Wybierz kolumnę</p>
<form action="index4.php" method="POST">
    <select name="columns">
        <?php
        for ($i = 0; $i < $result->num_rows; $i++){
            $row = $result->fetch_assoc();
            echo "<option value='{$row['COLUMN_NAME']}'>{$row['COLUMN_NAME']}</option>";
        }
        ?>
    </select>
    <p>Jak chcesz posortować?</p>
    <select name="sort">
        <option value="ASC">ASC</option>
        <option value="DESC">DESC</option>
    </select>
    <button type="submit">Submit</button>
<form>
<br>

<?php
if (isset($_POST['columns']) && isset($_POST['sort'])){
    $column_name = $_POST['columns'];
    $sort = $_POST['sort'];

    $sql = "SELECT $column_name FROM contacts ORDER BY $column_name $sort";
    $result = $conn->query($sql);
    if ($result->num_rows > 0){
        while ($row = $result->fetch_assoc()){
            echo $row[$column_name] . "<br>";
        }
    }
}
?>

<form action="index4.php" method="POST">
    <label>Wybierz kolumne</label>
    <select name="columns">
        <?php
        $sql = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'contacts'";
        $result = $conn->query($sql);

        for ($i = 0; $i < $result->num_rows; $i++){
            $row = $result->fetch_assoc();
            echo "<option value='{$row['COLUMN_NAME']}'>{$row['COLUMN_NAME']}</option>";
        }
        ?>
    </select>
    <label>Podaj ciąg znaków</label>
    <input name="search">
    <button type="submit">Submit</button>

    <?php
    if (isset($_POST['search'])){
        $search = $_POST['search'];
        $sql = "SELECT * FROM contacts WHERE name LIKE " . "'%" . "$search" . "%'";
        $result = $conn->query($sql);
        for ($i = 0; $i < $result->num_rows; $i++){
            $row = $result->fetch_assoc();
            echo "<p>{$row['name']}</p>";
        }
    }


    ?>
<form>

<!-- Wyświetl dane z bazy w postaci tabeli HTML -->

<table>
    <tr>
        <th>Id</th>
        <th>Name</th>
        <th>Email</th>
        <th>Phone</th>
        <th>Created at</th>
    </tr>

    <?php
    $query = "SELECT * FROM contacts";
    $result = $conn->query($query);
    for ($i = 0; $i < $result->num_rows; $i++){
        $row = $result->fetch_assoc();
        echo "<tr>";
        echo "<td>{$row['id']}</td>";
        echo "<td>{$row['name']}</td>";
        echo "<td>{$row['email']}</td>";
        echo "<td>{$row['phone']}</td>";
        echo "<td>{$row['created_at']}</td>";
    }
    ?>

</table>

<!-- Napisz skrypt, który będzie wyświetlał z bazy dane wybranej osoby. -->

<form action="index4.php" method="POST">
    <label>Podaj id</label>
    <input name="id">
    <button type="submit">Submit</button>

    <?php

    if (isset($_POST['id'])){
        $query = "SELECT * FROM contacts WHERE id = " . $_POST['id'];
        $result = $conn->query($query);
        if ($result->num_rows > 0){
            $row = $result->fetch_assoc();
            echo "<p>{$row['name']}</p>";
            echo "<p>{$row['email']}</p>";
            echo "<p>{$row['phone']}</p>";
            echo "<p>{$row['created_at']}</p>";
        }
    }

    ?>
</form>



<br>