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>

Publicado por

Avatar del usuario

Juan Pablo Fuentes

Formador de programación y bases de datos