Bases de Datos
## Objetivos de Aprendizaje
- Conectar a bases de datos usando PDO
- Ejecutar consultas de forma segura con declaraciones preparadas
- Obtener datos correctamente
- Manejar transacciones
- Comprender mejores prácticas de bases de datos
## Descripción General de PDO
PDO (PHP Data Objects) proporciona una interfaz consistente para acceso a bases de datos.
### Bases de Datos Soportadas
- MySQL
- PostgreSQL
- SQLite
- Oracle
- Microsoft SQL Server
- y muchas más
## Conectando a MySQL
### DSN (Data Source Name)
```php
```
### Creando Conexión
```php
setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo "Connected successfully";
} catch (PDOException $e) {
echo "Connection failed: " . $e->getMessage();
}
?>
```
### Opciones de Conexión
```php
PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false,
];
try {
$pdo = new PDO($dsn, $user, $password, $options);
} catch (PDOException $e) {
echo "Connection failed: " . $e->getMessage();
}
?>
```
### Modos de Obtención Comunes
```php
value)
$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
// Fetch as object
$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_OBJ);
// Fetch into class
$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_CLASS);
// Fetch both (array + object)
$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_BOTH);
?>
```
## Declaraciones Preparadas
### ¿Por qué Declaraciones Preparadas?
- Previene inyección SQL
- Mejora rendimiento para consultas repetidas
- Separa lógica SQL de datos
### Consulta Sin Declaración Preparada (Inseguro)
```php
query($sql);
// Someone could input: ' OR '1'='1
// Result: SELECT * FROM users WHERE name = '' OR '1'='1'
?>
```
### Declaración Preparada con Placeholders
```php
prepare("SELECT * FROM users WHERE id = :id");
$stmt->execute(['id' => 1]);
// Question mark placeholders
$stmt = $pdo->prepare("SELECT * FROM users WHERE name = ?");
$stmt->execute(["Alice"]);
?>
```
### execute() con Parámetros
```php
prepare("
INSERT INTO users (name, email, age)
VALUES (:name, :email, :age)
");
$stmt->execute([
'name' => 'Alice',
'email' => 'alice@example.com',
'age' => 25
]);
// Or with bindParam()
$stmt = $pdo->prepare("SELECT * FROM users WHERE age > :min_age");
$stmt->bindParam(':min_age', $minAge, PDO::PARAM_INT);
$minAge = 18;
$stmt->execute();
?>
```
## Consultas SELECT
### fetch() - Una Fila
```php
prepare("SELECT * FROM users WHERE id = :id");
$stmt->execute(['id' => 1]);
$user = $stmt->fetch();
print_r($user);
// [id] => 1, [name] => Alice, [email] => alice@example.com
?>
```
### fetchAll() - Todas las Filas
```php
prepare("SELECT * FROM users WHERE active = 1");
$stmt->execute();
$users = $stmt->fetchAll();
foreach ($users as $user) {
echo $user['name'] . "\n";
}
// With class
$stmt = $pdo->prepare("SELECT * FROM users");
$stmt->execute();
$users = $stmt->fetchAll(PDO::FETCH_CLASS, 'User');
?>
```
### fetchColumn() - Un Solo Valor
```php
prepare("SELECT COUNT(*) FROM users WHERE active = 1");
$stmt->execute();
$count = $stmt->fetchColumn();
echo "Active users: " . $count;
?>
```
### Obtener con Clase
```php
prepare("SELECT * FROM users WHERE id = :id");
$stmt->setFetchMode(PDO::FETCH_CLASS, 'User');
$stmt->execute(['id' => 1]);
$user = $stmt->fetch();
echo $user->name; // Alice
?>
```
## Consultas INSERT
### Insert Básico
```php
prepare("
INSERT INTO users (name, email, age)
VALUES (:name, :email, :age)
");
$stmt->execute([
'name' => 'Alice',
'email' => 'alice@example.com',
'age' => 25
]);
$newId = $pdo->lastInsertId();
echo "Created user with ID: " . $newId;
?>
```
### Múltiples Inserts
```php
prepare("
INSERT INTO users (name, email)
VALUES (:name, :email)
");
$users = [
['name' => 'Alice', 'email' => 'alice@example.com'],
['name' => 'Bob', 'email' => 'bob@example.com'],
['name' => 'Charlie', 'email' => 'charlie@example.com']
];
$pdo->beginTransaction();
try {
foreach ($users as $user) {
$stmt->execute($user);
}
$pdo->commit();
echo "All users inserted";
} catch (Exception $e) {
$pdo->rollBack();
echo "Error: " . $e->getMessage();
}
?>
```
## Consultas UPDATE
### Update Básico
```php
prepare("
UPDATE users
SET email = :email, age = :age
WHERE id = :id
");
$stmt->execute([
'email' => 'newemail@example.com',
'age' => 26,
'id' => 1
]);
$affected = $stmt->rowCount();
echo "Updated $affected rows";
?>
```
## Consultas DELETE
### Delete Básico
```php
prepare("DELETE FROM users WHERE id = :id");
$stmt->execute(['id' => 1]);
$affected = $stmt->rowCount();
echo "Deleted $affected rows";
?>
```
### Delete Seguro con Verificación
```php
prepare("DELETE FROM users WHERE id = :id AND active = 0");
$stmt->execute(['id' => 1]);
if ($stmt->rowCount() === 0) {
echo "No inactive user found with that ID";
}
?>
```
## Transacciones
### Transacción Básica
```php
beginTransaction();
try {
// Deduct from one account
$stmt = $pdo->prepare("UPDATE accounts SET balance = balance - ? WHERE id = ?");
$stmt->execute([100, 1]);
// Add to another account
$stmt->execute([100, 2]);
$pdo->commit();
echo "Transfer complete";
} catch (Exception $e) {
$pdo->rollBack();
echo "Transfer failed: " . $e->getMessage();
}
?>
```
### Modo Auto-Commit
```php
setAttribute(PDO::ATTR_AUTOCOMMIT, false);
// ... perform multiple queries ...
// Re-enable auto-commit (commits if active)
$pdo->setAttribute(PDO::ATTR_AUTOCOMMIT, true);
?>
```
## Manejo de Errores
### Bloque try-catch
```php
prepare("SELECT * FROM nonexistent_table");
$stmt->execute();
} catch (PDOException $e) {
echo "Error: " . $e->getMessage();
// For debugging (not in production!)
echo "SQL State: " . $e->errorInfo[0];
echo "Error Code: " . $e->getCode();
}
?>
```
### Array Error Info
```php
prepare("INVALID SQL");
try {
$stmt->execute();
} catch (PDOException $e) {
$errorInfo = $stmt->errorInfo();
print_r($errorInfo);
// [0] => 42000 (SQLSTATE)
// [1] => 1064 (MySQL error code)
// [2] => Syntax error...
}
?>
```
## Trabajando con SQLite
### Conectar a SQLite
```php
setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// Create table
$pdo->exec("
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
email TEXT UNIQUE
)
");
?>
```
### Operaciones SQLite
```php
prepare("INSERT INTO users (name, email) VALUES (:name, :email)");
$stmt->execute(['name' => 'Alice', 'email' => 'alice@example.com']);
// Select
$stmt = $pdo->query("SELECT * FROM users");
$users = $stmt->fetchAll(PDO::FETCH_ASSOC);
?>
```
## Trabajando con PostgreSQL
### Conectar a PostgreSQL
```php
getMessage();
}
?>
```
## Patrones Comunes
### Registro de Usuario
```php
prepare("SELECT id FROM users WHERE email = :email");
$stmt->execute(['email' => $email]);
if ($stmt->fetch()) {
return false; // Email already exists
}
// Hash password
$hashedPassword = password_hash($password, PASSWORD_DEFAULT);
// Insert new user
$stmt = $pdo->prepare("
INSERT INTO users (name, email, password)
VALUES (:name, :email, :password)
");
$stmt->execute([
'name' => $name,
'email' => $email,
'password' => $hashedPassword
]);
return true;
}
?>
```
### Inicio de Sesión de Usuario
```php
prepare("SELECT * FROM users WHERE email = :email");
$stmt->execute(['email' => $email]);
$user = $stmt->fetch();
if (!$user) {
return null; // User not found
}
if (!password_verify($password, $user['password'])) {
return null; // Wrong password
}
return $user;
}
?>
```
### Paginación
```php
prepare("
SELECT * FROM users
ORDER BY created_at DESC
LIMIT :limit OFFSET :offset
");
$stmt->bindValue(':limit', $perPage, PDO::PARAM_INT);
$stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
$stmt->execute();
$users = $stmt->fetchAll();
// Get total for pagination
$totalStmt = $pdo->query("SELECT COUNT(*) FROM users");
$total = $totalStmt->fetchColumn();
$totalPages = ceil($total / $perPage);
?>
```
### Búsqueda con LIKE
```php
prepare("
SELECT * FROM products
WHERE name LIKE :search
OR description LIKE :search
ORDER BY name
");
$stmt->execute(['search' => $search]);
$results = $stmt->fetchAll();
?>
```
## Resumen
- PDO proporciona interfaz unificada de acceso a bases de datos
- Usa DSN para conectar: `$pdo = new PDO($dsn, $user, $pass)`
- Siempre usa declaraciones preparadas para prevenir inyección SQL
- Placeholders nombrados `:name` preferidos sobre `?`
- `fetch()` devuelve una fila; `fetchAll()` devuelve todas las filas
- `execute()` devuelve booleano; usa `rowCount()` para filas afectadas
- Transacciones con `beginTransaction()`, `commit()`, `rollBack()`
- `lastInsertId()` obtiene ID auto-increment después de insert
- `password_hash()` y `password_verify()` para contraseñas seguras
- Establece `PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION` para manejo de errores
Comments
Comments powered by Giscus
To enable comments, add your Giscus embed code here.
Learn more about Giscus →