Administració de la Base de Dades

Administració de la BBDD

Creació de la base de dades

Per crear la base de dades primer hem de realitzar una connexió a MariaDB amb l'usuari root

// Definim els paràmetres per realitzar la connexió
$servername = "127.0.0.1"; 
$username = "root"; 
$password = "fjeclot";

Seguidament, crearem la taula

// Definim un nou objecte de la classe PDO amb els atributs: host al que ens connectarem, el nom d'usuari i contrasenya.    
$conn = new PDO("mysql:host=$servername", $username, $password); 
// A través de la funció setAttribute, agafem el tipus d'error en cas de que n'hi hagi algun
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// Definim una variable que és l'ordre que s'executara, la qual és crear una BBDD nova amb nom "myDBPDO"
$sql = "CREATE DATABASE Usuaris"; 
// Dins de la connexió, executem la variable sql (que és l'ordre vista anteriorment)

$conn->exec($sql);

Degut a que crearem dues connexions diferents (una de lectura i una altra d'escriptura), donarem dos permisos diferents a dos usuaris diferents

$permisosRW = "GRANT ALL PRIVILEGES ON Usuaris.* TO 'iot'@'localhost';";
$permisosR = "GRANT SELECT ON Usuaris.* TO 'convidat'@'localhost';";

$conn->exec($permisosRW);
$conn->exec($permisosR);

Creació de la taula

Per connectar-nos farem servir l'usuari root amb la BBDD creada anteriorment

// Definim els paràmetres per realitzar la connexió
$servidor = "127.0.0.1"; 
$usuari = "root"; 
$contrasenya = "fjeclot";
$nomDB = "Usuaris";

La taula serà la següent:

// Definim una variable que és l'ordre que s'executara, la qual és crear una BBDD nova amb nom "myDBPDO"
$sql = "CREATE TABLE Usuaris (
      id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
      tipusDocument ENUM('NIF', 'NIE', 'Passaport') NOT NULL,
      numeroDocument VARCHAR(30) UNIQUE NOT NULL,
      nom VARCHAR(50) NOT NULL,
      cognoms VARCHAR(50) NOT NULL,
      sexe ENUM('home', 'dona') NOT NULL,
      dataNaixement date NOT NULL,
      email VARCHAR(50) NOT NULL,
      telf VARCHAR(9) NOT NULL,
      poblacio ENUM('Barcelona', 'Girona', 'Lleida', 'Tarragona', 'Sabadell') NOT NULL,
      codiPostal VARCHAR(5) NOT NULL,
      adreca VARCHAR(50) NOT NULL,
      periodicitat ENUM('Anual', 'Semestral', 'Mensual') NOT NULL,
      rebreComunicacions ENUM('Si', 'No') NOT NULL,
      contrasenya VARCHAR(50),
      hash VARCHAR(100),
      observacions VARCHAR(150),
      arxiu MEDIUMBLOB NOT NULL,
      reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)";

Altres opcions d'administració

Esborrament de la BBDD

Per tal d'agilitzar processos d'administració de la BBDD, s'ha realitzat un codi per poder esborrar la base de dades mitjançant PHP

// Definim un nou objecte de la classe PDO amb els atributs: host al que ens connectarem, el nom d'usuari i contrasenya.    
$conn = new PDO("mysql:host=$servername", $username, $password); 
                
// A través de la funció setAttribute, agafem el tipus d'error en cas de que n'hi hagi algun
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
                
// Definim una variable que és l'ordre que s'executara, la qual és crear una BBDD nova amb nom "myDBPDO"
$sql = "DROP DATABASE Usuaris"; 
                
// Dins de la connexió, executem la variable sql (que és l'ordre vista anteriorment)
$conn->exec($sql); 

// Si es crea correctament
echo "Base de Dades eliminada correctament<br>";

Mostra d'usuaris

Per mostrar tots els usuaris de la BBDD en format taula, s'ha implementat el següent codi

try {
    $stmt = $conn->prepare("SELECT
            id, 
            tipusDocument AS 'Tipus de document',
            numeroDocument AS 'Nº Document',
            nom AS 'Nom',
            cognoms AS 'Cognoms',
            sexe AS 'Sexe',
            dataNaixement AS 'Data de naixement',
            email AS 'Email',
            telf AS 'Telèfon',
            poblacio AS 'Població',
            codiPostal AS 'Codi postal',
            adreca AS 'Adreça',
            periodicitat AS 'Periodicitat de cobrament',
            rebreComunicacions AS 'Accepta comunicacions',
            contrasenya AS 'Contrasenya',
            observacions AS 'Observacions',
            reg_date AS 'Data de registre',
            arxiu FROM Usuaris");
    $stmt->execute();
    $result = $stmt->fetchAll(PDO::FETCH_ASSOC);

    if (!$result) {
        echo "<p>No hi ha usuaris a la base de dades.</p>";
    } else {
        echo "<table>";
        echo "<tr>";
        foreach (array_keys($result[0]) as $col) {
            if ($col !== 'arxiu') echo "<th>" . htmlspecialchars($col) . "</th>";
        }
        echo "<th>PDF</th>"; // nova columna
        echo "</tr>";

        foreach ($result as $fila) {
            echo "<tr>";
            foreach ($fila as $col => $valorCol) {
                if ($col !== 'arxiu') echo "<td>" . htmlspecialchars($valorCol) . "</td>";
            }

            // Botó per descarregar PDF
            echo '<td>';
            if (!empty($fila['arxiu'])) {
                echo '<a class="button" href="../php/descarregarPDF.php?id=' . intval($fila['id']) . '">Descarregar PDF</a>';
            } else {
                echo 'No hi ha PDF';
            }
            echo '</td>';

            echo "</tr>";
        }
        echo "</table>";
    }
} catch (PDOException $e) {
    echo "<p style='color:red;'>Error amb la base de dades: " . htmlspecialchars($e->getMessage()) . "</p>";
}

Connexions a la BBDD

Connexió de lectura (R)

Per tots aquells codis que únicament hagin de consultar dades i no escriure'n, utilitzaran aquesta connexió d'únicament de lectura sobre la BBDD

<?php 
    $servidor = "127.0.0.1";
    $usuari = "convidat";
    $contrasenya = "benvingut";
    $nomDB = "Usuaris";
    $nomTaula = "Usuaris";

    try {
        // Definim un nou objecte de la classe PDO amb els atributs: host al que ens connectarem, la base de dades, el nom d'usuari i contrasenya.
        $conn = new PDO("mysql:host=$servidor;dbname=$nomDB", $usuari, $contrasenya);
                
        // A través de la funció setAttribute, agafem el tipus d'error en cas de que n'hi hagi algun
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);                    
                
        } catch(PDOException $e) {                                      // En cas de que hi hagi un error, PHP llença una PDOException, i la variable $e agafa aquesta excepció
            echo "No es pot connectar. Motiu: " . $e->getMessage();     // Es mostra el missatge
        }
?>

En aquesta connexió es fa servir l'usuari Convidat, amb únicament permisos de lectura a la BBDD

Connexió d'escriptura (RW)

Per tots aquells codis que únicament hagin de modificar la BBDD, utilitzaran la següent connexió:

<?php 
    $servidor = "127.0.0.1";
    $usuari = "iot";
    $contrasenya = "iot";
    $nomDB = "Usuaris";
    $nomTaula = "Usuaris";

    try {
        // Definim un nou objecte de la classe PDO amb els atributs: host al que ens connectarem, la base de dades, el nom d'usuari i contrasenya.
        $conn = new PDO("mysql:host=$servidor;dbname=$nomDB", $usuari, $contrasenya);
                
        // A través de la funció setAttribute, agafem el tipus d'error en cas de que n'hi hagi algun
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);                         
                
        } catch(PDOException $e) {                                      // En cas de que hi hagi un error, PHP llença una PDOException, i la variable $e agafa aquesta excepció
            echo "No es pot connectar. Motiu: " . $e->getMessage();     // Es mostra el missatge
        }
?>

En aquesta connexió es fa servir l'usuari IOT, amb permisos d'escriptura a la BBDD