El uso de tablas en las páginas es un recurso muy utilizado para mostrar información a los usuarios. Suelen tratarse de tablas estáticas donde el usuario no puede modificar los datos que aparecen en ellas.
Que esto sea lo habitual, no significa que no podamos crear tablas que nos permitan modificar los datos de sus celdas. Seguramente has visto sitios webs en los que es posible editar la información de una tabla directamente sobre la misma tabla, haciendo clic sobre las celdas.
Esta, es una característica muy usable que mejora muy mucho la experiencia del usuario. En este tutorial te mostraremos cómo implementar tablas HTML editables con jQuery y PHP. La información de la tabla que vamos a editar la extraeremos de una tabla de nuestra base de datos MySQL.
Para gestionar la función de editar la tabla inline necesitaremos el plugin tabledit, un plugin para jQuery.
Qué se necesita para crear la tabla editable
Como creemos que puede tratarse de un recurso muy útil para cualquier proyecto web, en nuestro White Paper de este mes os explicaremos cómo podemos crear una tabla editable utilizando el lenguaje de programación PHP, base de datos MySQL y jQuery.
El funcionamiento general se divide en varias partes:
- Crear la base de datos y la tabla que almacenará la información.
- Establecer la conexión entre PHP y MySQL.
- Recuperar los registros de la base de datos.
- Mostrar los registros dentro de una tabla HTML.
- Aplicar el plugin Tabledit mediante jQuery.
- Enviar mediante POST los cambios realizados en las celdas.
- Actualizar los registros en MySQL con una consulta UPDATE.
Crear la base de datos y sus tablas
Continuando con el diagrama de nuestra base de datos, ahora podemos pasar a la acción, creando nuestra primer base de datos y sus tablas.
Para crear nuestra base de datos, primero debemos asegurarnos que en nuestro localhost esté encendido el servicio MySQL.
El manejo de sistemas web y/o páginas web hoy en día requiere de una base de datos para almacenar la información que genera. En esta publicación explicaremos como podemos crear tablas de manera dinámica en el lenguaje PHP.
Por lo tanto, si estamos creando sistemas web, lo primero es crear tablas apoyados con la sentencia «CREATE TABLE» de MySQL.
Definir las columnas y sus atributos
Las características de una tabla implica tener las columnas y sus respetivos atributos, por ejemplo pueden ser de:
- Texto
- Numéricos
- Fechas
- Fecha y hora
- Decimal, etc.
Esto es muy fácil de entender. Creamos una tabla con un nombre X, dentro de la apertura y cierre de paréntesis vamos a definir los atributos de esta tabla.
Lo primero que debemos hacer es definir el nombre del atributo y luego su tipo, entre paréntesis indicamos la cantidad de dígitos que tendrá éste.
Para este ejemplo usaremos cuatro columnas que describiremos a continuación.
- Alumno_id
- Nombres
- Apellidos
- F_nacimiento
El campo Alumno_id es de tipo número entero que posee 10 caracteres de longitud. Por lo tanto, se muestra un tipo de datos similar a «Alumno_id INT (10)». Sin embargo, esta columna será la clave principal de la tabla alumnos que estamos generando.
La columna de «Nombres». Sera el encargado de almacenar el nombre del alumno. Le estamos asignando 80 caracteres, se representa de la siguiente manera «nombres VARCHAR (80)».
Ahora, la columna de «Apellidos». Sera el encargado de almacenar el apellidos del alumno.
El campo fecha de nacimiento «f_nacimiento» del alumno le estamos asignando el tipo de datos FECHA.
Aquí estamos mostrando un comando SQL con una instrucción que creará una tabla llamada alumnos como se describió líneas arriba.
Tipos de datos útiles para la tabla
Existen cinco tipos de datos numéricos, estos dependerán del valor máximo que necesitemos guardar, del volumen de información.
En primer lugar, cada tipo de dato soportará hasta un valor máximo dependiendo de si tiene signo o no. O sea, si soporta valores negativos o sólo positivos.
Por ejemplo, el tipo de dato tinyint, configurado sin signo (sólo números positivos) tendrá un rango que irá desde el valor 0 hasta el 255, de lo contrarió será desde -128 hasta 127.
Por un lado tenemos char y varchar, los cuales nos permitirán guardar cadenas de hasta 255 caracteres.
La principal diferencia entre ambos es que char usa la cantidad de caracteres que asignemos, rellenando los caracteres restantes con espacios.
Por ejemplo podemos configurar nuestro atributo como un char de 30 caracteres, pero si insertamos registros con valores menores a 30, por ejemplo un valor de 12, éste se guardará con los caracteres que ingresamos, y automáticamente agregará espacios en blanco hasta completar los 30.
Esto con varchar no sucede, sólo se usarán la cantidad de caracteres del registro guardado.
char y varchar son útiles para guardar cadenas pequeñas, por ejemplo nombres de personas.
Pero si necesitamos guardar valores muy grandes, deberíamos usar blog o text, que nos permitirán almacenar extensas cadenas como por ejemplo la letra de una canción.
La diferencia entre ambas es que blog almacena datos binarios, mientras que text nos permiten guardar cadenas de caracteres.
Y por últimos tendremos los tipos de datos enum y set, que nos permitirán guardar un valor extraído de una lista.
Por ejemplo un atributo sexo, que sólo podrá ser dos valores: ‘hombre’ o ‘mujer’.
Estos tipos de datos pueden ser date, para guardar una fecha, el formato, por ejemplo para el 23 de Diciembre del año 2013, debe ser de la siguiente manera: ‘2013-12-23’.
Si en cambio quisiéramos guardar la fecha y hora en un mismo campo deberíamos usar datetime o timestamp.
La diferencia entre ambos es que datetime guarda sus valores con el siguiente formato: ‘2013-12-23 00:56:41’, mientras que timestamp comprenderá el formato unix, osea la cantidad de segundos desde 1 de Enero de 1970 a las 00:00:00, la misma fecha y hora que guardamos con datetime podríamos guardarla en una columna timestamp de esta forma ‘1387756601’.
Incluir una clave primaria y campos automáticos
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.
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.
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.
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.
Bien, en primer lugar vamos a usar una nueva instrucción para nuestro código SQL, la palabra reservada unique, que nos permite que el valor de cada registro nuevo a insertar no sea repetido, osea que no haya otro registro con ese mismo nombre.
Por otro lado agregamos dos campos nuevos fecha_alta y fecha_modificacion, que nos permitirá guardar la fecha y hora en que se inserta un nuevo usuario y la fecha y hora cuando se modifica, respectivamente.
Por último creamos un atributo nacionalidad_id, que será una clave externa, que estará unida a la clave primaria nacionalidad_id, de la tabla nacionalidad.
Esto nos permitirá que todo cambio y eliminación de registros en la tabla padre, en nuestro caso nacionalidad, afectará a los registros relacionados con la tabla hija, en nuestro caso usuario.
Pero cómo habíamos dicho que la tabla interes tendría una relación de n a n (muchos a muchos) con la tabla usuario, y que también este tipo de relaciones generan una nueva tabla intermedia.
Bien, con esto tenemos nuestra primer base de datos, con sus tablas y las tres posibles relaciones.
Conectar PHP con MySQL
Lo primero que tendremos que hacer será crearnos la tabla donde recuperaremos los datos que se mostrarán en la tabla editable que vamos a crear.
Este archivo será donde tendremos el código que utilizaremos para conectarnos a la base de datos.
En este archivo se almacenan los datos necesarios para establecer la conexión:
- Servidor.
- Usuario.
- Contraseña.
- Nombre de la base de datos.
Código de conexión:
$conn = mysqli_connect($servername, $username, $password, $dbname) or die("Connection failed: " .
Aquí lo que tenemos son una serie de variables donde almacenaremos los datos de nuestra conexión.
Para establecerla, utilizaremos la función “mysqli_connect” a la que le pasaremos nuestros datos.
Conexión mediante PDO
Mostramos ejemplo usando (PDO)
Si, nuestro proyecto usa PDO.
Insertar registros en MySQL
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.
$sql = 'INSERT INTO Messages (title, bodytext, created) VALUES ("'. $p["title"] . '", "' . $p["bodytext"] . '", ' . time() .
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.
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.
"'. $p["title"] .
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.
Seleccionar y mostrar los registros
Una vez creada la base de datos, empezaremos con el código que tendremos en el archivo index.php.
Lo siguiente será añadir el código que se encargará de pintar la tabla con los datos.
$resultset = mysqli_query($conn, $sql_query) or die("error base de datos:".
Como podéis ver, ser trata del código habitual que utilizamos para crear las tablas con la diferencia de que al tag “<table>” le hemos indicado como “id” el valor “data-table”.
Ahora podemos mostrar estos registros usando el método display_public(), el cual también vamos a modificar.
print '<span class="time">#' . $row['mid'] . ' de ' . date('d-m-Y', $row['created']) . '</span><h2>' . $row['title'] .
print '<p>' . $row['bodytext'] .
Ya he reemplazado completamente la implementación del método display_public(), ahora todos los registros se muestran desde la base de datos.
Cómo funciona SELECT
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.
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.
Creo que puede ser difícil de entender al principio, pero con el tiempo te acostumbras a escribir consultas en SQL.
Ordenar y filtrar la selección
También se puede establecer el orden de la selección usando el operador ORDER BY.
Así ordenamos la selección por mid de forma ascendente.
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.
$sql = 'SELECT * FROM Messages WHERE mid<3 AND created>' . $limit_time .
Así aplicamos una condición adicional: que el registro se haya creado no antes de hace una hora.
Convertir la tabla HTML en editable
Lo siguiente que veremos será el código del archivo “table_edit.js” que se encargará de hacer editable nuestra tabla.
Para ello, recordemos que utilizaremos el plugin para jQuery Tabledit.
Lo primero que vemos es que este código no se ejecutará hasta que no se haya cargado todo el DOM de la página.
Luego, lo que se hace es recuperar la referencia a nuestra tabla (recordemos que le habíamos indicado como “id” el valor “data_table”).
A esa referencia será a la que se le aplique las funcionalidades del plugin Tableedit.
En el fichero custom_table_edit.js, llamaremos a la función Tabledit() para hacer que las celdas de nuestra tabla sean editables.
Para ello necesitaremos configurar el plugin según nuestros menesteres.
Entre las opciones de configuración, destacar la importancia de la propiedad url en la que definiremos el archivo que realizará los cambios en nuestra base de datos.
Configuración de las columnas:
columns: sección donde se indica las columnas que tendrá la tabla.
| Elemento | Función |
|---|---|
| id | Identifica la tabla HTML y permite aplicar Tabledit. |
| Tabledit() | Activa la edición de las celdas. |
| columns | Indica las columnas que tendrá la tabla. |
| url | Define el archivo que realizará los cambios en la base de datos. |
Guardar los cambios en MySQL
Por último tenemos el archivo editarCelda.php que será el encargado de guardar en base de datos los cambios realizados en las celdas de la tabla.
$sql_query = "UPDATE libro SET $update_field WHERE id='" . $input['id'] .
mysqli_query($conn, $sql_query) or die("database error:".
Aquí, lo primero que nos encontramos, es la llamada al archivo de conexión a la base de datos que hemos explicado anteriormente.
Lo siguiente es recuperar la información que hay que modificar.
Para ello hacemos uso de la función de PHP “filter_input_array” que se encarga de recuperar todos los valores que se envían por GET, POST, COOKIE…
En nuestro caso la información se envía mediante el método POST, de ahí que se utilice el valor “INPUT_POST”.
El siguiente paso será comprobar que la acción que se ha enviado es la de editar.
$sql_query = "UPDATE libro SET $update_field WHERE id='" . $input['id'] .
mysqli_query($conn, $sql_query) or die("database error:".
Actualizar un registro seleccionado
El flujo de actualización es el siguiente:
- El usuario selecciona o edita una celda de la tabla.
- Tabledit identifica el registro y la columna modificada.
- El plugin envía los datos al archivo definido en la propiedad url.
- PHP recupera los valores enviados mediante POST.
- Se comprueba que la acción recibida sea la de editar.
- Se construye la consulta UPDATE.
- MySQL guarda el nuevo valor en el registro correspondiente.
Mostrar únicamente determinadas tablas de una base de datos
Hola.. haciendo referencia a un post muy bien respondido me gustaria entender como generar un filtro para mostrar una lista de determinadas tablas de una BD.. es decir, no quiero mostrar filas de una tabla... quiero mostrar Tablas de una BD, pero seleccionar que tablas mostrar..
jackson666 me hecho un buen cable, solucionando como mostrar todas las tablas, y funciona perfecto... solo que no comprendo como manejar bien el filtro para el fin que ya mencione, estoy leyendo las referencias de MYSQL, pero no hallo la manera..
Gracias por los aportes
Comprobar la estructura desde phpMyAdmin
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.
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.
Observa las comillas invertidas que se usan ahí, son las que utiliza phpMyAdmin.
En PHP podemos usar comillas simples normales.
Ahora verifiquemos cómo funciona esto.
Confirma la eliminación.