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

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ú?