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:
UPDATE tabla SET campo = 2 WHERE id = 1; UPDATE tabla SET campo = 67 WHERE id = 2; UPDATE tabla SET campo = 143 WHERE id = 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:
; UPDATE users SET admin = 1 WHERE username = '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 + ++++++++++++++++++++++++++++++
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:
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)
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:
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)
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');
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:
$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;
- 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ú?
Ver comentarios (33)
Hola Vic, a ver si puedes ayudarme. Tengo una consulta que me da un error al utilizar EXITS, no sé que estoy haciendo mal o que otra opción tengo para poder hacerla.
El error es el siguiente : Símbolo EXISTS no válido. Símbolos válidos:
La consulta es :
SELECT (case when SUBSTRING(RB3REE, 1, 1) = 'U' and
SUBSTRING(RB3REE, 8, 1) = '5' and
EXISTS (SELECT * FROM RHDAT.PFCRERB7 WHERE
Rb3emp = rb7emp and rb3prv = rb7prv and rb3cnu = rb7cnu and
RB3CRE = RB7CRE AND RB3CPR = RB7CPR AND
Rb3ree = rb7ree and rb3liq = rb7liq) THEN 3
else 0
END) AS CU5
FROM rhdat.pfcrerb3 A, rhdat.pfcrerb2 B
WHERE RB2FCO = 201704
AND RB2LIQ = 'L03'
AND substring(rb3ree, 1, 1) in ('C', 'U')
AND SUBSTRING(RB3REE, 8, 1) 'O'
AND RB3PDE = RB2PDE AND RB3EMP = RB2EMP AND RB3cnu = RB2cnu
AND RB3CRE = RB2CRE AND RB3CPR = RB2CPR
AND RB3PRV = RB2PRV AND RB3REE = RB2REE AND RB3LIQ = RB2LIQ
La he reducido, pues es muy grande, y no puedo sacar el exists fuera del case, por distintas casuisticas que hay.
Gracias Vic
Hmmm, podrías poner en dropbox un volcado de las tablas que necesitas consultar con algunos datos en un sql y pasármelo en un enlace de dropbox o drive? También me puedes poner exactamente qué es lo que quieres hacer por que al ser solo un select puede que haya una forma más limpia de hacerlo.
Hola. Una pregunta. Cómo puedo rellenar 2 campos de una tabla, sólo cuando están vacíos, con 2 campos de otra tabla? Son campos de ubicación, Latitud y longitud. Así que o ambos están vacíos o ambos llenos.
Gracias
Sería simplemente hacer una consulta que compruebe los dos campos y actualizar con un join, algo así:
UPDATE dataB
INNER JOIN dataA ON dataB.id = tableA.id
SET dataB.lat = dataA.lat, dataB.lon = dataA.lon
WHERE dataB.lat IS NULL AND dataB.lon IS NULL
Si quieres comprobar solo uno de los campos haces dos consultas separadas, una para latitud y otra para longitud, ya que en este caso los dos tienen que ser nulos.
Hola de nuevo Vic.
Tengo otra duda, como puedo obtener el la última letra de un campo alfanumerico sin tener en cuenta los espacios en blanco? Es un campo que mide 350 posiciones y necesito saber la útima letra.
Hay alguna instrucción que me lo muestre?
Perdona, se me ha ido la olla, acabo de caer que te lo he dicho al revés, el trim tienes que hacerlo primero:
substr(trim("mi cadena con espacios al final "), -1);
Hola Vic, La instrucción trim no la conocia, me será de gran ayuda.
Estoy intentado lo que comentas, pero me muestra toda la cadena de caracteres, en lugar de la última letra, creo que el problema es el -1, pues poniendolo positivo me muestra lo mismo y sé lo que ocupa el campo pero no sé donde termina la última letra del campo.
select substr(trim(rb9der), -1)
Vale, perdona, había entendido que lo querías hacer con php y no directamente en el select de la consulta sql.
Para cualquier otra cadena que tengas una vez ya en una variable sí que te va a funcionar el método que te digo, pero claro, es php no sql.
Para mysql las consultas son TRIM() y SUBSTRING(), por lo que esto debería funcionarte:
SUBSTRING(TRIM(´rb9der´), -1)
Te recomiendo utilizar siempre las comillas ´´ para encerrar las tablas y así evitas problemas al usar algún nombre de función en mysql.
Dime si te funciona.
Me daba problemas en las comillas y sin comillas, al final conseguí solucionarlo de esta manera RIGHT(TRIM(rb9der), 1)
Gracias Vic.
A tí por la info de la solución, si necesitas algo más ya sabes.
Sí, para eso puedes hacer
trim(substr("mi cadena con espacios al final ", -1));
El trim quita los espacios al principio y final, y el substr coge las letras que le digas, en negativo empieza por el final.
Hola vic.
tengo una duda sobre una UPDATE, que estoy haciendo, y no sé como puedo resolverla,
Tengo que actualizar un campo de una tabla, pero me puede venir por la condicion dos registros, pues necesitaría concatenar el resultado del campo de estos dos registros en el campo CREAVI.
Ahora mismo lo único que estoy haciendo es machacar y quedarme con el último registro.
¿Cómo puedo hacerlo? Gracias por adelantado.
update rhdat.pfcred00 a set creavi = (select
a.RB3ERR || ' ' || a.RB3DER
from rhdat.pfcrerb3 a, rhdat.pfcrerb2 b, rhdat.pfcred00 c
where rb3pde = 201611 and
rb3err NOT IN ('R9566','R9529','R9670','R9998') and
rb3liq = 'L00' and
substring(rb3ree, 1, 1) not in ('C', 'U') and
rb3ree = rb2ree and rb3emp = rb2emp and
rb3prv = rb2prv and rb3nop = rb2nop and
rb3pde = rb2pde and rb3pha = rb2pha and
rb3liq = rb2liq and
SUBSTRING(rb3pha, 1, 4) = creano and
SUBSTRING(rb3pha, 5, 2) = cremes and
rb3emp = creemp and rb3prv = crecpr and
rb3liq = creliq and rb3nop = crenop and
rb2emp = creemp and rb2prv = crecpr and
rb2liq = creliq and rb2nop = crenop and rb2cnu = crecnu)
Para juntar varios registros en MySQL tienes la función CONCAT(). De todas formas, y teniendo en cuenta la estructura de la consulta yo optaría por revisar de ejecutar el select por separado, de forma que luego sea más versátil a la hora de hacerle cambios. Te digo esto por que supongo que es en el panel de administración, y como esas consultas se ejecutarán solo cuando modifiques algo no debería suponer una diferencia de carga para el servidor.
Gracias Vic, lo voy a intentar. Un saludo
Si tienes algún problema en el proceso dímelo y miramos de solucionarlo.
Hola VicHaunter,
tengo un problema parecido, es el siguiente:
mysql_query("
SELECT pr.nombre_pregunta, re.valor_respuesta
FROM alumno al, respuesta re, pregunta pr, evaluacion ev
WHERE al.IDalumno = re.IDalumno
AND re.IDevaluacion = ev.IDevaluacion
AND pr.IDevaluacion = ev.IDevaluacion
AND al.IDalumno = '$IDalumno'
AND ev.IDevaluacion = '$IDevaluacion'
GROUP BY re.IDrespuesta",
uso un while($row = mysql_fetch_array($pegar)){$nuevo_array[] = $row[1]; }, para obtener los datos de re.valor_respuesta en row[1], cuando lo visualizo con un
foreach ($nuevo_array as $nuevo_valor)
{ echo "esto es nuevo valor(actualizar): ".$nuevo_valor.""; }
todo OK, puedo ver que mi variable: $nuevo_array tiene los valores de $row[1], pero cuando lo envío con un form a través de un submit, solamente puedo visualizar el siguiente mensaje:
$array_nuevo = array();
$array_nuevo[] = $_POST['nuevo_valor'];
esto es nuevo valor(array_nuevo) linea 1: Array. Mi pregunta es como puedo recibir de forma completa los valores del array y podemos utilizarlos usando UPDATE?
Espero tu apoyo. Gracias.
Si lo que quieres es enviar un array en un campo de un formulario lo mejor será que lo codifiques con json y luego lo descodifiques, json_encode para enviar y json_decode para volver a convertirlo en un array.
Gracias VicHaunter,
encontré la solución, tenía que modificar el "name" con valor de array (solamente lo tenía como name="respuesta") en el envío del input:
<input type="hidden" name="IDrespuesta[]" id="IDrespuesta" value="" />
<input type="text" name="respuesta[]" value="" /> dentro del while, y al recibirlo usar un foreach para poder utilizarlo en un UPDATE:
$respuesta = $_POST['respuesta'];
$IDrespuesta = $_POST['IDrespuesta'];
$link=Conectarse();
foreach(array_combine($_POST['respuesta'],$_POST['IDrespuesta']) as $value => $id)
{
$sql = "UPDATE respuesta
SET valor_respuesta = '$value'
WHERE IDrespuesta = '$id'";
$result_up = mysql_query($sql); echo mysql_error()."";
}
mysql_close($link);
Espero le sirva mi solución a otras personas.
Saludos.
Gracias por el aporte!
Buenas noches, tengo una duda, cómo le hago cuando tengo una llave primaria compuesta, he intentado agregando AND pero me actualiza todos los campos con el mismo valor del primero
Hola, gracias por el tutorial :) tengo cierto problema que lleva varios dias comiendo la cabeza, estoy creando una tienda online para un cliente que trabaja directo con el stock de su proveedor, me han pasado un URL basado en arrays con ID de cada producto y su disponible stock en sus almacenes que seria aIgo asi "AACC.010";"0";"1" ID producto almacen Madrid unidades aImacen BarceIona unidades. Necesito enlazar una consulta a ese url para que me actualize el stock de la base de datos con dichos campos. agradezco la respuesta
Supongo que en la base de datos de tu cliente los productos se han guardado con los mismos ids que tiene el mayorista. En ese caso sería tan simple como hacer un foreach al array que tiene cada producto, y ejecutar un update del stock para cada id. Si quieres codifica una porción del array en json y le echo un ojo para hacer una consulta, o puedes pegarlo en pastebin o algún sitio similar.
Hola Vic, Efectivamente los ID de los productos son los mismos lo que busco es exactamente lo que dices ej, dar una orden que cada 20min se haga un foreach ala URL y que las variables se actualizen en mi base de datos, me he hecho una idea de como funcionaria, pero ni se por donde empezar, ej: sabria que tiene que comparar el ID de porducto, y que las variables son el Stock, pero no sabria decir le donde sobreescribir esos valores
jeje, estás trabajando con mysql supongo, mira, como no me has dejado el array me lo invento y te pongo el ejemplo:
$lista = array(0 => array('id' => '333', 'almacen1' => 4, 'almacen2' => 76), 1 => array('id' => '222', 'almacen1' => 23, 'almacen2' => 44));
foreach ($lista as $producto){
$sql = "UPDATE tabla SET `columna_almacen_1` = ".$producto['almacen1']." `columna_almacen_2` = ".$producto['almacen2']." WHERE `col_id` = ".$producto['id'];
//$db->query($sql); //descomenta esto en caso de que estés conectando con mysqli y la variable de conexión a la base de datos sea $db
}
Por supuesto va a depender del formato de tu array, pero si es multidimensional como este debería funcionarte. Lo que hace es recorrer el array y generar una consulta de actualización para cada producto. Debería ser una consulta rápida aunque si la haces cada 20 minutos, hay muchos productos y el servidor no es muy potente te la página irá lenta por el bloqueo de tablas.
http://www.leaflife.com/stock/naturalroots.php?key=Mjs8ShyStcW este es el array, por suerte mi cIiente no trabajara con todos Ios productos
Vaaale, lo que pasa es que estás trabajando con datos en CSV no un array como comentabas, es un producto por línea y tienes una función para recoger estos datos con php.
Para que te quede un array como el que te he puesto arriba bastará con formatear los datos del csv así:
$file = fopen('http://www.leaflife.com/stock/naturalroots.php?key=Mjs8ShyStcW', 'r');
$productos = array();
while (($line = fgetcsv($file,";",'"')) !== FALSE) {
//$line is an array of the csv elements
//var_export($line);
$productos[] = $line;
}
fclose($file);
var_export( $productos );
He puesto un var_export para que veas el contenido, pero cambiando en el foreach $lista por $productos debería funcionarte.
Lo único es que tienes que cambiar id, almacen1 y almacen2, por 0, 1 y 2
cometi un error te envie otro URL este es el original: http://www.leaflife.com/stock/stock.php?key=Mjs8ShyStcW, me estas ayudando mucho ya estoy entendiendo me mejor con el tema
Simplemente cambia la línea del while por esta:
while (($line = fgetcsv($file,0 , "t")) !== FALSE) {
Recuerda que para consultas que no tengan que ver con cualquier artículo también te puedo echar una mano en el foro, hay un subforo de php, por si necesitas ayuda más adelante ;)
Hola de nuevo, llevo varios intentos desde esta mañana y por suerte tengo copias de seguridad ya que casi me cargo todo el trabajo, tengo solo un espacio de stock en DB asi que de ahi un conflicto que no se si se puede hacer una suma entre valor de almacen 1 y 2 y el resultado dejar lo en _stock y el segundo justo lo que dices la pagina se relentizo muchisimo desde que llegue a 1000 productos pero esto lo solucionare con cambiar de plan o hosting
Para hacer una suma es tan simple como meter el valor en una variable, por ejemplo:
$total = $line[1]+$line[2];
Si metes eso dentro del while podrás usarlo directamente en la base de datos.
Un consejo, haz siempre un dump de lo que vas a hacer antes de ejecutarlo contra la base de datos para ver si está todo correcto, no te confíes con estas cosas ya que si no te haces copia puedes liarla.
No no lo conigo algo hago mal, soy demasiado nuevo en eso.
Creo que todo Io escribo mal
Mira, haz una cosa, abre un hilo en el foro y vamos a ver si te puedo echar un cable más a fondo.
http://www.vichaunter.org/foro/ver/ayuda/ayuda-programacion/php/
Necesitaría que me indiques en el mensaje inicial del foro: Url de productos y nombre de la tabla en la base de datos así como de las columnas de la misma, para así tratar de hacerte la consulta correcta.
Además si pones cómo estás conectando a la base de datos mejor, para adaptar la consulta directamente a lo que necesitas.
Listo, abri un hilo, gracias de nuevo