sakila.php
<?php
class Sakila {
private $server = "localhost";
private $user = "root";
private $password = "";
private $db = "sakila";
public $conn;
function __construct() {
try {
$this->conn = new PDO("mysql:host=$this->server;dbname=$this->db", $this->user, $this->password);
$this->conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$this->conn->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
$this->conn->exec("SET CHARACTER SET utf8");
} catch (PDOException $e) {
echo "Connection failed: " . $e->getMessage();
}
}
function getActors() {
$sql = "select * from actor";
$query = $this->conn->query($sql);
return $query->fetchAll();
}
function getActor($id) {
$sql = "select * from actor where actor_id=$id";
$query = $this->conn->query($sql);
return $query->fetch();
}
/**
*
* @param type $actor array asociativo con los campos del actor
* ejemplo: ['actor_id'=>1,'first_name'=>'Santiago','last_name'=>'Segura']
*/
function updateActor($actor) {
$sql = "update actor set first_name='" . $actor['first_name'] . "', last_name='" . $actor['last_name'] . "'
where actor_id=" . $actor['actor_id'] . ";";
$this->conn->exec($sql);
}
function deleteActor($id) {
$sql = "delete from actor where actor_id=$id";
$this->conn->exec($sql);
}
/**
* Inserta un nuevo actor o devuelve el id si ya existe
* @param string valor de first_name
* @param string valor de last_name
* @return int id
*/
function newActor($first_name, $last_name) {
if (!empty($first_name) && !empty($last_name)) {
$sql = "select * from actor where first_name=:first_name and last_name=:last_name";
$st = $this->conn->prepare($sql);
$st->execute([':first_name' => $first_name, ':last_name' => $last_name]);
if ($actor = $st->fetch()) {
return $actor['actor_id'];
}
$sql = "insert into actor (first_name,last_name) values (:first_name,:last_name)";
$st = $this->conn->prepare($sql);
$st->execute([':first_name' => $first_name, ':last_name' => $last_name]);
return $this->conn->lastInsertId();
} else {
return null;
}
}
/**
* Inserta una película en una categoría
* @param int id de la categoría
* @param string título de la película
*/
function newFilm($category_id, $film) {
$this->conn->beginTransaction();
try {
$sql = "insert into film(title,language_id) values (:film,1)";
$st = $this->conn->prepare($sql);
$st->execute([':film' => $film]);
$film_id = $this->conn->lastInsertId();
$sql = "insert into film_category(film_id,category_id) values($film_id,$category_id)";
$this->conn->exec($sql);
$this->conn->commit();
} catch (PDOException $e) {
echo $e->getMessage();
$this->conn->rollBack();
}
}
/**
* Crea un select con todas las categorías
*/
function selectCategory() {
$sql = "select * from category";
$q = $this->conn->query($sql);
$categorias = $q->fetchAll();
?>
<select name="category">
<?php foreach ($categorias as $categoria) {
?>
<option value="<?= $categoria['category_id'] ?>"><?= $categoria['name'] ?></option>
<?php } ?>
</select>
<?php
}
}
index.php
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title></title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0-beta.3/css/bootstrap.min.css" integrity="sha384-Zug+QiDoJOrZ5t4lssLdxGhVrurbmBWopoEl+M6BdEfwnCJZtKxi1KgxUyJq13dy" crossorigin="anonymous">
</head>
<body>
<div class="container">
<?php
include "sakila.php";
$sakila = new Sakila();
$nuevo = filter_input(INPUT_GET, 'nuevo');
$first_name = filter_input(INPUT_GET, 'first_name');
$last_name = filter_input(INPUT_GET, 'last_name');
if (!empty($nuevo) && !empty($first_name) && !empty($last_name)) {
$sakila->newActor($first_name, $last_name);
}
$cambiar = filter_input(INPUT_GET, 'cambiar');
$actor_id = filter_input(INPUT_GET, 'actor_id');
if (!empty($cambiar) && !empty($actor_id) && !empty($first_name) && !empty($last_name)) {
$sakila->updateActor(['actor_id' => $actor_id, 'first_name' => $first_name, 'last_name' => $last_name]);
}
$delete = filter_input(INPUT_POST, 'delete');
$actor_id = filter_input(INPUT_POST, 'actor_id');
if (!empty($delete) && !empty($actor_id)) {
$sakila->deleteActor($actor_id);
}
$actores = $sakila->getActors();
?>
<h1>Mantenimiento actores</h1>
<form>
<div class="form-group">
<label for="first_name">Nombre:</label>
<input type="text" class="form-control" name="first_name">
</div>
<div class="form-group">
<label for="last_name">Apellidos:</label>
<input type="text" class="form-control" name="last_name">
</div>
<input class="btn btn-success" type="submit" name="nuevo" value="Nuevo">
</form>
<table class="table">
<tr><td>Nombre</td><td>Apellidos</td><td>Acciones</td></tr>
<?php
foreach ($actores as $actor) {
?>
<tr><td><?= $actor['first_name'] ?></td><td><?= $actor['last_name'] ?></td>
<td><a href="editar.php?actor_id=<?= $actor['actor_id'] ?>" class="btn btn-success">Editar</a>
<form action="index.php" method="post" style="float:left;margin-right: 10px">
<input type="hidden" name="actor_id" value="<?= $actor['actor_id'] ?>">
<input class="btn btn-success" type="submit" name="delete" value="Borrar">
</form>
</td></tr>
<?php
}
?>
</table>
</div>
</body>
</html>
editar.php
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title></title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0-beta.3/css/bootstrap.min.css" integrity="sha384-Zug+QiDoJOrZ5t4lssLdxGhVrurbmBWopoEl+M6BdEfwnCJZtKxi1KgxUyJq13dy" crossorigin="anonymous">
</head>
<body>
<div class="container">
<?php
include "sakila.php";
$sakila = new Sakila();
$actor_id = filter_input(INPUT_GET, 'actor_id');
if (!empty($actor_id)) {
$actor = $sakila->getActor($actor_id);
}
?>
<h1>Editar actor</h1>
<form action="index.php">
<input type="hidden" class="form-control" name="actor_id" value="<?= $actor['actor_id'] ?>">
<div class="form-group">
<label for="first_name">Nombre:</label>
<input type="text" class="form-control" name="first_name" value="<?= $actor['first_name'] ?>">
</div>
<div class="form-group">
<label for="last_name">Apellidos:</label>
<input type="text" class="form-control" name="last_name" value="<?= $actor['last_name'] ?>">
</div>
<input class="btn btn-success" type="submit" name="cambiar" value="Cambiar">
</form>
</div>
</body>
</html>
