Lección de PHP - Lección 3.2 - Trabajo con bases de datos MySQL. Inserción de datos con INSERT INTO. Consulta de datos con SELECT

En la lección anterior creamos una tabla para nuestro sitio. En esta lección vamos a mejorar nuestra tabla y ya comenzaremos a trabajar con la base de datos: insertar datos en ella y extraer estos datos de la tabla. Creo que no se espera nada complicado, así que comencemos.

Para empezar, propongo mejorar nuestra tabla messages. Actualmente tiene campos para los datos, pero necesitamos un campo más para la numeración de los registros. Si abres la base de datos de Drupal, verás que en la tabla node existe un campo nid, por el cual numeramos nuestros nodos. También deberíamos hacer eso con nuestra tabla messages.

Vamos a entrar a phpMyAdmin (http://localhost/tools/phpmyadmin para Denwer) y editamos nuestra tabla. Agregamos una columna más al principio de la tabla, como se muestra en la imagen:

Llamamos a la columna mid (message id), tipo de datos INT (entero), longitud de 11 caracteres. También marcamos la casilla AUTO_INCREMENT, lo que significa numeración automática en esta columna. Y también establecemos el valor primary en el campo Index, lo que significa que será la clave primaria de nuestra tabla.

Si recuerdas, creamos un método separado para la clase simpleCMS para crear la tabla. Creo que ahora necesitamos cambiar el funcionamiento de este método para que también agregue el campo mid. Entra a la tabla messages y haz clic en exportar:

Entraremos a la página de exportación de la tabla. La exportación se realiza en forma de consultas SQL para crear y editar tablas. Si editamos una tabla en phpMyAdmin, luego podemos copiar la consulta SQL para su creación, lo cual es muy conveniente.

En la página de exportación deja todo por defecto y haz clic en OK. PhpMyAdmin te mostrará la consulta SQL directamente en el navegador o como un archivo. Ambas opciones nos sirven. Necesitaremos copiar este código:

Observa las comillas invertidas que se usan ahí, son las que utiliza phpMyAdmin. En PHP podemos usar comillas simples normales. Copiamos el texto en nuestro método buildDB():

  public function buildDB(){
    $sql = "CREATE TABLE IF NOT EXISTS 'messages' (
              'mid' int(11) NOT NULL AUTO_INCREMENT,
              'title' varchar(150) DEFAULT NULL,
              'bodytext' text,
              'created' varchar(100) DEFAULT NULL,
              PRIMARY KEY ('mid')
            ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1";
    return mysql_query($sql);    
  }

Ahora verifiquemos cómo funciona esto. Vamos a eliminar la tabla messages.

Confirma la eliminación. Ahora cuando ejecutemos index.php en nuestro sitio, se ejecutará el método buildDB() y nuestra tabla se debe crear de nuevo.

  public function buildDB(){
    $sql = "CREATE TABLE Messages
    (
    mid int NOT NULL AUTO_INCREMENT,
    PRIMARY KEY(mid),
    title varchar(15),
    bodytext text,
    created  int(11)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1";
    $result = mysql_query($sql);   
    print_r($result);	
  }

Ahora ejecutamos index.php y nos aparece la tabla Messages, tal como antes.

Consultas de inserción INSERT INTO

La base de datos está creada, todo está listo para implementar nuestros métodos de escritura y visualización de mensajes usando la base de datos. Comencemos con la escritura en la base. Abre el método write(), lo vamos a modificar.

La escritura en las tablas se realiza con el operador INSERT INTO de la siguiente manera:

  public function write($p) { // método de escritura de mensaje
    $sql = 'INSERT INTO Messages (title, bodytext, created) VALUES ("'. $p["title"] . '", "' . $p["bodytext"] . '", ' . time() . ')';
    return mysql_query($sql);
  }

Ahora entendamos cómo se insertan los valores. Como mencioné antes, la consulta SQL de inserción comienza con el operador INSERT INTO, seguido por el nombre de la tabla Messages. Después del nombre de la tabla, escribimos en qué columnas insertar los valores. Observa que no escribimos mid, porque su valor será asignado automáticamente por MySQL, ya que habilitamos AUTO_INCREMENT para esta columna. Luego va el operador VALUES (valores), después del cual escribimos los valores para cada columna de la tabla. El orden de las columnas y de los valores debe coincidir, al igual que la cantidad.

Si hay que insertar un valor de tipo cadena, usamos comillas. Debemos asegurarnos que estas comillas sean diferentes de las que envuelven toda la consulta SQL. Y no te confundas cuando insertes una variable en la consulta SQL: la parte de texto del SQL está entre comillas simples, por lo que la cadena que sale de la variable debe estar entre comillas dobles. Por ejemplo aquí:

"'. $p["title"] . '"

El orden de las comillas es: doble, simple, punto, variable, punto, simple, doble.

Creo que por ahora un ejemplo es suficiente, más adelante habrá otros ejemplos, así que no te preocupes si no lo entendiste a la primera. Mejor creemos un par de registros en la tabla, para eso reemplaza el código del método write() con el que te di arriba.

Consultas de selección SELECT

Creamos algunas filas, las miramos a través de phpMyAdmin:

Ahora podemos mostrar estos registros usando el método display_public(), el cual también vamos a modificar. Hacemos la selección de la tabla con el operador SELECT, comencemos con una selección simple:

  public function display_public() { // método de visualización de mensajes
    $content = '';

    $sql = 'SELECT * FROM Messages'; //consulta de selección
    $result = mysql_query($sql);  // resultado de la consulta lo almacenamos en una variable
    
    while($row = mysql_fetch_array($result)){ // procesamos la variable de consulta con mysql_fetch_array()
      print '<div class="post">';
      print '<span class="time">#' . $row['mid'] . ' de ' . date('d-m-Y', $row['created']) . '</span><h2>' . $row['title'] . '</h2>';
      print '<p>' . $row['bodytext'] . '</p>';
      print '</div>';
    }

    $content .= '<p><a href="/index.php?admin=1">Agregar mensaje</a></p>';
    return $content;
  }

Ya he reemplazado completamente la implementación del método display_public(), ahora todos los registros se muestran desde la base de datos. Analicemos este ejemplo. La consulta de selección comienza con el operador SELECT seguido del símbolo * (asterisco), lo que significa que queremos seleccionar todos los campos de cada fila de la tabla. Si hubiéramos escrito esto:

$sql = 'SELECT mid,title FROM Messages'; //consulta de selección
$result = mysql_query($sql);

Significaría que solo vamos a seleccionar mid y title de cada fila, mientras que bodytext y created no nos interesan.

Después del * va el operador FROM, que indica de qué tabla vamos a seleccionar.

El resultado de la función mysql_query() para una consulta de selección debe guardarse en una variable (en este caso $result), porque luego esa variable debe ser procesada con la función mysql_fetch_array().

Imagina que hicimos una consulta SELECT y obtuvimos 3 registros, entonces la función mysql_fetch_array() permite formar un array uno por uno en cada llamada. Es decir, en la primera llamada obtendremos un array de la primera fila, en la segunda el de la segunda fila, y así sucesivamente. Cuando se terminen las filas, la función devolverá false. Por eso se puede usar un bucle while (mientras la condición se cumple, ejecutamos las acciones). Esto permite realizar acciones iguales para todos los registros de la tabla. Así iteramos por cada registro mientras se cumpla la condición.

Creo que puede ser difícil de entender al principio, pero con el tiempo te acostumbras a escribir consultas en SQL. No hay tantos operadores, así que se aprenden rápido.

Consultas de selección, ordenación con ORDER BY

También se puede establecer el orden de la selección usando el operador ORDER BY. Por defecto el orden es ascendente, por ejemplo:

$sql = 'SELECT * FROM Messages ORDER BY mid';

Así ordenamos la selección por mid de forma ascendente. También se puede ordenar de forma descendente, para eso usamos el operador adicional DESC.

$sql = 'SELECT * FROM Messages ORDER BY mid DESC';

Como puedes ver es muy sencillo, si para descendente usamos DESC, entonces para ascendente usamos ASC:

$sql = 'SELECT * FROM Messages ORDER BY mid ASC';

Aunque el orden ascendente se usa por defecto.

Consultas de selección, condiciones WHERE

Además de la ordenación, podemos filtrar las consultas de selección estableciendo condiciones con el operador WHERE:

$sql = 'SELECT * FROM Messages WHERE mid<3 ORDER BY mid DESC';

Así mostramos los primeros dos registros (con mid=1 y mid=2), si es que existen en la tabla. También se pueden usar los operadores AND (y) y OR (o), para conectar múltiples condiciones. Si usamos AND, ambas condiciones deben cumplirse. Si usamos OR, basta con que se cumpla una sola.

$limit_time = time() - 3600; // una hora atrás
$sql = 'SELECT * FROM Messages WHERE mid<3 AND created>' . $limit_time . ' ORDER BY mid DESC';

Así aplicamos una condición adicional: que el registro se haya creado no antes de hace una hora. Creo que por ahora es suficiente sobre consultas SELECT, así que pasemos a la siguiente lección.