Ver índice
PDO – Consultas SQLite / MySQL

        Ocultar índice  

   Índice de contenidos
   Instalación en Windows
   Instalación en Ubuntu
   Servidores seguros
   Páginas dinámicas
   Sintaxis básica
   Operaciones
   Arrays
   Formatos de presentación
   Operadores
   Bucles
   Extraer y ord. información
   Funciones
   Ficheros externos
   Imágenes dinámicas
   Gestión de directorios
   Cookies y sesiones
   Clases y objetos
   Ficheros en formato PDF
   Bases de datos MySQL
   PHP y XML
   PDO - Bases SQLite / MySQL
   MySQL a traves de misqli
   Algo de JavaScript y AJAX


Algunos ejemplos de consultas

Las consultas de los datos y registros contenidos en una tabla ofrecen un amplísimo abanico de posibilidades a partir de las opciones ya descritas en páginas anteriores para las tablas MySQL. Salvo pequeños detalles que iremos comentando a lo largo de los sucesivos ejemplos la sintaxis y los resultados son muy similares entre las consultas a tablas SQLite y MySQL. Repetiremos las consultas ya comentadas anteriormente para estas nuevas tablas creadas y manejadas mediante objetos PDO.

La consulta más simple

Si utilizamos la sentencia

SELECT * FROM tabla

obtendremos información sobre todos los campos (*) y la salida estará en el mismo orden en el que fueron añadidos los datos. Si visualizas este ejemplo, verás que aparecen ordenados por el valor autonumérico del campo Contador lo cual, como ves, resulta coherente con la afirmación anterior.

La única diferencia entre los resultados de la misma consulta en ambas bases de datos estaría en la presentación del campo autonúmerico. En el caso de MySQL aparece con ceros a la izquierda dado que al definir la tabla establecimos TINYINT(8) UNSIGNED ZEROFILL mientras que en caso de SQLite estuvimos obligados configurar ese campo autoincremental como INTEGER PRIMARY KEY.

Si observas el código fuente, en el caso del campo Idioma hemos tenido que hacer un pequeño arreglo en la consulta SQLite para visualizar los mismos datos que en MySQL. La razón es la no admisión de campos tipo SET ni ENUM por parte de SQLite.

Ejecutar consulta Ver código fuente

Consultando sólo algunos campos

Ahora utilizaremos la sentencia

SELECT campo1,campo2, ... FROM tabla

y tendremos como resultado una lista completa, por el mismo orden que la anterior, pero sólo mostrando los campos indicados.

Ejecutar consulta Ver código fuente

La respuesta es idéntica en ambas bases de datos.

  ¡Cuidado!  

En los comentarios contenidos en estos ejemplos puedes ver la forma en la que mysql_fetch_row y mysql_fetch_array tratan los índices escalares de los resultados que producen los SELECT de MySQL.
Los valores de los índices se asignan a los contenidos de los campos por el mismo orden en el que estos se escriben en la sentencia SELECT. El campo1 (primero que se escribe) será recogido por el elemento de índice cero del array, el campo2 será recogido con índice uno y así sucesivamente

Consultando sólo algunos campos y limitando la salida a n registros

Ahora utilizaremos la sentencia

SELECT campo1,campo2, ... FROM tabla LIMIT (n, m)

y tendremos como resultado una lista que contendrá m registros a partir del n+1, por el mismo orden que la anterior, y mostrando los campos indicados.

Ejecutar consulta Ver código fuente

La respuesta es idéntica en ambas bases de datos.

Consultando sólo algunos campos y ordenando la salida

Utilizaremos la sentencia MySQL de esta forma

SELECT campo1,campo2, ... FROM tabla ORDER BY campo_n [ASC|DESC], campo_m [ASC|DESC]

y tendremos como resultado una lista ordenada por el primero de los campos indicados en ORDER BY, y en caso de coincidencia de valores en ese campo, utilizaríamos el criterio de ordenación señalado en segundo lugar.

Ejecutar consulta Ver código fuente

La respuesta es idéntica en ambas bases de datos.

Consulta seleccionando registros

Utilizaremos la sentencia MySQL de esta forma

SELECT campo1, ... FROM tabla WHERE condición

que nos devolverá la lista de registros que cumplen la condición indicada. Aquí tienes un ejemplo muy sencillo.

Ejecutar consulta Ver código fuente

La respuesta es idéntica en ambas bases de datos.

Consultas condicionadas

La claúsula WHERE permite un variado abanico de condiciones, que trataremos de resumir aquí. Las opciones señaladas con (*) tiene alguna diferencia de comportamiento. En el propio ejemplo o en llamada a pie de tabla tienes comentadas sus causas.

Operador Tipo
de campo
Sintaxis Descripción Código
fuente
Ver
ejemplo
= Numérico WHERE campo=num Selecciona los registros que contienen en el campo un valor igual a num Ver Probar
= Cadena WHERE campo="cadena" Selecciona los registros que contienen en el campo una cadena idéntica a cadena (*) Ver Probar
< Numérico WHERE campo<num Selecciona los registros que contienen en el campo un valor menor a num Ver Probar
< Cadena WHERE campo<"cadena" Selecciona los registros que contienen en el campo una cadena cuyos n primeros caracteres son menores que los de la cadena, siendo n el número de caracteres que contiene cadena. (**) Ver Probar
<= Numérico WHERE campo<=num Selecciona los registros que contienen en el campo un valor menor O igual a num Ver Probar
<= Cadena WHERE campo<="cadena" Selecciona los registros que contienen en el campo una cadena cuyos n primeros caracteres son menores que los de la cadena, siendo n el número de caracteres que contiene cadena y añade respecto al caso anterior la opción de que en caso de que ambos valores fueran iguales también los presentaría (**) Ver Probar
> Numérico WHERE campo>num Selecciona los registros que contienen en el campo un valor mayor a num Ver Probar
> Cadena WHERE campo>"cadena" Selecciona los registros que contienen en el campo una cadena cuyos n primeros caracteres son mayores que los de la cadena, siendo n el número de caracteres que contiene cadena. (**) Ver Probar
>= Numérico WHERE campo>=num Selecciona los registros que contienen en el campo un valor mayor o igual a num Ver Probar
>= Cadena WHERE campo>="cadena" Selecciona los registros que contienen en el campo una cadena cuyos n primeros caracteres son mayores que los de la cadena, siendo n el número de caracteres que contiene cadena y añade respecto al caso anterior la opción de que en caso de que ambos valores fueran iguales también los presentaría (**) Ver Probar
IN Numérico
o
Cadena
WHERE campoIN (valor1,valor2..) Selecciona los registros que contienen en el campo valores que coinciden con alguno de los especificados dentro del paréntesis. Cuando se trata de valores no numéricoz han de ir entre comillas Ver Probar
BETWEEN Numérico
o
Cadena
WHERE campo BETWEEN valor1 AND valor2 Selecciona los registros en los que los valores contenidos en el campo seleccionado están comprendidos en el intervalo valor1 (mínimo) – valor2 (máximo) incluyendo en la selección ambos extremos.
Cuando los contenidos de los campos son cadenas sigue los mismos criterios que se indican para los demás operadores de comparación
Ver Probar
IS NULL Cadena WHERE campo IS NULL Selecciona los registros en los que los valores contenidos en el campo seleccionado son NULOS Ver Probar
IS NOT NULL Cadena WHERE campo IS NOT NULL Selecciona los registros en los que los valores contenidos en el campo seleccionado son NO NULOS Ver Probar
(*) Cuando se trata de cadenas de caracteres, el concepto menor que significa anterior en la ordenación de los caracteres según su código ASCII y mayor que significa posterior en esa misma ordenación.
(**) La discriminación de Mayúsculas/Minúsculas dependerá del tipo de campo.
Recuerda que en MySQL los tipo BLOB hacen esa discriminación, mientras que los de tipo TEXT son insensibles a mayúsculas/Minúsculas y también a vocales con y sin tilde. SQLite siempre toma en consideración esas diferencias.

Cuando se trata de comparar cadenas MySQL dispone de una potente instrucción (LIKE) que permite establecer los criterios de selección a toda o parte de la cadena. Su sintaxis contempla distintas posibilidades utilizando dos comodines>: % (que se comporta de forma similar al (*) en las búsquedas de Windows) y _ (de comportamiento similar a (?) en Windows). Aquí tienes algunas de sus posibilidades:

Sintaxis Descripción Código
fuente
Ver
ejemplo
WHERE campo LIKE '%cadena%' Selecciona todos los registros que contengan la cadena en el campo indicado sea cual fuere su posición Ver Probar
WHERE campo LIKE 'cadena%' Selecciona todos los registros en los que el campo indicado que contengan la cadena exactamente al principio del campo Ver Probar
WHERE campo LIKE '%cadena' Selecciona todos los registros en los que el campo indicado que contengan la cadena exactamente al final del campo Ver Probar
WHERE campo LIKE '_cadena%' Selecciona todos los registros en los que el primer caracter del campo puede ser cualquiera pero los siguientes han de ser exactamente los indicados en cadena pudiendo ir seguidos de cualesquiera otros caracteres Ver Probar

El comodín (_) puede ir tanto al principio como al final y puede repetirse tantas veces como sea necesario. Por tanto sería correcto LIKE '___es%' y también LIKE 'a___es%' así como: LIKE '%a___es'.

La claúsula WHERE aun tiene más opciones. Acepta múltiples condiciones vinculadas por los operadores lógicos AND, OR, NOT o sus sintaxis equivalentes: &&, || y !.

El comportamiento de estos operadores es idéntico al descrito para sus homónimos de PHP en esta página

Utilizando funciones sobre campos

Por medio de la sintaxis

SELECT MAX(campo1), MIN (campo2), ... FROM tabla

obtendríamos UNA SOLA FILA cuyos valores serían los resultados de la aplicación de las funciones a todos los registros del campo indicado. Aquí tienes un ejemplo que determina todos los valores de esos estadísticos aplicados al campo Contador de nuestra tabla demo4.

Ejecutar consulta Ver código fuente

Observa que en el código fuente hemos incluido dos consultas distintas y que la consulta MySQL devuelve un valor más que SQLite. Se debe a que la función STDDEV (desviación típica de la población) no está definida en SQLite.

Aplicando la opción GROUP BY

Tal como señalamos al margen, las funciones anteriores pueden aplicarse a grupos de registros seleccionados mediante un criterio GROUP BY (nombre del campo)

En este ejemplo obtendremos los mismos parámetros estadísticos que en el anterior, pero ahora agrupados por sexo, lo que significaría que obtendremos dos filas de resultados. Aquí tienes el ejemplo

Ejecutar consulta Ver código fuente

Es válido el comentario del caso anterior para STDDEV (desviación típica de la población) que no está definida en SQLite.

Manejo de fechas en las consultas

MySQL dispone de algunas cláusulas de gestión de fechas que pueden tener una gran utilidad a la hora de gestionar consultas. En el caso de SQLite las cosas son algo distintas. No dispone de tales herramientas sin embargo si posee algunas funciones que permiten obtener resultados similares. En los ejemplos utilizamos las siguientes:

substr(campo,posicion_inicial,numero_caracteres)

extrae del contenido del campo de la tabla indicado el numero_caracteres comenzando a partir del que ocupa la posicion_inicial. Si posicion_inicial es un número negativo la determinará contando los caracteres contenidos en el campo de derecha a izquierda.

Formato Descripción Sintaxis Ver código Ver ejemplo
%d Día del mes en formato de dos dígitos DATE_FORMAT(Nacimiento,'%d') Ver Probar
%e Día del mes en formato de uno ó dos dígitos DATE_FORMAT(Nacimiento,'%e') Ver Probar
%m Número del mes en formato de dos dígitos DATE_FORMAT(Nacimiento,'%m') Ver Probar
%c Número del mes en formato de uno o dos dígitos DATE_FORMAT(Nacimiento,'%c') Ver Probar
%y Número del año en formato de dos dígitos DATE_FORMAT(Nacimiento,'%y') Ver Probar
%Y Número del año en formato de cuatro dígitos DATE_FORMAT(Nacimiento,'%Y') Ver Probar

Creación de tablas a partir de la consulta de otra tabla

Reiteramos aquí lo ya comentado cuando estudiábamos MYSQL. Es frecuente -podría decirse que es lo habitual- relacionar tablas mediante campos con idéntico contenido. Supongamos que entre los individuos de nuestra tabla demo4 se pretende establecer un proceso de selección para elegir entre ellos un número determinado de astronautas, pongamos por caso.

Supongamos también, que la selección va a constar de tres pruebas que serán juzgadas y calificadas por tres tribunales distintos.

Una primera opción sería crear tres tablas –una para cada tribunal– e incluir en ellas todos los datos de cada uno de los individuos. Esa opción es factible pero no es ni la más cómoda, ni tampoco es la más rápida ni la que menos espacio de almacenamiento necesita. No debemos olvidar que una tabla puede tener una enorme cantidad de registros.

Una opción alternativa sería crear tres nuevas tablas que sólo contuvieran dos campos cada una. Por ejemplo el campo DNI y el campo Calificación. Como quiera que el campo DNI ha de contener los mismos valores en las cuatro tablas y además es un campo único podrían crearse las nuevas tablas y luego copiar en cada una de ellas todos los DNI de la tabla original. Nos garantizaría que no habría errores en los DNI y además nos garantizaría que se incluyeran todos los aspirantes en esas nuevas tablas.

Aquí tienes el código fuente de un script que crea esas tres tablas (a las que hemos llamado prueba1, prueba2 y prueba3.

Ver código fuente

Una consulta conjunta de varias tablas

MySQL permite realizar consultas simultáneas en registros situados en varias tablas.

Para ese menester se usa la siguiente sintaxis:

SELECT tabla1.campo1, tabla2.campo2, ... FROM tabla1, tabla2

en la que, como ves, modificamos ligeramente la sintaxis ya que anteponemos el nombre de la tabla al del campo correspondiente separando ambos nombres por un punto, con lo cual no hay posibilidad de error de identificación del campo incluso cuando campos de distinta tabla tengan el mismo nombre.

    Otra innovación -respecto a los ejemplos anteriores- es que detrás de la cláusula FROM escribimos los nombres de todas las tablas que está usando SELECT.

    A partir de ahí se pueden establecer todo tipo de relaciones para las sentencias WHERE, ORDER BY y GROUP BY utilizando para ello campos de cualquiera de las tablas sin otra particularidad más que poner cuidado al aludir a los campos utilizando siempre la sintaxis nombre_tabla.nombre_campo.

    A modo de ejemplo –hemos procurado comentarlo línea a línea– aquí tienes un script PHP que hace una consulta conjunta de las tablas demo4, demodat1, demodat2 y demodat3 y nos presenta una tabla con los datos personales y las puntuaciones de las tres pruebas así como las suma de puntos de las tres y, además, ordena los resultados -de mayor a menor- según la suma de las tres puntuaciones.

Ejecutar consulta Ver código fuente

Declaraciones preparadas (Prepare statement)

La clase PDO dispone de una alternativa al método query que conviene conocer. Requiere realizar el proceso de consulta en dos pasos que utilizan los métodos siguientes:

$actuacion= $objeto->prepare(SENTENCIA)

El método prepare prepara la SENTENCIA para su ejecución devolviendo el resultado como un objeto $actuacion. Este método no ejecuta la sentencia, solo la prepara.

$actuacion-> execute()

El método execute es propio de los objetos resultantes de la $actuacion de una sentencia. Devuelve el objeto resultado de la ejecución de esa sentencia. Este objeto permite extraer y organizar los resultados mediante métodos como estos:

$recuento=$actuacion-> rowCount()

También rowCount es un método propio de los objetos resultantes de una $actuacion . La variable recuento contiene el número de registros afectados por la ejecución de esa sentencia.

Cuando se trata de sentencias que devuelven resultados (caso típico de las consultas) pueden utilizarse algunos métodos que facilitan la organización de la información. Los más útiles pueden ser estos:

$resultado=$actuacion-> fetch(PDO::FETCH_NUM )

aplicando el método fetch al objeto que resulta de execute ($actuacion) se recoge el $resultado en un array escalar cuyo primer índice es cero y que sigue el mismo orden por el que los campos han sido incluidos en la consulta en el registro actual.

$resultado=$actuacion-> fetch(PDO::FETCH_ASSOC )

Cuando se incluye FETCH_ASSOC el resultado será un array asociativo cuyos índices coinciden con los nombres de los campos incluidos en la consulta en el registro actual.

$resultado=$actuacion-> fetch(PDO::FETCH_BOTH )

FETCH_BOTH es el valor por defecto. Devuelve un array asociativo indexado por los nombres de los campos y otro escalar indexado (a partir de cero) por las posiciones de los campos en la sentencia SELECT en el registro actual.

$resultado=$actuacion-> fetchAll(PDO::FETCH_BOTH )

Sentencias que devuelven más de un resultado

Cuando una sentencia produce como resultados varios registros –caso habitual en las consultas– las opciones anteriores no ofrecen los resultados esperados. En esos supuestos es necesaria las siguiente modificación:

de esta forma ya estaremos en condiciones de construir esa consulta y efectuar la lectura de los resutados por medio de un bucle tal como puedes ver en este ejemplo.

<?php
include('conecta.inc.php');
$dbMySQL= conecta('ejemploSQLite','MySQL');
$dbSQLite= conecta('ejemploSQLite','SQLite');
$tabla="demo4";
$query= "SELECT Nombre, Apellido1, Apellido2 FROM $tabla WHERE Nombre='Dorotea'";
/* preparamos la consulta*/
$consulta=$dbSQLite->prepare($query);
/* ejecutamos la consulta */
$consulta->execute();
print "<br><br>La consulta SQLite ha producido los siguientes resultados<br><br>";
/* creamos un bucle que irá recorriendo los resultados de la consulta
como consecuencia de haber agregado a fecth el valor PDO::FETCH_ORI_NEXT */
while($resultado=$consulta->fetch(PDO::FETCH_ASSOC,PDO::FETCH_ORI_NEXT)){ 
                     print $resultado['Apellido1']." ".$resultado['Nombre']."<br />";
}
/* repetimos el mismo proceso anterior, esta vez para el objeto MySQL */
$consulta=$dbMySQL->prepare($query);
$consulta->execute();
print "<br><br>La consulta MySQL ha producido los siguientes resultados<br><br>";
while($resultado=$consulta->fetch(PDO::FETCH_ASSOC,PDO::FETCH_ORI_NEXT)){
                     print $resultado['Apellido1']." ".$resultado['Nombre']."<br />"; 
}
?>
Realizar consultas

Otra posibilidad, alternativa a la anterior, nos la ofrece el siguiente método PDO:

$resultado=$actuacion-> fetchAll()

dónde $resultado es un array bidimensional que contiene el resultado de la ejecución de una sentencia previamente preparada mediante $actuacion=$objeto->prepare(SENTENCIA) y ejecutada por medio de $actuacion->execute().

El primer índice del array es de tipo escalar (contiene el número de orden, empezando en cero, de cada registro leído) El segundo índice ofrece dos posibilidades. Una de carácter escalar (siendo su valor el número de orden, partiendo de cero, del nombre del campo en la sentencia que crea la consulta) y otra asociativa siendo el nombre del índice identico a nombre del campo o campos sobre los que se realiza la consulta.

<?php
include('conecta.inc.php');
$dbMySQL= conecta('ejemploSQLite','MySQL');
$dbSQLite= conecta('ejemploSQLite','SQLite');
$tabla="demo4";
$query= "SELECT Nombre, Apellido1, Apellido2 FROM $tabla WHERE Nombre='Dorotea'";
/* preparamos y ejecutamos la consulta */
$consulta=$dbSQLite->prepare($query);
$consulta->execute();
print "<br><br>La consulta SQLite ha producido los siguientes resultados<br><br>";
/* mediante fetchall nos devolverá un array bidimensional con todos los resultados*/
$resultado=$consulta->fetchAll();
/* el bucle nos recorrerá los primeros indices y extraeremos los valores
de los elementos deseados de cada uno de esos indices */
for ($i=0;$i<sizeof($resultado);$i++){
    print $resultado[$i]['Nombre']." ".$resultado[$i]['Apellido1']."<br>";
}
/* repetimos la misma consulta ahora con el objeto MYSQL*/
$consulta=$dbMySQL->prepare($query);
$consulta->execute();
print "<br><br>La consulta MySQL ha producido los siguientes resultados<br><br>";
$resultado=$consulta->fetchAll();
for ($i=0;$i<sizeof($resultado);$i++){
    print $resultado[$i]['Nombre']." ".$resultado[$i]['Apellido1']."<br>";
}
?>
Realizar consultas

La tabla sqlite_master

Auque en algunas situaciones puntuales –las transacciones de las que hablaremos más adelante– será necesario el uso de un fichero temporal llamado rollback journal, la gestión de una base de datos SQLite puede hacerse mediante un único archivo llamado archivo principal de la base de datos.

Ese archivo principal está formado varias por partes iguales, llamadas páginas, de tamaños comprendidos entre 512 y 65536 bytes. El número de páginas máximo puede ser 231 - 2 lo cual significa que un fichero de base de datos SQLite podría llegar a tener (considerando todas las páginas posibles y el tamaño máximo de estas) una dimensión de 140 terabytes.

En la sección de encabezado del archivo principal de base de datos está la tabla sqlite_master, gestionada de forma automática, que contiene la información sobre la ubicación de las restantes tablas (número de página en el que están situadas), índices, view y trigger contenidos en la base de datos. Se crea de forma automática mediante una sentencia similar a esta:

CREATE TABLE sqlite_master(
  type text,
  name text,
  tbl_name text,
  rootpage integer,
  sql text
)

En ella, el campo type de esta tabla recoge, dependiendo del tipo de objeto utilizado, uno de los siguientes valores: 'table', 'index', 'view' o 'trigger'. El campo name contendrá el nombre del elemento(indice, consulta, trigger o tabla). En tbl_name se recoge el nombre de la tabla a la que está asociado el objeto (si el elemento es un índice sería el nombre de la tabla a la que va asociado). Por último, en rootpage se indica el número de página en la que comienza el objeto y el campo de texto sql suele incluir el texto de la sentencia utilizada para la creación del objeto.

Todas las tablas de una base de datos SQLite, están registradas en sqlite_master.

Una consulta como esta nos permite visualizar su contenido.

<?php
include('conecta.inc.php');
$dbSQLite= conecta('ejemploSQLite','SQLite');
/* Escribimos una consulta todos los datos de la tabla correspondiente
  a registros  tipo table */
$llamadaSQLite = "SELECT * FROM sqlite_master WHERE type='table'";

if($resultado=$dbSQLite->query($llamadaSQLite)){
     print "La tabla sqlite_master contiene los siguientes registros <br>";
     foreach ($resultado as $registro=>$contenido){
             print "<b>Tabla del registro n.º: ".$registro."</b><br />";
             foreach ($contenido as $campo=>$valor){
                    if (!is_int($campo)){
                        print "<i>Campo: </i>".$campo;
                        print "<i> Valor: </i>".$valor."<br>";
                    }
             }
         }

} else{
        print "Ha habido un error de PDO: <br />" ;
        echo "<pre>";
            print_r($dbSQLite->errorInfo());
        echo "</pre>";
}
?>
ejemplo529.php

Si ejecutas el ejemplo anterior tal vez te encuentres con una tabla llamada sqlite_sequence en cuya estructura existen únicamente dos campos: name y seq. Esta tabla de crea de forma automática cuando alguna de las tablas que componen la base de datos incluye un campo autoincremental. Será en esta tabla donde se recogan los últimos valores de estos campos en cada una de las tablas que los requieran.

Modificación de la estructura de una tabla

Las modificaciones de tablas utilizan la sentencia ALTER TABLE complementada con las palabras reservadas ADD cuando se trata de agregar un nuevo campo. Si la modificación conlleva la eliminación de un campo el proceso será un poco más complejo ya que habría que hacerlo según el siguiente proceso:

1.º Crear una nueva tabla cuyo diseño incluya los nombres de campos que pretendemos mantener.
2.º Copiar los datos de la tabla actual a la nueva tabla.
3.º Eliminar la tabla antigua.
4.º Cambiar el nombre a la nueva tabla asignándole el de la recién borrada.

Renombrar una tabla

Se puede cambiar el nombre de una tabla utilizando la sentencia:

ALTER TABLE nombre_de_la_tabla RENAME TO nuevo_nombre

dónde el nombre actual de la tabla es nombre_de_la_tabla y el futuro nuevo_nombre

Borrar una tabla

Las sentencias MySQL que permiten borrar una tabla son las siguientes:

DROP TABLE nombre de la tabla
o
DROP TABLE IF EXISTS nombre de la tabla

la diferencia entre ambas radica en que usando la segunda no se generaría ningún error en el caso de que tratáramos de borrar una tabla inexistente.