Creació de la BD i les seves taules (A5.2)

De Wiki personal d'en Guillem Serrat

Estructura de la BD

Aquesta aplicació tindrà una BD anomenada “botiga” amb codificació UTF8 

$sql = "CREATE DATABASE IF NOT EXISTS botiga CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci";

Amb els següents permisos: 

$permisosRW = "GRANT SELECT, INSERT, UPDATE, DELETE ON botiga.* TO 'iot'@'localhost';";
$permisosR = "GRANT SELECT ON botiga.* TO 'convidat'@'localhost';";

Aquesta BD comptarà amb tres taules:

  • Taula "productes", per emmagatzemar els productes del catàleg i els seus detalls
  • Taula "compres", per emmagatzemar la data i el total de les compres realitzades
  • Taula "compres_detall", per emmagatzemar els detalls de les compres realitzades (quins productes, quina quantitat, etc)

Taula "productes"

A la taula productes s'emmagatzemarà el següent, en format UTF-8:

  • ID
  • Nom
  • Preu
  • Descripció
  • Estoc
  • Categoria
  • Imatge (Ruta del servidor on es desa la imatge, no el binari)
$sql = "CREATE TABLE productes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nom VARCHAR(100) NOT NULL,
    preu DECIMAL(10, 2) NOT NULL,
    descripcio TEXT,
    estoc INT NOT NULL DEFAULT 1,
    categoria VARCHAR(30) NOT NULL,
    imatge VARCHAR(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;";

Per defecte, el catàleg tindrà els següents productes:

$sql = "INSERT INTO productes (nom, preu, descripcio, estoc, categoria, imatge) VALUES



   ('Portàtil', 899.99, 'Portàtil amb 16GB RAM i SSD 512GB', 10, 'PC', 'portatil.jpg'),
   ('Ratolí sense fils', 29.99, 'Ratolí ergonòmic amb connexió Bluetooth', 50, 'Perifèric', 'ratoli.jpg'),
   ('Teclat mecànic', 119.99, 'Teclat RGB amb switches mecànics', 25, 'Perifèric', 'teclat.jpg'),
   ('Monitor 27', 299.99, 'Monitor Full HD 27 polzades', 15, 'Perifèric', 'monitor.jpg'),
   ('Webcam HD', 79.99, 'Càmera web 1080p amb micròfon', 30, 'Video, Perifèric', 'webcam.jpg'),
   ('Auriculars', 59.99, 'Auriculars amb cancel·lació de soroll', 40, 'Audio, Perifèric', 'auriculars.jpg');";

Taula "compres"

A la taula compres únicament s'emmagatzemarà, en format UTF-8:

  • ID
  • Data de la compra
  • Total
$sql = "CREATE TABLE compres (
    id INT AUTO_INCREMENT PRIMARY KEY,
    data_compra DATETIME NOT NULL,
    total float NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;";

Taula "compres_detall"

A la taula compres_detall es desen en format UTF-8 els detalls (productes) d'una compra de la taula compres. En concret

  • ID (del detall)
  • ID de compra (Clau forània a la taula compres, per relacionar cada detall amb la seva compra)
  • ID de producte (Clau forània a la taula productes, per relacionar cada ID de producte amb el seu nom, categoria, stock, etc)
  • Quantitat que es compra
  • Preu unitari al que es compra
  • Total del producte (Quantitat * Preu unitari)
$sql = "CREATE TABLE compres_detall (
    id INT AUTO_INCREMENT PRIMARY KEY,
    compra_id INT NOT NULL,
    producte_id INT NOT NULL,
    quantitat INT NOT NULL,
    preu_unitari DECIMAL(10,2) NOT NULL,
    total_producte float NOT NULL,

    FOREIGN KEY (compra_id) REFERENCES compres(id),
    FOREIGN KEY (producte_id) REFERENCES productes(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;";

Per tant, un exemple de model de dades seria el següent:

  • Taula Compres
    • Registre 1
      • ID 1
      • Data 2026-01-04 20:28:11
      • Total 200€
  • Taula compres_detall
    • Registre 1
      • ID 1
      • compra_id 1 (Mateixa compra)
      • producte_id 1 (Diferent producte)
      • quantitat 2
      • preu unitari 50
      • total_producte 100
    • Registre 2
      • ID 2
      • compra_id 1 (Mateixa compra)
      • producte_id 1 (Diferent producte)
      • quantitat 4
      • preu unitari 25
      • total_producte 100

Codi complet

<!DOCTYPE html>
<html lang="ca">
    <head>
        <title>Creació BD i Taula</title>
        <meta http-equiv="Content-type" content="text/html; charset=UTF-8">
    </head>
    <body>
        <?php
            // Definim els paràmetres per realitzar la connexió
            $servername = "127.0.0.1"; 
            $username = "root"; 
            $password = "fjeclot";

            try {   
                $conn = new PDO("mysql:host=$servername", $username, $password); 
                $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
                
                // Creem la BD botiga amb codificació UTF8
                $sql = "CREATE DATABASE IF NOT EXISTS botiga CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci"; 

                $conn->exec($sql); 

                // Afegim permissos a diversos usuarios sobre la BD
                $permisosRW = "GRANT SELECT, INSERT, UPDATE, DELETE ON botiga.* TO 'iot'@'localhost';";
                $permisosR = "GRANT SELECT ON botiga.* TO 'convidat'@'localhost';";

                $conn->exec($permisosRW);
                $conn->exec($permisosR);
                $conn->exec("FLUSH PRIVILEGES;");

                
                
                // Segona connexió per crear la taula
                $servidor = "127.0.0.1"; 
                $usuari = "root"; 
                $contrasenya = "fjeclot";
                $nomDB = "botiga";

                $conn = new PDO("mysql:host=$servidor;dbname=$nomDB", $usuari, $contrasenya);
                $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
                
                // Creem la taula productes, on s'emmagatzemen els productes per vendre al catàleg
                $sql = "CREATE TABLE productes (
                id INT AUTO_INCREMENT PRIMARY KEY,
                nom VARCHAR(100) NOT NULL,
                preu DECIMAL(10, 2) NOT NULL,
                descripcio TEXT,
                estoc INT NOT NULL DEFAULT 1,
                categoria VARCHAR(30) NOT NULL,
                imatge VARCHAR(30) NOT NULL
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;";

                $conn->exec($sql); 

                // Inserim productes
                $sql = "INSERT INTO productes (nom, preu, descripcio, estoc, categoria, imatge) VALUES
                ('Portàtil', 899.99, 'Portàtil amb 16GB RAM i SSD 512GB', 10, 'PC', 'portatil.jpg'),
                ('Ratolí sense fils', 29.99, 'Ratolí ergonòmic amb connexió Bluetooth', 50, 'Perifèric', 'ratoli.jpg'),
                ('Teclat mecànic', 119.99, 'Teclat RGB amb switches mecànics', 25, 'Perifèric', 'teclat.jpg'),
                ('Monitor 27', 299.99, 'Monitor Full HD 27 polzades', 15, 'Perifèric', 'monitor.jpg'),
                ('Webcam HD', 79.99, 'Càmera web 1080p amb micròfon', 30, 'Video, Perifèric', 'webcam.jpg'),
                ('Auriculars', 59.99, 'Auriculars amb cancel·lació de soroll', 40, 'Audio, Perifèric', 'auriculars.jpg');";

                $conn->exec($sql); 

                // Creem la taula on es registrarà les compres (únicament la data de la compra i el total d'aquesta)
                // Posteriorment, es crea una taula per desar-hi els detalls
                    $sql = "CREATE TABLE compres (
                        id INT AUTO_INCREMENT PRIMARY KEY,
                        data_compra DATETIME NOT NULL,
                        total float NOT NULL
                    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;";

                $conn->exec($sql); 

                // Creem la taula per desar els detalls de les compres. Desarem de cada compra quin productes ha comprat i els seus detalls (quantitat, preu unitari i total, etc)
                // Relacionarem ID de compra amb la taula compres per identificar la compra a la que correspon el detall del producte
                // Relacionarem la ID de producte amb la taula productes per identificar quin producte és
                $sql = "CREATE TABLE compres_detall (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    compra_id INT NOT NULL,
                    producte_id INT NOT NULL,
                    quantitat INT NOT NULL,
                    preu_unitari DECIMAL(10,2) NOT NULL,
                    total_producte float NOT NULL,

                    FOREIGN KEY (compra_id) REFERENCES compres(id),
                    FOREIGN KEY (producte_id) REFERENCES productes(id)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;";

                $conn->exec($sql); 
                echo "Base de Dades i taula creades correctament<br>"; 

            } catch(PDOException $e) {
                echo $sql . "<br>" . $e->getMessage(); 
            }

            $conn = null;
            ?>
        <h1>Codi en PHP</h1>
        <?php
            show_source("creacioBDiTaula.php");
        ?>
    </body>
</html>
Obtingut de «https://wiki.gserrat.cat/index.php?title=Creació_de_la_BD_i_les_seves_taules_(A5.2)&oldid=463»