Revista Tecnología

Como ejecutar multiples updates con Php en una única consulta MySQL

Publicado el 10 noviembre 2014 por Vichaunter

Prácticamente siempre utilizamos bases de datos cuando creamos nuestros programas, sobretodo cuando el resultado que esperamos es dinámico, como pasa cuando trabajamos con Php y MySQL, una combinación pensada para hacer webs dinámicas y escalables. Como hay ciertas restricciones sobre encadenar consultas, vamos a ver como hacer varios updates en una única consulta MySQL.

La base que utilizamos es Php, pero te valdrá para cualquier lenguaje de programación que quieras utilizar siempre que sigas la misma estructura sql, ya que si tu motor es MySQL interpretará la consulta de la misma forma.

Lo primero que quiero explicar es por qué no se pueden encadenar consultas mysql en php. El motivo es muy sencillo, y pasaría por querer reducir el número de ejecuciones sql para que así no haya tanto retraso y en parte optimizar las consultas, pero tenemos un pequeño problema.

¿Por qué Php no ejecuta consultas separadas con punto y coma ;?

Es simplemente una medida de seguridad. Está deshabilitado y es principalmente para evitar las inyecciones SQL. Una consulta de ejemplo que podríamos querer utilizar sería esta:

MySQL UPDATE tabla SET campo = 2 WHERE id = 1; UPDATE tabla SET campo = 67 WHERE id = 2; UPDATE tabla SET campo = 143 WHERE id = 3;

1 UPDATEtablaSETcampo=2WHEREid=1;UPDATEtablaSETcampo=67WHEREid=2;UPDATEtablaSETcampo=143WHEREid=3;

Nuestra idea es que en lugar de hacer un while o un foreach con cada valor y ejecutar una consulta sql en cada uno lo que haríamos sería guardar todas las consultas en una variable o array y luego utilizarlas directamente sobre un query($sql) para así ejecutarlas todas de una vez sin retrasos.

El problema principal viene cuando el sistema queda expuesto, por ejemplo si alguien en un formulario escribiese esto:

MySQL ; UPDATE users SET admin = 1 WHERE username = 'vichaunter';

1 ;UPDATEusersSETadmin=1WHEREusername='vichaunter';

Esta es una forma sencilla de inyectar sql, cambiaríamos el supuesto valor admin a 1 en el usuario vichaunter si estos campos y valores existiesen.

Cómo concatenar updates en mysql con php

Ahora vamos a la parte con sustancia. Como desde php no vamos a poder encadenar consultas por el problema comentado, lo que vamos a hacer es en una única consulta iniciar la actualización de todos los campos. Eso sí, desgraciadamente con este método solo podemos actualizar campos de una tabla a la vez, así que si por ejemplo tenemos una colección de discos y queremos cambiar varios a la vez nos serviría perfectamente.

Teniendo por ejemplo esta tabla:

++++++++++++++++++++++++++++++ + id + nombre + precio + ++++++++++++++++++++++++++++++ + 1 + disco1 + 15,95 + + 2 + disco2 + 10,95 + + 3 + disco3 + 5,50 + + 4 + disco4 + 17,95 + ++++++++++++++++++++++++++++++

12345678 +++++++++++++++++++++++++++++++  id  +  nombre  +  precio  ++++++++++++++++++++++++++++++++  1   +  disco1  +  15,95   ++  2   +  disco2  +  10,95   ++  3   +  disco3  +   5,50   ++  4   +  disco4  +  17,95   +++++++++++++++++++++++++++++++

Ahora imaginate que quieres cambiar el precio de varios discos, por ejemplo el 1,3 y 4 por que están de oferta o cualquier cosa que quieras suponer y esta semana valen 10 en lugar de sus precios habituales.

La consulta final que deberíamos realizar de esta forma es la siguiente:

MySQL UPDATE discos SET precio = CASE id WHEN 1 THEN 10 WHEN 3 THEN 10 WHEN 4 THEN 10 END WHERE id IN (1,3,4)

1234567 UPDATEdiscosSETprecio=CASEid  WHEN1THEN10  WHEN3THEN10  WHEN4THEN10ENDWHEREidIN(1,3,4)

Ten en cuenta que el precio que es 10 puede ser diferente en cada uno. De esta forma estamos diciendo que vamos a actualizar campos en la tabla discos, y que vamos a cambiar la columna precio por la coincidencia con la id. Luego simplemente le decimos que cuando id XX entonces precio XX y por último limitamos las ids para que solo actualize las que tocan, para evitar problemas.

Esto mismo podemos hacerlo actualizando varios campos simplemente agregando otra consulta CASE-END separada por una coma, por ejemplo:

MySQL UPDATE discos SET precio = CASE id WHEN 1 THEN 10 WHEN 3 THEN 10 WHEN 4 THEN 10 END, nombre = CASE id WHEN 1 THEN 'nuevotitulo 1' WHEN 3 THEN 'nuevotitulo 3' WHEN 4 THEN 'nuevotitulo 4' END WHERE id IN (1,3,4)

123456789101112 UPDATEdiscosSETprecio=CASEid  WHEN1THEN10  WHEN3THEN10  WHEN4THEN10END,nombre=CASEid  WHEN1THEN'nuevotitulo 1'  WHEN3THEN'nuevotitulo 3'  WHEN4THEN'nuevotitulo 4'ENDWHEREidIN(1,3,4)

Como ves la única limitación sería utilizar una consulta por cada tabla, lo que podría reducir drásticamente la cantidad de consultas, imagínate que tienes un foreach con 300 campos a actualizar y en cada uno vas a ejecutar una consulta sql que tarda 0.01 segundos. De esta forma el rendimiento mejoraría notablemente.

Código php para encadenar updates en mysql

Ahora bien, muy bonito, la teoría está perfecta, pero ¿Cómo lo hacemos de forma práctica?, por que seamos sinceros, el tener que hacer una consulta infinita y rellenar cientos de campos aunque sea con variables puede ser una locura.

La mejor opción, crea un array y usa este código. Cuando hablo de crear un array me refiero a que utilices la id y el valor que le vas a dar (id o el campo que quieras usar como identificador para localizar cada fila), es decir así:

$array_ids = array(1 = 'nuevonombre 1', 3 = 'nuevonombre 3', 4 = 'nuevonombre 4');

123 $array_ids=array(1='nuevonombre 1',   3='nuevonombre 3',   4='nuevonombre 4');

Una vez tenemos nuestro array con los campos en el formato array($key = $valor) ya podemos parsearlos y convertirlos en una consulta que luego ejecutaremos. La idea sería la siguiente:

PHP $ids = implode(',', array_keys($array_ids)); $sql = "UPDATE discos SET precio = CASE id "; foreach ($aray_ids as $id => $valor) { $sql .= sprintf("WHEN %d THEN %d ", $id, $valor); } $sql .= "END WHERE id IN ($ids)"; echo $sql;

12345678 $ids=implode(',',array_keys($array_ids));$sql="UPDATE discos SET precio = CASE id ";foreach($aray_idsas$id=>$valor){  $sql=sprintf("WHEN %d THEN %d ",$id,$valor);}$sql="END WHERE id IN ($ids)"; echo$sql;

  • Parsearíamos las llaves del array o keys en la variable $ids, para luego utilizarla en el IN().
  • Por otro lado iniciamos la consulta del update en la variable $sql, sobre la que añadiremos el resto.
  • En la línea del foreach vemos como cogeremos cada valor del array y sacaremos la $id y el $valor para usarlos creano la consulta que agregaremos a $sql con .=
  • Por último limitaremos con END y el WHERE para que no se nos salga de las filas que queremos actualizar.

Después de todo esto, dependiendo de qué forma estemos utilizando para ejecutar las consultas tendremos que hacer la query del $sql con query($sql) o $db->query($sql), etc.

Con esto habremos aumentado el rendimiento de nuestra consulta de forma considerable a la vez que reduciremos el tiempo de bloqueo de celdas o tablas, así como la carga de la página se notará mucho más ágil que anteriormente.

¿Haces muchos updates? ¿Cómo lo has solucionado tú?


También podría interesarte :

Volver a la Portada de Logo Paperblog

Quizás te interesen los siguientes artículos :

  • Funciones en MySQL

    Funciones MySQL

    Una función en MySQL es una rutina creada para tomar unos parámetros, procesarlos y retornar en un salida.Se diferencian de los procedimientos en las... Leer el resto

    Por  Jamesrevelo
    TECNOLOGÍA
  • Curso Online Aprende MySQL sin dolor

    Curso Online Aprende MySQL dolor

    MySQL es la base de datos más utilizada en Internet, sin duda alguna. Se estima en más de 6 millones de instalaciones al rededor del mundo. Leer el resto

    Por  Francves
    INFORMÁTICA, TECNOLOGÍA
  • Triggers en MySQL

    Triggers MySQL

    Un Trigger en MySQL es un programa almacenado(stored program), creado para ejecutarse automaticamente cuando ocurra un evento en nuestra base de datos. Leer el resto

    Por  Jamesrevelo
    INFORMÁTICA, TECNOLOGÍA
  • Cómo conectar Php y Mysql?

    Cómo conectar Mysql?

    En este artículo veremos como integrar Php y Mysql para aumentar la funcionalidad de nuestros desarrollos web. Primero veremos como abrir la conexión en el... Leer el resto

    Por  Jamesrevelo
    INFORMÁTICA, TECNOLOGÍA
  • Como hacer biodisel casero

    Como hacer biodisel casero

    El biodiesel es un combustible diésel que se produce mediante la reacción de aceite vegetal (aceite de cocina) con otros productos químicos comunes. Leer el resto

    Por  Chester
    MOTOR, TECNOLOGÍA
  • XAMPP: Descarga un servidor de prueba local, para tus desarrollos en PHP

    XAMPP: Descarga servidor prueba local, para desarrollos

    XAMPP es uno de los entornos más populares de desarrollo con PHP, es una distribución de Apache completamente gratuita y fácil de instalar que contien... Leer el resto

    Por  Geeksroom
    COMUNICACIÓN, INTERNET, REDES SOCIALES, TECNOLOGÍA
  • Enviar mensajes WhatsApp mediante PHP usando WhatsAPI (1 de 2)

    Enviar mensajes WhatsApp mediante usando WhatsAPI

    WhatsApp es un  servicio de mensajera SMS muy popular para los teléfonos inteligentes, pero lamentablemente sólo está disponible para smartphones por  ahora. Leer el resto

    Por  Soloelectronicos
    100% VERDE, CIENCIA, TECNOLOGÍA

Revista