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