Clase sakila

<?php

class Sakila {

private $server = "localhost";
 private $user = "root";
 private $password = "";
 private $db = "sakila";
 private $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();
 }
 }

/**
 * 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'";
 $c = $this->conn->query($sql);
 if ($actor = $c->fetch()) {
 return $actor['actor_id'];
 }
 $sql = "insert into actor (first_name,last_name) values ('$first_name','$last_name')";
 $this->conn->exec($sql);
 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) {

$sql = "insert into film(title,language_id) values ('$film',1)";
 $this->conn->exec($sql);
 $film_id = $this->conn->lastInsertId();
 $sql = "insert into film_category(film_id,category_id) values($film_id,$category_id)";
 $this->conn->exec($sql);
 }

/**
 * 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
 }

}

Añadir película y categoría

<?php
 $server = "localhost";
 $user = "root";
 $password = "";
 $db = "sakila";

try {
 $conn = new PDO("mysql:host=$server;dbname=$db", $user, $password);
 $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$conn->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
 $conn->exec("SET CHARACTER SET utf8");

$category = filter_input(INPUT_GET, "category", FILTER_VALIDATE_INT);
 $film = filter_input(INPUT_GET, "film");
 if (!empty($category) && !empty($film)) {
 $sql="insert into film(title,language_id) values ('$film',1)";
 $conn->exec($sql);
 $film_id=$conn->lastInsertId();
 $sql="insert into film_category(film_id,category_id) values($film_id,$category)";
 $conn->exec($sql);
 }

$sql = "select * from category";
 $q = $conn->query($sql);
 $categorias = $q->fetchAll();
 } catch (PDOException $e) {
 echo "Connection failed: " . $e->getMessage();
 }
 ?>
 <form>
 Categoría: 
 <select name="category">
 <?php foreach ($categorias as $categoria) {
 ?>
 <option value="<?= $categoria['category_id'] ?>"><?= $categoria['name'] ?></option>
 <?php } ?>
 </select>
 <br/>
 Película: <input type="text" name="film"><br/>
 <input type="submit">

</form>

Acceso a datos con PDO

Conectar al servidor:

$server="localhost";
$user="root";
$password="";
$db="sakila";

try {
    $conn = new PDO("mysql:host=$server;dbname=$db", $user, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    }
catch(PDOException $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