SQLite3 y PDO con PHP: crud y ejemplos

Introducción

Hace algunas semanas o meses escribí una pequeña introducción a Python con SQLite. Ahora es el turno de PHP, un poderoso lenguaje muy popular soportado por casi todos los servidores del mundo. Veamos un ejemplo de PHP, SQLite3 y PDO.

SQLite3 viene como anillo al dedo si nuestra app no será consumida al mismo tiempo (en el mismo instante de tiempo), ya que, recordemos, no es una base de datos multihilo.

Nota: recuerda habilitar la extensión sqlite

Por cierto, ¿quieres un tutorial para MySQL? también puedes ver la conexión de PHP con otras bases de datos.

Proyecto terminado

Si quieres puedes descargar el proyecto completo en el siguiente enlace. Recuerda que recomiendo leer el post completo para que entiendas el funcionamiento del código: https://github.com/parzibyte/crud_sqlite_pdo_php

Recomendaciones

Tener la extensión habilitada
Contar con un servidor web local
Saber un poco de consultas SQL

Ventajas y desventajas

Las desventajas, como ya lo dije, es que no es una base de datos multihilo y no cuenta con muchas funciones para facilitarnos las cosas.

Como ventajas tenemos que todos nuestros datos residen en un sólo fichero. ¿Quieres respaldar? cópialo. ¿Quieres borrar todo? elimínalo. Así de simple es.

Ejemplos con PHP, SQLite3 y PDO

Abriendo base de datos

Como todos los datos residen en mismo fichero, para instanciar la base de datos basta con crear un objeto de la clase PDO y pasarle la cadena DSN que tiene el formato:

sqlite:ruta/absoluta/a/la/base/de/datos.db

<?php
$baseDeDatos = new PDO("sqlite:" . __DIR__ . "/videojuegos.db");
?>

En este caso usamos __DIR__ para obtener el directorio actual, luego lo concatenamos y al final nos puede dar un resultado como sqlite:C:\xampp\htdocs\sqlite\videojuegos.db

Con eso se habrá creado un archivo llamado videojuegos.db. Recuerda que puedes poner el nombre que quieras al archivo, así como la extensión. Incluso puedes dejar al archivo sin extensión y no habrá problemas.

Personalmente recomiendo que la extensión sea db.

Archivo de conexión

Como no queremos ser redundantes, vamos a poner la conexión a la base de datos en un archivo separado. Y cuando queramos usar la base de datos simplemente lo incluimos.

Por lo tanto, su contenido queda así:

<?php
$baseDeDatos = new PDO("sqlite:" . __DIR__ . "/videojuegos.db");
$baseDeDatos->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
?>

Hay que fijarnos que le ponemos un atributo llamado ATTR_ERRMODE. Eso es para que, en caso de un error de cualquier tipo, se nos avise. Si no ponemos esto y ocurre un error no se lanzará ninguna excepción y será un poco más difícil depurar.

Creando tablas

Para todos los ejemplos de este post usaremos una tabla de Videojuegos , en donde sólo guardaremos el año de lanzamiento, el título y el género. La base de datos se llamará videojuegos.db

Por lo tanto, la definición de la tabla y su creación queda así:

<?php
include_once __DIR__ . "/base_de_datos.php";
$definicionTabla = "CREATE TABLE IF NOT EXISTS videojuegos(
 id INTEGER PRIMARY KEY AUTOINCREMENT,
 anio INTEGER NOT NULL,
 titulo TEXT NOT NULL,
 genero TEXT NOT NULL
);";
#Podemos usar $baseDeDatos porque incluimos el archivo que la crea
$resultado = $baseDeDatos->exec($definicionTabla);
echo "Tablas creadas correctamente";
?>

Nota: por favor, no usar exec cuando los datos provengan de una fuente insegura. En este caso la definición de la tabla no contiene código malicioso para hacer una inyección SQL, ya que nosotros mismos la estamos haciendo.

Cuando usemos consultas en donde los datos provengan del usuario, es mejor utilizar sentencias preparadas, que veremos más adelante.

A este script lo tenemos que llamar una vez (por ejemplo, accediendo a localhost/crear_tablas.php).

Llamada a script para crear tablas de la base de datos de SQLite3 con PHP y PDO

Finalmente es importante notar el campo llamado id que se irá incrementando automáticamente. Esto servirá para editar y eliminar datos más adelante.

Insertando datos

Veamos cómo insertar datos en SQLite3.

Primero tenemos que crear un formulario para que se puedan ingresar datos desde ahí. Hay que separar el formulario y la lógica de la aplicación.

En el archivo PHP (que veremos más adelante) procesamos lo que se manda en el formulario y lo guardamos en la base de datos.

El formulario sirve simplemente como una interfaz o medio para que el usuario ingrese datos.

Formulario

Entonces creamos un formulario con HTML así:

<!DOCTYPE html>
<html lang="es">
<head>
 <meta charset="UTF-8">
 <title>Registrar un nuevo videojuego</title>
</head>
<body>
 <form action="insertar.php" method="post">
  <label for="titulo">Título</label>
  <input type="text" id="titulo" name="titulo" required placeholder="El título del videojuego">
  <br>
  <br>
  <label for="anio">Año</label>
  <input type="number" id="anio" name="anio" required placeholder="Escribe el año del videojuego">
  <br>
  <br>
  <label for="genero">Género</label>
  <input type="text" id="genero" name="genero" required placeholder="Escribe el género del videojuego">
  <br><br>
  <button type="submit">Guardar</button>
 </form>
</body>
</html>

Lo primero que tenemos que ver es que estamos aplicando una validación simple a los campos del formulario usando la propiedad required. También especificamos su tipo, si es número ponemos number y si es texto text.

El atributo action del formulario lleva al archivo insertar.php que servirá como conexión entre los datos que ingrese el usuario y nuestra base de datos.

Finalmente les ponemos un id y un name. El id es para que el label se refiera a ellos, y el name es el nombre de la clave que nos permitirá acceder a la variable $_POST que recibiremos en PHP así que hay que tener cuidado con esto.

Interfaz del formulario para insertar un videojuego

Procesar formulario

Para ejemplos prácticos no vamos a validar nada en PHP, pero no por ello lo debemos dejar así (puedes agregar la validación más tarde).

Este es sólo un ejercicio, pero si vas a publicarlo en el mundo real debes primero validar todo. Lo único que hacemos aquí es prevenir las inyecciones SQL.

<?php

if (empty($_POST["titulo"])) {
 exit("Faltan uno o más datos"); #Terminar el script definitivamente
}

if (empty($_POST["anio"])) {
 exit("Faltan uno o más datos"); #Terminar el script definitivamente
}

if (empty($_POST["genero"])) {
 exit("Faltan uno o más datos"); #Terminar el script definitivamente
}

#Si llegamos hasta aquí es porque los datos al menos están definidos
include_once __DIR__ . "/base_de_datos.php"; #Al incluir este script, podemos usar $baseDeDatos

# creamos una variable que tendrá la sentencia
$sentencia = $baseDeDatos->prepare("INSERT INTO videojuegos(anio, titulo, genero)
 VALUES(:anio, :titulo, :genero);");


# Debemos pasar a bindParam las variables, no podemos pasar el dato directamente
# debido a que la llamada es por referencia


$sentencia->bindParam(":anio", $_POST["anio"]);
$sentencia->bindParam(":titulo", $_POST["titulo"]);
$sentencia->bindParam(":genero", $_POST["genero"]);
$resultado = $sentencia->execute();
if($resultado === true){
 echo "Videojuego registrado correctamente";
}else{
 echo "Lo siento, ocurrió un error";
}
?>

Primero comprobamos que existan datos, es decir, que los datos del formulario se hayan llenado correctamente (aquí la explicación de la función empty)

Luego creamos una sentencia preparada pero en lugar de poner los verdaderos datos, ponemos un placeholder. Por ejemplo “:anio”

Las sentencias preparadas son aquellas que nos permiten preparar una consulta y luego pasarle datos para su ejecución. No es una simple concatenación, es una inserción segura.

Más tarde le pasamos parámetros a dicha sentencia y una vez que terminamos de pasarlos, la ejecutamos.

Al ejecutarla se devuelve un booleano indicando el éxito o error. Así que comprobamos si es igual a true y en caso de que sí significa que todo va bien.

Mostrar datos

Ya comprobamos que nuestro videojuego se inserta correctamente pero no tenemos forma de visualizarlo desde nuestra aplicación. Vamos a crear una interfaz en donde mostramos los datos en una tabla, dichos datos vienen de la base de datos SQLite3.

Tabla estática

Pero primero veamos cómo crear una tabla estática para entender más esto. Es decir, todavía no vamos a meternos con PHP, vamos a ver cómo se crea normalmente con HTML.

La tabla va entre las etiquetas <table>, lleva un <thead> y un <tbody>

<!DOCTYPE html>
<html lang="es">
<head>
 <meta charset="UTF-8">
 <title>Tabla estática</title>
 <style>
  table, th, td {
      border: 1px solid black;
  }
 </style>
</head>
<body>
 <table>
  <thead>
   <tr>
    <th>Título</th>
    <th>Año</th>
    <th>Género</th>
    <th>Editar</th>
    <th>Eliminar</th>
   </tr>
  </thead>
  <tbody>
   <!-- Notar que siempre se repite lo que hay entre <tr> y </tr> -->

   <tr>
    <td>Pac Man</td>
    <td>2000</td>
    <td>Arcade</td>
    <td>
     <a href="#">Editar</a>
    </td>
    <td>
     <a href="#">Eliminar</a>
    </td>
   </tr>

   <tr>
    <td>Crash Team Racing</td>
    <td>2000</td>
    <td>Carreras</td>
    <td>
     <a href="#">Editar</a>
    </td>
    <td>
     <a href="#">Eliminar</a>
    </td>
   </tr>


  </tbody>
 </table>
</body>
</html>

Como vemos, los dato que se repiten van entre <tr> y cada celda lleva un <td>. Ya pusimos los enlaces para editar y eliminar aunque por el momento no sirven. La tabla se ve así:

Tabla estática con HTML

Los estilos que van entre <style> son para poner bordes a la tabla. Ahorita no vamos a ver nada de CSS así que puedes omitirlo si no lo entiendes.

Más sobre tablas aquí.

Tabla dinámica

Ahora vamos a hacer que la tabla de arriba se llene con datos de nuestra base de datos de SQLite3. Primero veamos cómo recuperar datos de nuestra tabla en forma de arreglo:

<?php
include_once __DIR__ . "/base_de_datos.php"; #Al incluir este script, podemos usar $baseDeDatos

$resultado = $baseDeDatos->query("SELECT * FROM videojuegos;");
$videojuegos = $resultado->fetchAll(PDO::FETCH_OBJ);

Como vemos, instanciamos nuestra base de datos. Luego llamamos al método query (lo que devuelve un result) para que más tarde llamemos a fetchAll con el argumento PDO::FETCH_OBJ.

Este último argumento sirve para traer los datos como un objeto para poder acceder a $dato->propiedad en lugar de $dato[0] o $dato["propiedad"].

De esta forma hacemos nuestro código más expresivo.

Una vez que ya tenemos nuestro arreglo llamado videojuegos vamos a recorrerlo y a dibujar la tabla.

<?php
include_once __DIR__ . "/base_de_datos.php"; #Al incluir este script, podemos usar $baseDeDatos

$resultado = $baseDeDatos->query("SELECT * FROM videojuegos;");
$videojuegos = $resultado->fetchAll(PDO::FETCH_OBJ);
?>
<!DOCTYPE html>
<html lang="es">
<head>
 <meta charset="UTF-8">
 <title>Tabla estática</title>
 <style>
  table, th, td {
      border: 1px solid black;
  }
 </style>
</head>
<body>
 <table>
  <thead>
   <tr>
    <th>Título</th>
    <th>Año</th>
    <th>Género</th>
    <th>Editar</th>
    <th>Eliminar</th>
   </tr>
  </thead>
  <tbody>
   <!-- Notar que siempre se repite lo que hay entre <tr> y </tr> -->
   <!-- Así que lo hacemos en un ciclo foreach -->

   <?php foreach($videojuegos as $videojuego){ /*Notar la llave que dejamos abierta*/ ?>
    <tr>
     <td><?php echo $videojuego->titulo ?></td>
     <td><?php echo $videojuego->anio ?></td>
     <td><?php echo $videojuego->genero ?></td>
     <td>
      <a href="editar.php?id=<?php echo $videojuego->id ?>">Editar</a>
     </td>
     <td>
      <a href="eliminar.php?id=<?php echo $videojuego->id ?>">Eliminar</a>
     </td>
    </tr>
   <?php } /*Cerrar llave, fin de foreach*/ ?>

  </tbody>
 </table>
</body>
</html>

Como vemos, casi nada cambia. Únicamente ponemos un foreach y dibujamos un td dentro de él. Después de registrar muchos videojuegos, la tabla puede verse así:

Tabla dinámica con datos de SQLite3 usando PHP y PDO

Editando o modificando datos

Formulario

Ya hemos listado nuestros datos. Ahora vamos a ver cómo editarlos. Dentro de nuestra tabla tenemos un link que tiene el formato editar.php?id=1 y así sucesivamente por cada videojuego.

Lo que haremos ahora será crear ese archivo, tomar la variable del id, consultar el videojuego con ese id, llenar el formulario y permitir guardar.

<?php
if (empty($_GET["id"])) {
 exit("No hay id");
}

include_once __DIR__ . "/base_de_datos.php";
$sentencia = $baseDeDatos->prepare("SELECT * FROM videojuegos WHERE id = :id LIMIT 1;");
$sentencia->bindParam(":id", $_GET["id"]);
$sentencia->execute();
$videojuego = $sentencia->fetch(PDO::FETCH_OBJ);
if ($videojuego === FALSE) { #Si no existe ningún registro con ese id
 exit("No hay ningún videojuego con ese ID");
}

# si llegamos hasta aquí es porque el videojuego sí existe

?>
<!DOCTYPE html>
<html lang="es">
<head>
 <meta charset="UTF-8">
 <title>Editar un videojuego</title>
</head>
<body>
 <form action="guardar_cambios.php" method="post">
  <!-- Guardamos el ID del videojuego para enviarlo con los demás datos -->
  <!-- Este ID no debe ser cambiado, pues con él haremos el update -->
  <input name="id" type="hidden" value="<?php echo $videojuego->id ?>">
  <label for="titulo">Título</label>
  <input value="<?php echo $videojuego->titulo; ?>" type="text" id="titulo" name="titulo" required placeholder="El título del videojuego">
  <br>
  <br>
  <label for="anio">Año</label>
  <input value="<?php echo $videojuego->anio; ?>" type="number" id="anio" name="anio" required placeholder="Escribe el año del videojuego">
  <br>
  <br>
  <label for="genero">Género</label>
  <input value="<?php echo $videojuego->genero; ?>" type="text" id="genero" name="genero" required placeholder="Escribe el género del videojuego">
  <br><br>
  <button type="submit">Guardar</button>
 </form>
 <br>
 <a href="3_tabla_dinamica.php">Ver todos los videojuegos</a>
</body>
</html>

Las cosas importantes que hay que notar es que primero recibimos la variable por medio de $_GET["id"].

En caso de que el videojuego exista, mostramos el formulario previamente llenado. Para ello utilizamos el atributo value de la etiqueta input.

También guardamos en el formulario el id del videojuego, para poder recibirlo más tarde. Este campo está oculto, ya que lo necesitamos pero el usuario no debe poder editarlo.

Cuando el formulario se envíe, se llamará al archivo guardar_cambios.php cuya programación viene a continuación.

Procesar edición del videojuego

Vamos a guardar los cambios realizados al videojuego para que queden registrados en nuestra base de datos de SQLite3.

Es casi como el de insertar, sólo que ahora haremos un UPDATE y utilizaremos el ID del videojuego.

<?php

if (empty($_POST["id"])) { # En este caso también necesitamos al ID
 exit("Faltan uno o más datos"); #Terminar el script definitivamente
}

if (empty($_POST["titulo"])) {
 exit("Faltan uno o más datos"); #Terminar el script definitivamente
}

if (empty($_POST["anio"])) {
 exit("Faltan uno o más datos"); #Terminar el script definitivamente
}

if (empty($_POST["genero"])) {
 exit("Faltan uno o más datos"); #Terminar el script definitivamente
}

#Si llegamos hasta aquí es porque los datos al menos están definidos
include_once __DIR__ . "/base_de_datos.php"; #Al incluir este script, podemos usar $baseDeDatos

# creamos una variable que tendrá la sentencia
$sentencia = $baseDeDatos->prepare("UPDATE videojuegos 
 SET anio = :anio,
 titulo = :titulo,
 genero = :genero
 WHERE id = :id");



#Pasar los datos...
$sentencia->bindParam(":id", $_POST["id"]);#Aquí pasamos el ID
$sentencia->bindParam(":anio", $_POST["anio"]);
$sentencia->bindParam(":titulo", $_POST["titulo"]);
$sentencia->bindParam(":genero", $_POST["genero"]);
$resultado = $sentencia->execute();
if($resultado === true){
 echo "Videojuego guardado correctamente";
 echo '<br><a href="3_tabla_dinamica.php">Ver los videojuegos</a>';
}else{
 echo "Lo siento, ocurrió un error";
}
?>

Como lo dije, es muy parecido al de insertar. Sólo que ahora añadimos el campo que tiene el ID y la consulta cambia de ser un INSERT a ser un UPDATE.

Adicionalmente, después de editar mostramos un enlace para ver los cambios realizados.

Eliminar datos

Para terminar con este tutorial veremos cómo eliminar un dato de SQLite3 utilizando el ID. Es importante mencionar que en aplicaciones de la vida real no debemos dejarlo así de simple ya que nos exponemos a un ataque CSRF.

En fin, ahora lo dejaremos así para efectos de simplicidad pero puedes leer el post que dejo arriba para ver cómo prevenirlo.

Una vez dicho eso, continuemos.

Creación del archivo

Vamos a crear el archivo llamado eliminar.php que será parecido al de editar.php. Igualmente recibirá un parámetro por $_GET y dependiendo del mismo eliminará un videojuego.

El código queda así:

<?php

if (empty($_GET["id"])) {
 exit("No hay ID");
}

#Si llegamos hasta aquí es porque los datos al menos están definidos
include_once __DIR__ . "/base_de_datos.php"; #Al incluir este script, podemos usar $baseDeDatos

# creamos una variable que tendrá la sentencia
$sentencia = $baseDeDatos->prepare("DELETE FROM videojuegos WHERE id = :id");



#Pasar los datos...
$sentencia->bindParam(":id", $_GET["id"]);#Aquí pasamos el ID
$resultado = $sentencia->execute();
if($resultado === true){
 echo "Videojuego eliminado correctamente";
 echo '<br><a href="3_tabla_dinamica.php">Ver los videojuegos</a>';
}else{
 echo "Lo siento, ocurrió un error";
}
?>

Lo que hacemos es ejecutar una operación DELETE en donde el id sea el que pasamos por GET. Y así queda terminado nuestro CRUD con SQLite3 y PHP usando PDO.

Proyecto terminado

Puedes probar el proyecto online aquí:

Ejemplo de SQLite y PDO con PHP online

Igualmente puedes descargarlo:

crud_sqlite

Estoy aquí para ayudarte 🤝💻

Estoy aquí para ayudarte en todo lo que necesites. Si requieres alguna modificación en lo presentado en este post, deseas asistencia con tu tarea, proyecto o precisas desarrollar un software a medida, no dudes en contactarme. Estoy comprometido a brindarte el apoyo necesario para que logres tus objetivos. Mi correo es parzibyte(arroba)gmail.com, estoy como@parzibyte en Telegram o en mi página de contacto

No te pierdas ninguno de mis posts 🚀🔔

Suscríbete a mi canal de Telegram para recibir una notificación cuando escriba un nuevo tutorial de programación.

parzibyte

Programador freelancer listo para trabajar contigo. Aplicaciones web, móviles y de escritorio. PHP, Java, Go, Python, JavaScript, Kotlin y más :) https://parzibyte.me/blog/software-creado-por-parzibyte/

Siguiente Python 3 y MySQL: CRUD y ejemplos de conexión »

Anterior « Redondear números en JavaScript

Ver comentarios

Javier dice:

septiembre 3, 2022 en 8:49 am

No se de cuando es este artículo pero SQLite sí soporta multihilo. De hecho es el modo por defecto (llamado "serialized thread model")