Categoría: BD

Ejercicio academia

Crear un diseño entidad relación que permita controlar el sistema de información de una academia de cursos siguiendo estas premisas:

  • Los datos que se almacenan de los alumnos son el DNI, dirección, nombre, teléfono y la edad
  • Los cursos que imparte la academia se identifican con un código de curso. Además se almacena el programa del curso, las horas de duración del mismo, el título y cada vez que se imparte se anotará las fechas de inicio y fin del curso junto con un número concreto de curso (distinto del código) y los datos del profesor o profesora (sólo uno por curso) que son: dni, nombre, apellidos, dirección y teléfono
  • Se almacena la nota obtenida por cada alumno en cada curso teniendo en cuenta que un mismo alumno o alumna puede realizar varios cursos y en cada cual obtendrá una nota.

Mini ejercicio BD

Una empresa nos pide que creemos una BD para controlar su inventario. Tienen un almacen con una serie de productos de los que les interesa saber el nombre y la cantidad que tienen (p. ej. tuercas, 20).
Los productos se los compran a unos proveedores y quieren saber el nombre y el telefono de los mismos para que cuando necesiten renovar stock les puedan llamar.

Crear el modelo E/R para esta base de datos.

¿Crees que tienes toda la información o consideras que hay que preguntarle algo al cliente?

Insertar cliente

Hay que hacer varios pasos

 $server = "localhost";
$user = "root";
$password = "";
$db = "sakila";
try {
    $conn = new PDO("mysql:host=$server;dbname=$db;charset=UTF8", $user, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    //Los datos que tengo que insertar
    $pais = 'ArgentinaJarll';
    $ciudad = "La plata";
    $direccion = "Agla 6";
    $nombre = "Juan";
    $apellidos = "Pi";
    
    //Compruebo si existe el país consultando a la base de datos si hay
    //algún país que se llame como el que me han pasado
    $sql = "select * from country where country=:country";
    $parametros = [
        'country' => $pais
    ];
    $st = $conn->prepare($sql);
    $st->execute($parametros);
    $country = $st->fetch();
    
    //Si no existe lo inserto
    if (empty($country)) {
        $sql = "insert into country(country) values(:country)";
        $st = $conn->prepare($sql);
        $st->execute($parametros);
        $country_id = $conn->lastInsertId();
    } else {
        $country_id = $country['country_id'];
    }
      //Aquí ya tengo el país
    echo $country_id . "<br/>";

  
    //Hago la misma comprobación para la ciudad pero ojo, de ese país
    $sql = "select * from city where city=:city and country_id=:country_id";
    $parametros = [
        'city' => $ciudad,
        'country_id' => $country_id
    ];
    $st = $conn->prepare($sql);
    $st->execute($parametros);
    $city = $st->fetch();
    
    //Si no existe la ciudad la inserto
    if (empty($city)) {
        $sql = "insert into city(city,country_id) values(:city,:country_id)";
        $st = $conn->prepare($sql);
        $st->execute($parametros);
        $city_id = $conn->lastInsertId();
    } else {
        $city_id = $city['city_id'];
    }
    //Aquí ya tengo el id de la ciudad
    echo $city_id . "<br>";

    //A partir de aquí no compruebo más. Inserto la dirección con esa ciudad
    $sql = "insert into address (city_id,address) values (:city_id,:address)";
    $parametros = [
        'city_id' => $city_id,
        'address' => $direccion
    ];
    $st = $conn->prepare($sql);
    $st->execute($parametros);
    //Recupero el id de de esa dirección
    $address_id = $conn->lastInsertId();
    
    //Inserto el cliente con el id de la dirección y en store_id pongo un 1 directamente
    $sql = "insert into customer (address_id,first_name,last_name,store_id)"
            . " values (:address_id,:first_name,:last_name,1)";
    $parametros = [
        'address_id' => $address_id,
        'first_name' => $nombre,
        'last_name' => $apellidos
    ];
    $st = $conn->prepare($sql);
    $st->execute($parametros);
    echo "insertado el cliente con id ".$conn->lastInsertId();
} catch (Exception $e) {
    echo "Connection failed: " . $e->getMessage();
}

Ejemplos prepare

$sql="insert into actor (first_name,last_name) values (:first_name,:last_name)";
    $parametros=[
      'first_name'=>'Perico',
      'last_name'=>'De los palotes'
    ];
    $st=$conn->prepare($sql);
    $st->execute($parametros);

 $sql="SELECT city FROM country join city using(country_id) where country=:country";
   $parametros=[
       'country'=>'Argentina'
   ];
   $st=$conn->prepare($sql);
   $st->execute($parametros);
   $ciudades=$st->fetchAll();
   print_r($ciudades);

  $sql="SELECT country FROM country  where country like :country";
   $parametros=[
       'country'=>'%ar%'
   ];
   $st=$conn->prepare($sql);
   $st->execute($parametros);
   $paises=$st->fetchAll();
   print_r($paises);
 $sql = "SELECT * FROM customer join address using(address_id)
            where email=:email and phone=:phone";
   
  $parametros=[
        'email'=>'mary.smith@sakilacustomer.org',
        'phone'=>'28303384290'
    ];
    $st=$conn->prepare($sql);
    $st->execute($parametros);
    $cliente=$st->fetch();
    if (empty($cliente)){
        echo "No sé quién eres";
    } else{
        echo "hola ".$cliente['first_name'];
    }


    $sql="update film set replacement_cost=replacement_cost*:descuento where rating=:rating";
    $parametros=[
        'descuento'=>0.9,
        'rating'=>'R'
    ];
    $st=$conn->prepare($sql);
    $st->execute($parametros);

CRUD actores sakila

actores.php

<!DOCTYPE html>
<html>
    <head>
        <meta charset="UTF-8">
        <title></title>
    </head>
    <body>
        <?php
        $server = "localhost";
        $user = "root";
        $password = "";
        $db = "sakila";
        ?>
        <form method="post">
            <p>Nombre<input type="text" name="first_name"></p>
            <p>Apellidos<input type="text" name="last_name"></p>
            <input type="submit">
        </form>
        <table>
            <tr><td>Id</td><td>Nombre</td><td>Apellido</td><td>Acciones</td>
            </tr>
            <?php
            try {
                $conn = new PDO("mysql:host=$server;dbname=$db;charset=UTF8", $user, $password);
                $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

                //Insertar el actor SI me lo mandas
                //Recuperar los datos de POST
                $first_name = filter_input(INPUT_POST, "first_name", FILTER_SANITIZE_MAGIC_QUOTES);
                $last_name = filter_input(INPUT_POST, "last_name", FILTER_SANITIZE_MAGIC_QUOTES);
                $actor_id = filter_input(INPUT_POST, "actor_id", FILTER_SANITIZE_NUMBER_INT);

                //Comprobar que me han mandado datos
                if (!empty($first_name) && !empty($last_name)) {
                       //Si me han mandado crear el SQL para insertar o actualizar
                 if (empty($actor_id)) {
                        $sql = "insert into actor(first_name,last_name) values ('$first_name','$last_name')";
                    } else {
                $sql = "update actor set first_name='$first_name', last_name='$last_name'"
                        . " where actor_id=$actor_id";
                    }
                    //Ejecutarlo
                    if ($conn->exec($sql) > 0) {
                        echo "Insertado el actor $first_name $last_name";
                    }
                }

                $borrar = filter_input(INPUT_GET, "borrar", FILTER_SANITIZE_NUMBER_INT);
                if (!empty($borrar)) {
                    $sql = "delete from actor where actor_id=$borrar";
                    if ($conn->exec($sql) > 0) {
                        echo "Borrado el actor $borrar";
                    }
                }


                //Muestra los actores
                $sql = "select * from actor";
                $q = $conn->query($sql);
                while ($row = $q->fetch()) {
                    ?> 
                    <tr><td><?= $row['actor_id'] ?></td>
                        <td><?= $row['first_name'] ?></td>
                        <td><?= $row['last_name'] ?></td>
                        <td><a href="actor_editar.php?actor_id=<?= $row['actor_id'] ?>">Editar</a>
                            <a href="?borrar=<?= $row['actor_id'] ?>">Borrar</a></td>
                    </tr>
                    <?php
                }
            } catch (Exception $e) {
                echo "Connection failed: " . $e->getMessage();
            }
            ?>
        </table>
    </body>
</html>

actor_editar.php

<!DOCTYPE html>
<html>
    <head>
        <meta charset="UTF-8">
        <title></title>
    </head>
    <body>
        <?php
        $server = "localhost";
        $user = "root";
        $password = "";
        $db = "sakila";
        try {
            $conn = new PDO("mysql:host=$server;dbname=$db;charset=UTF8", $user, $password);
            $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            
            $actor_id= filter_input(INPUT_GET, "actor_id",FILTER_SANITIZE_NUMBER_INT);
            $sql="select * from actor where actor_id=$actor_id";
            $q=$conn->query($sql);
            $actor=$q->fetch();
            //print_r($actor);
            ?>
        <h2>Editar actor</h2>

        <form action="actores.php" method="post">
            <p>Id: <input readonly type="text" name="actor_id" value="<?=$actor['actor_id']?>"></p>
            <p>Nombre: <input type="text" name="first_name" value="<?=$actor['first_name']?>"></p>
            <p>Apellido: <input type="text" name="last_name" value="<?=$actor['last_name']?>"></p>
            <input type="submit">
        </form>
        <?php
            
            
        } catch (Exception $e) {
            echo "Connection failed: " . $e->getMessage();
        }
        ?>
    </body>
</html>

Ejemplo de procedimientos almacenados

Parámetros entrada y salida:

CREATE PROCEDURE `test`(in id int, out p_actor varchar(100))
BEGIN
 select concat_ws(' ',first_name,last_name) into p_actor from actor where actor_id=id;
 
END

call test(2, @actor);

CREATE DEFINER=`root`@`localhost` PROCEDURE `suma`(in a int, in b int, out suma int)
BEGIN
set suma=a+b;
END

call suma(8,9,@res);

Alta de registros con comprobación incluída:

CREATE DEFINER=`root`@`localhost` PROCEDURE `alta_actor`(in nombre varchar(100), in apellido varchar(100))
BEGIN
declare c int;
select count(*) into c from actor where first_name=nombre and last_name=apellido;
if c=0 and length(nombre)>1 and length(apellido)>1 then
 insert into actor (first_name, last_name) values (nombre, apellido);
end if;
END

call alta_actor ('juan','pa');
call alta_actor ('juan','pa');  --No funcionará por repetido
call alta_actor ('juan','p');   -- no funcionará por longitud

Ejemplo de cursor:

CREATE PROCEDURE `cursor_ejemplo`(out total int)
BEGIN

DECLARE final int DEFAULT 0;

declare nombre varchar(100);

DECLARE mi_cursor CURSOR FOR SELECT first_name FROM actor;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET final = 1;
set total=0;
OPEN mi_cursor;

while final=0 do

fetch mi_cursor into nombre;

if nombre like '%z%' then
 set total=total+1;
end if;

end while;

CLOSE mi_cursor;

END


call cursor_ejemplo(@t);