Mes: mayo 2019

Ajax simple

<!DOCTYPE html>
<html>
    <head>
        <meta charset="UTF-8">
        <title></title>
          <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>
     
    </head>
    <body>
        <button id="ajax">Pulsa aquí para probar el ajax</button>
        <div id="resultado"></div>
        <script>
        $(function(){
           console.log("Página cargada"); 
           $('#ajax').click(function(){
               console.log("Pulsado");
               $.get("ejemplo_ajax_backend.php",function(data){
                   console.log("resultado "+data);
                   $('#resultado').append(data+"<br/>");
               });
           });
        });
        </script>
    </body>
</html>

<?php
echo mt_rand(1,10);

CRUD AJAX

actores_ajax (html)

<!DOCTYPE html>

<html>
    <head>
        <meta charset="UTF-8">
        <title></title>
        <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>
        <script src="actores.js" type="text/javascript"></script>
    </head>

    <body>
        <p>Nombre<input type="text" id="first_name" name="first_name"></p>
        <p>Apellidos<input type="text"  id="last_name" name="last_name"></p>
        <input type="button" value="Añadir" id="nuevo">
        <div id="mensaje"></div>
        <div id="lista"></div>
    </body>
</html>

actores.js (ajax)

$(function () {
    cargar();
    function cargar() {
        $.get("actores_ajax_back.php?funcion=lista", function (data) {
            $("#lista").html(data);
            $(".borrar").click(function () {
                console.log($(this));
                console.log($(this).val());
                $.post("actores_ajax_back.php?funcion=borrar",
                        {actor_id: $(this).val()})
                        .done(function (data) {
                            console.log(data);
                            $('#mensaje').html("Borrado");
                            cargar();
                        });
            });
        });
    }
    $("#nuevo").click(function () {
        $.post("actores_ajax_back.php?funcion=nuevo",
                {first_name: $('#first_name').val(), last_name: $('#last_name').val()})
                .done(function (data) {
                    $('#mensaje').html("Insertado con id " + data);
                    cargar();
                });
    });

});

actores_ajax_back.php (backend)

<?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);
} catch (Exception $e) {
    echo "Connection failed: " . $e->getMessage();
}
$funcion = filter_input(INPUT_GET, "funcion");

if (function_exists($funcion)) {
    $funcion($conn);
}

function lista($conn) {
    try {
        $sql = "select * from actor";
        $q = $conn->query($sql);
        ?>
        <table>
            <tr><td>Id</td><td>Nombre</td><td>Apellido</td><td>Acciones</td>
            </tr>
            <?php
            while ($row = $q->fetch()) {
                ?> 
                <tr><td><?= $row['actor_id'] ?></td>
                    <td><?= $row['first_name'] ?></td>
                    <td><?= $row['last_name'] ?></td>
                    <td><button class="borrar" value="<?= $row['actor_id'] ?>">Borrar</button></td>
                </tr>
                <?php
            }
            ?></table><?php
    } catch (Exception $ex) {
        echo $ex->getMessage();
    }
}

function nuevo($conn) {
    try {
        $first_name = filter_input(INPUT_POST, "first_name", FILTER_SANITIZE_MAGIC_QUOTES);
        $last_name = filter_input(INPUT_POST, "last_name", FILTER_SANITIZE_MAGIC_QUOTES);
        if (!empty($first_name) && !empty($last_name)) {
            $sql = "insert into actor(first_name,last_name) values ('$first_name','$last_name')";
            //Ejecutarlo
            if ($conn->exec($sql) > 0) {
                echo $conn->lastInsertId();
            }
        }
    } catch (Exception $ex) {
        echo $ex->getMessage();
    }
}
function borrar($conn) {
    try {
        $actor_id = filter_input(INPUT_POST, "actor_id");
         if (!empty($actor_id) ) {
            $sql = "delete from actor where actor_id=$actor_id";
            //Ejecutarlo
            echo $conn->exec($sql);
        }
    } catch (Exception $ex) {
        echo $ex->getMessage();
    }
}

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>

Acceso a datos con PDO

Conectar al servidor:

 $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);
 } catch (Exception $e) {
      echo "Connection failed: " . $e->getMessage();
 }

Ejecutar una consulta de acción (también funcionaría con query):

$sql = "update category set name='$nombre' where category_id=$category_id";
            if ($conn->exec($sql) === true) {...}

Recorrer los registros:

$sql = "select * from category ";
$resul = $conn->query($sql);
if ($resul->rowCount()>0) {
  while ($row = $resul->fetch()) {
...
}
}

Obtener todos los registros de una sola vez:

$sql = "select * from category ";
$resul = $conn->query($sql);
$rows=$resul->fetchAll()

Insertar y obtener el último id:

 $sql = "insert into category (name) values ('" . addslashes($nombre) . "')";
            if ($conn->query($sql) == true) {
                $last_id = $conn->lastInsertId();
                echo "Insertada categoría $nombre con id " . $last_id;
            } else {
                echo "Error " . $conn->error;
            }

Enlazar resultados con variables:

$row = $conn->query('SELECT first_name,last_name from actor');
$row->bindColumn('first_name', $nombre);

$row->bindColumn(2, $apellido);
while ($row->fetch()) {
    print "$nombre $apellido. 
\n";
}

Preparar sentencias y luego pasar valores:

$st = $conn->prepare('INSERT INTO actor (first_name,last_name) VALUES (?,?)');
$st->execute(array('Vito','Corleone'));
$st->execute(array('Juan','Perez'));

$st = $conn->prepare('select * from actor where actor_id>?');
$st->execute(array(50));
print_r($st->fetchAll());

Pasar valores con nombre:

$st = $conn->prepare('select * from actor where first_name like :first_name');
$st->execute(array(':first_name'=>'%z%'));

Enlazar parámetros con variables:

$actores=['juan'=>'perez','ana'=>'pi','rosa'=>'buj'];
$st = $conn->prepare('insert into actor (first_name,last_name) values(:first_name,:last_name)');
$st->bindParam(':first_name', $first_name);
$st->bindparam(':last_name', $last_name);
foreach($actores as $first_name=>$last_name){
$st->execute();
}

Número de filas (Ojo, no funciona en todas las bases de datos):

$resul->rowCount()

Ejecutar con arrays:

 $sentencia = $conn->prepare("INSERT INTO actor( first_name,last_name) VALUES (?,?)");
 $sentencia->execute(array('asd','dsa'));

 $sentencia = $conn->prepare("INSERT INTO actor( first_name,last_name) VALUES (:f,:l)");
 $sentencia->execute(array('f'=>'asd','l'=>'dsa'));

Enlace manual php:

http://php.net/manual/es/pdo.prepared-statements.php

Tutorial de PDO

Un ejemplo:

<body>
        <form action="#">
            País:
            <input type="text" name="pais">
            <input type="submit">
        </form>
        <?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);
          //Para insertar, recupero el pais de GET y si hay país lo inserto
            $pais = filter_input(INPUT_GET, "pais", FILTER_SANITIZE_MAGIC_QUOTES);
            if (!empty($pais)) {
                $sql = "insert into country (country) values('$pais')";
                if ($conn->exec($sql) > 0) {
                    echo "Registro insertado<br/>";
                }
            }
            $sql = "select * from country ORDER BY COUNTRY_ID DESC";
            $q = $conn->query($sql);
            while ($row = $q->fetch()) {

                echo $row['country_id'] . "-" . $row['country'] . "<br>";
            }
        } catch (Exception $e) {
            echo "Connection failed: " . $e->getMessage();
        }
        ?>
    </body>

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);

 

Procedimientos almacenados

Ejemplos de procedimientos almacenados

CREATE DEFINER=`root`@`localhost`
 PROCEDURE film_in_stock
 (IN p_film_id INT, IN p_store_id INT,
 OUT p_film_count INT)
 READS SQL DATA
BEGIN
 SELECT inventory_id
 FROM inventory
 WHERE film_id = p_film_id
 AND store_id = p_store_id
 AND inventory_in_stock(inventory_id);

 SELECT FOUND_ROWS() INTO p_film_count;
END

call film_in_stock(123,1,@w);
select @w

CREATE DEFINER=`root`@`localhost` PROCEDURE 
`actores_por_categoria`
(in p_categoria varchar(50))
BEGIN
select distinct concat(first_name,' ',last_name) actor 
from actor join film_actor using (actor_id)
join film using (film_id)
join film_category using (film_id)
join category c using (category_id)
where c.name=p_categoria
order by actor;

END

call actores_por_categoria('Animation')

Ejercicios vistas

Crear una vista que nos muestre el pais, la ciudad, la dirección y el nombre de los clientes. La podemos llamar clientes_direccion

Con esa vista creada será muy fácil mostrar los clientes de Argentina o Italia

Crear una vista que nos relacione la película con sus pagos. Que nos muestre el id de la película, el title, y todos los datos de payment.

Con esa vista sería muy fácil ver el total de pagos por película.