Mantenimiento categorías

<!DOCTYPE html>
<!--
To change this license header, choose License Headers in Project Properties.
To change this template file, choose Tools | Templates
and open the template in the editor.
-->
<html>
    <head>
        <title>Mantenimiento categorías</title>
        <meta charset="UTF-8">
        <meta name="viewport" content="width=device-width, initial-scale=1.0">
        <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.1.0/css/bootstrap.min.css">

        <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>

        <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.0/umd/popper.min.js"></script>

        <script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.1.0/js/bootstrap.min.js"></script>

    </head>
    <body>
        <?php
        require_once 'libreria.php';
        $nombre = filter_input(INPUT_GET, 'nombre', FILTER_SANITIZE_STRING);
        $id = filter_input(INPUT_GET, 'idcategoria', FILTER_VALIDATE_INT);
        if (!empty($nombre)) {
            insertarCategoria($nombre);
        }
        if (!empty($id)) {
            borrarCategoria($id);
        }

        $id = filter_input(INPUT_GET, 'id', FILTER_VALIDATE_INT);
        $nombre = filter_input(INPUT_GET, 'categoria', FILTER_SANITIZE_STRING);
        if (!empty($id) && !empty($nombre)) {
            editarCategoria($id, $nombre);
        }
        ?>
        <h1>Mantenimiento categorías</h1>
        <form>Nombre: <input type="text" name="nombre"><input type="submit" class="btn btn-success"></form>
        <hr/>
        <?php
        listadoCategorias();
        ?>
    </body>
</html>
<!DOCTYPE html>
<!--
To change this license header, choose License Headers in Project Properties.
To change this template file, choose Tools | Templates
and open the template in the editor.
-->
<html>
    <head>
       <title>Editar categoría</title>
        <meta charset="UTF-8">
        <meta name="viewport" content="width=device-width, initial-scale=1.0">
        <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.1.0/css/bootstrap.min.css">

        <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>

        <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.0/umd/popper.min.js"></script>

        <script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.1.0/js/bootstrap.min.js"></script>

    </head>
    <body>
        <?php
        require_once 'libreria.php';
        $id = filter_input(INPUT_GET, 'idcategoria', FILTER_VALIDATE_INT);
        if(!empty($id)){
            $categoria=getCategoria($id);
            
        }
        ?>
        <form action="categorias.php">
            <input type="hidden" name="id" value="<?=$categoria['idcategorias']?>">
            Nombre: <input type="text" name="categoria" value="<?=$categoria['nombre']?>">
            <input type="submit" class="btn btn-success">
        </form>
        <a href="categorias.php">Volver a categorías</a>
    </body>
</html>
<?php

function conectar() {
    $server = "localhost";
    $user = "root";
    $password = "";
    $db = "tienda";
    try {
        $conn = new PDO("mysql:host=$server;dbname=$db", $user, $password, [PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8'"]);
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        return $conn;
    } catch (Exception $ex) {
        echo $ex->getMessage();
    }
}

function insertarCategoria($name) {
    try {
        $conn = conectar();
        $st = $conn->prepare("insert into categorias (nombre) values (:name)");
        $st->execute(['name' => $name]);
    } catch (Exception $ex) {
        echo $ex->getMessage();
    }
}

function borrarCategoria($id) {
    try {
        $conn = conectar();
        $st = $conn->prepare("delete from categorias where idcategorias=:id");
        $st->execute(['id' => $id]);
    } catch (Exception $ex) {
        echo $ex->getMessage();
    }
}

function editarCategoria($id,$nombre){
    try{
        $conn=conectar();
        $st=$conn->prepare("update categorias set nombre=:nombre where idcategorias=:id");
        $st->execute(['nombre'=>$nombre,'id'=>$id]);
    } catch (Exception $ex) {

    }
}
function getCategorias() {
    try {
        $conn = conectar();
        $st = $conn->prepare("select * from categorias");
        $st->execute();
        $categorias = $st->fetchAll();
        return $categorias;
    } catch (Exception $ex) {
        echo $ex->getMessage();
    }
}

function getCategoria($id) {
    try {
        $conn = conectar();
        $st = $conn->prepare("select * from categorias where idcategorias=:id");
        $st->execute(['id' => $id]);
        $categoria = $st->fetch();
        return $categoria;
    } catch (Exception $ex) {
        echo $ex->getMessage();
    }
}

function listadoCategorias() {
    $categorias = getCategorias();
    ?>
    <table class="table">
        <tr><td>Id</td><td>Nombre</td><td>Acciones</td></tr>
        <?php
        foreach ($categorias as $categoria) {
            ?>
            <tr><td><?= $categoria['idcategorias'] ?></td>
                <td><?= $categoria['nombre'] ?></td>
                <td>
                    <a href="?idcategoria=<?= $categoria['idcategorias'] ?>">Borrar</a>
                    <a href="editar_categoria.php?idcategoria=<?= $categoria['idcategorias'] ?>">Editar</a>
                </td>
            </tr>
            <?php
        }
        ?>
    </table>
    <?php
}

Categorías Sakila

 <h1>Categorías</h1>
        <?php
        require_once 'libreria.php';
        $category = filter_input(INPUT_GET, 'category', FILTER_SANITIZE_STRING);
        $category_id = filter_input(INPUT_GET, 'category_id', FILTER_VALIDATE_INT);
        if (!empty($category)) {
            insertarCategoria($category);
        }
        if(!empty($category_id)){
            borraCategoria($category_id);
        }
        ?>
        <form>
            Categoría: <input type="text" name="category">
            <input type="submit" class="btn btn-success">
        </form>
        <hr/>
        <?php
        listadoCategorias();
        ?>

libreria.php

<?php

function conectar() {
    $server = "localhost";
    $user = "root";
    $password = "";
    $db = "sakila";
    try {
        $conn = new PDO("mysql:host=$server;dbname=$db", $user, $password, [PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8'"]);
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        return $conn;
    } catch (Exception $ex) {
        echo $ex->getMessage();
    }
}

function insertarCategoria($name) {
    try {
        $conn = conectar();
        $st = $conn->prepare("insert into category (name) values (:name)");
        $st->execute(['name' => $name]);
    } catch (Exception $ex) {
        echo $ex->getMessage();
    }
}

function borraCategoria($id) {
    try {
        $conn = conectar();
        $st = $conn->prepare("delete from category where category_id=:id");
        $st->execute(['id' => $id]);
    } catch (Exception $ex) {
        echo $ex->getMessage();
    }
}
function getCategorias() {
    try {
        $conn = conectar();
        $st = $conn->prepare("select * from category");
        $st->execute();
        $categorias = $st->fetchAll();
        return $categorias;
    } catch (Exception $ex) {
        echo $ex->getMessage();
    }
}



function listadoCategorias() {
    echo "hola";
    $categorias = getCategorias();
    ?>
    <table class="table">
        <tr><td>Id</td><td>Nombre</td><td>Acciones</td></tr>
        <?php
        foreach ($categorias as $categoria) {
            ?>
            <tr><td><?= $categoria['category_id'] ?></td>
                <td><?= $categoria['name'] ?></td>
                <td><a href="?category_id=<?= $categoria['category_id'] ?>">Borrar</a></td>
            </tr>
            <?php
        }
        ?>
    </table>
    <?php
}

BD tienda

CREATE TABLE `tienda`.`categorias` (
  `idcategorias` INT NOT NULL AUTO_INCREMENT,
  `nombre` VARCHAR(100) NOT NULL,
  PRIMARY KEY (`idcategorias`));

CREATE TABLE `tienda`.`productos` (
  `idproductos` INT NOT NULL AUTO_INCREMENT,
  `nombre` VARCHAR(45) NULL,
  `precio` DECIMAL(6,4) NULL,
  `descripcion` VARCHAR(500) NULL,
  `idcategorias` INT NULL,
  PRIMARY KEY (`idproductos`),
  INDEX `fk_categorias_idx` (`idcategorias` ASC),
  CONSTRAINT `fk_categorias`
    FOREIGN KEY (`idcategorias`)
    REFERENCES `tienda`.`categorias` (`idcategorias`)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT);

Ejemplo trigger

CREATE TABLE `customer_log` (
 `idcustomer_log` int(11) NOT NULL AUTO_INCREMENT,
 `customer_id` int(11) DEFAULT NULL,
 `user` varchar(45) DEFAULT NULL,
 `fecha` datetime DEFAULT NULL,
 `nombre` varchar(45) DEFAULT NULL,
 `nombre_ant` varchar(45) DEFAULT NULL,
 PRIMARY KEY (`idcustomer_log`)
)

DELIMITER //
CREATE TRIGGER cliente_actualizado BEFORE UPDATE ON customer
FOR EACH ROW BEGIN

 DECLARE vUser varchar(50);

 -- Find username of person performing the INSERT into table
 SELECT USER() INTO vUser;

 -- Insert record into audit table
 INSERT INTO customer_log
 ( customer_id,
 fecha,
 user,nombre,nombre_ant)
 VALUES
 ( NEW.customer_id,
 SYSDATE(),
 vUser,NEW.first_name, OLD.first_name );

END;//

DELIMITER ;

Ejemplos desencadenadores (triggers)

Al borrar:

CREATE TRIGGER `sakila`.`actor_BEFORE_DELETE` BEFORE DELETE ON `actor` FOR EACH ROW
BEGIN

if OLD.actor_id between 1 and 20 then
 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'No puedes borrar';
else
 insert into actor_log(first_name, last_name) values (OLD.first_name, OLD.last_name);
end if;
END

Al insertar:

CREATE DEFINER=`root`@`localhost` TRIGGER `sakila`.`payment_BEFORE_INSERT` BEFORE INSERT ON `payment` FOR EACH ROW
BEGIN
if NEW.amount>=10 then
 set NEW.amount=9.99;
end if;
END

Al modificar:

CREATE DEFINER=`root`@`localhost` TRIGGER `sakila`.`payment_BEFORE_UPDATE` BEFORE UPDATE ON `payment` FOR EACH ROW
BEGIN

if NEW.amount > OLD.amount then
 set NEW.amount=OLD.amount;
end if;

END

Ejercicios Northwind

Insertar en Customers la compañía ‘Compañía XX’ con ‘Ana’, ‘Pi’ como contacto

insert into customers (company,first_name,last_name)
values ('Compañía XX','Ana','Pi')

Modificar en Customer el campo email_address y poner ‘Sin email’

update customers
set email_address='Sin email'

Eliminar el Customer con id = 20

delete from customers
where id=20

Seleccionar todos los proveedores (suppliers) cuyo trabajo sea ‘Sales Manager’

SELECT * from suppliers where job_title='Sales manager'

Seleccionar todos los pedidos (orders) de junio

select * from orders
where month(order_date)=6

Calcular el número de pedidos por cliente

SELECT company,count(orders.id) total FROM customers left join orders on customers.id=orders.customer_id
group by customers.id

Calcular el total de ventas por empleado

SELECT last_name,first_name,sum(quantity*unit_price*(1-discount)) total FROM employees join orders on employees.id=orders.employee_id
join order_details on orders.id=order_details.order_id
group by employees.id

Averiguar el producto más caro

SELECT * FROM `products` order by list_price desc limit 1

Averiguar el número de ventas por producto

select products.*, sum(quantity*unit_price*(1-discount)) total from products join order_details on products.id= order_details.product_id
group by products.id

Mostrar los clientes con más de 10000 en ventas

select customers.*, sum(quantity*unit_price*(1-discount)) total from customers join orders on customers.id= orders.customer_id
join order_details on orders.id= order_details.order_id
group by customers.id
having total>10000