El uso de CASE en mysql y php para actulizar una base de datos

4 de octubre de 2014

Con CASE se pueden actualizar varios registros en una base de datos mysql con un solo query / consulta.

Supongamos que tenemos esta tabla y queremos actualizar todos los registros o más de 2 registros:

matricula_2015
id estado matricula
1 Aguascalientes 10
2 Baja California Norte 11
3 Baja California Sur 12
4 Campeche 13
5 Chiapas 14
6 Chihuahua 15
7 Coahuila 16
8 Colima 17
9 Distrito Federal 18
10 Durango 19
11 Guanajuato 20
12 Guerrero 21
13 Hidalgo 22
14 Jalisco 23
15 México 24
16 Michoacán 25
17 Morelos 26
18 Nayarit 27
19 Nuevo León 28
20 Oaxaca 29
21 Puebla 30
22 Querétaro 31
23 Quintana Roo 32
24 San Luis Potosí 33
25 Sinaloa 34
26 Sonora 35
27 Tabasco 36
28 Tamaulipas 37
29 Tlaxcala 38
30 Veracruz 39
31 Yucatán 40
32 Zacatecas 41

Una solución poco elegante y lenta sería hacer 32 consultas tipo:

UPDATE matricula_2015 SET matricula = 25 WHERE id = 1;

UPDATE matricula_2015 SET matricula = 25 WHERE id = 2;

UPDATE matricula_2015 SET matricula = 25 WHERE id = 3;

...

Una solución más rápida es hacer uso de CASE en nuestra consulta.

Actualizar 5 registros de esta tabla con una consulta usando CASE quedaría así:

UPDATE matricula_2015 SET matricula = CASE id
WHEN 1 THEN 100
WHEN 2 THEN 200
WHEN 3 THEN 300
WHEN 4 THEN 400
WHEN 5 THEN 500
END WHERE id IN (1,2,3,4,5);

UPDATE matricula_2015

define la tabla a modificar, en este ejemplo matricula_2015

SET matricula

define la columna cuyos valores se modificarán, en este ejemplo los datos de la columna matricula se modificarán

= CASE id

inicia el condicional case y define la columna que se utilizará como referencia para hacer el cambio, en este ejemplo la columna de referencia es id

WHEN 1 THEN 100

hace la modificación. En este ejemplo CUANDO (WHEN) id = 1, matricula será igual a 100
(THEN)

WHEN 2 THEN 200

hace la modificación. En este ejemplo CUANDO (WHEN) id = 2, matricula será igual a 200 (THEN)

WHEN 3 THEN 300

hace la modificación. En este ejemplo CUANDO (WHEN) id = 3, matricula será igual a 300 (THEN)

WHEN 4 THEN 400

hace la modificación. En este ejemplo CUANDO (WHEN) id = 4, matricula será igual a 400 (THEN)

WHEN 5 THEN 500

hace la modificación. En este ejemplo CUANDO (WHEN) id = 5, matricula será igual a 500 (THEN)

END
Termina con el condicional case.

WHERE id IN (1,2,3,4,5);

Restringe el cambio a los registros cuyo valor en id sea 1,2,3,4,5.

Tras procesar esa consulta nuestra tabla quedaría así:

matricula_2015
id estado matricula
1 Aguascalientes 100
2 Baja California Norte 200
3 Baja California Sur 300
4 Campeche 400
5 Chiapas 500
6 Chihuahua 15
7 Coahuila 16
8 Colima 17
9 Distrito Federal 18
10 Durango 19
11 Guanajuato 20
12 Guerrero 21
13 Hidalgo 22
14 Jalisco 23
15 México 24
16 Michoacán 25
17 Morelos 26
18 Nayarit 27
19 Nuevo León 28
20 Oaxaca 29
21 Puebla 30
22 Querétaro 31
23 Quintana Roo 32
24 San Luis Potosí 33
25 Sinaloa 34
26 Sonora 35
27 Tabasco 36
28 Tamaulipas 37
29 Tlaxcala 38
30 Veracruz 39
31 Yucatán 40
32 Zacatecas 41

Los primeros 5 registros fueron modificados.

Suponiendo que la consulta la queremos hacer usando php con misqli el resultado final sería:

<?php

$server = 'localhost';
// define el servidor mysql a usar

$user = 'mi_usuario';
// define el usuario para conectarse al servidor mysql
//  Ojo. Cada servidor tiene uno diferente

$pass = 'mi_contrasena';
// define la contraseña para conectarse al servidor mysql
//  Ojo. Cada servidor tiene uno diferente

$db = 'matricula_2015';
// define la base de datos a usar


$mysqli = new mysqli($server, $user, $pass, $db);
// uso mysqli para conectarme al servidor

if ($mysqli->connect_error) 
// si no se puede conectar al servidor, muestra un mensaje de error
{
echo 'No se pudo establecer una conexión.';

exit;
}

$utf8 = $mysqli->query("SET NAMES 'utf8'");
// utiliza el encoding utf8 para mostrar los datos


$mysql_update = "UPDATE matricula_2015 SET matricula = CASE id 
WHEN 1 THEN 100 
WHEN 2 THEN 200
WHEN 3 THEN 300
WHEN 4 THEN 400
WHEN 5 THEN 500
END WHERE id IN (1,2,3,4,5);";
// define nuestra consulta


if($mysqli->query($mysql_update))
// ejecuta la consulta
{
	echo "ok";
	// si la consulta es exitosa, muestra la leyenda ok
}
else
{
	echo ":(";
	// si la consulta falla, muestra una carita triste
}
?>

¿Y si tuviéramos un formulario html con todos los datos? ¿Cómo procesaríamos los datos y construiríamos la misma consulta

Suponiendo que tenemos este formulario hecho con html para actualizar la información:

El código html de este formulario es:

<form id="form1" name="form1" method="post" action="enviar.php">
<table border="0" cellpadding="4" cellspacing="0">
<tr><td><label for="p1">Aguascalientes</label></td><td><input type="text" name="p1" id="1"></td></tr>
<tr><td><label for="p2">Baja California Norte</label></td><td><input type="text" name="p2" id="2"></td></tr>
<tr><td><label for="p3">Baja California Sur</label></td><td><input type="text" name="p3" id="3"></td></tr>
<tr><td><label for="p4">Campeche</label></td><td><input type="text" name="p4" id="4"></td></tr>
<tr><td><label for="p5">Chiapas</label></td><td><input type="text" name="p5" id="5"></td></tr>
<tr><td><label for="p6">Chihuahua</label></td><td><input type="text" name="p6" id="6"></td></tr>
<tr><td><label for="p7">Coahuila</label></td><td><input type="text" name="p7" id="7"></td></tr>
<tr><td><label for="p8">Colima</label></td><td><input type="text" name="p8" id="8"></td></tr>
<tr><td><label for="p9">Distrito Federal</label></td><td><input type="text" name="p9" id="9"></td></tr>
<tr><td><label for="p10">Durango</label></td><td><input type="text" name="p10" id="10"></td></tr>
<tr><td><label for="p11">Guanajuato</label></td><td><input type="text" name="p11" id="11"></td></tr>
<tr><td><label for="p12">Guerrero</label></td><td><input type="text" name="p12" id="12"></td></tr>
<tr><td><label for="p13">Hidalgo</label></td><td><input type="text" name="p13" id="13"></td></tr>
<tr><td><label for="p14">Jalisco</label></td><td><input type="text" name="p14" id="14"></td></tr>
<tr><td><label for="p15">México</label></td><td><input type="text" name="p15" id="15"></td></tr>
<tr><td><label for="p16">Michoacán</label></td><td><input type="text" name="p16" id="16"></td></tr>
<tr><td><label for="p17">Morelos</label></td><td><input type="text" name="p17" id="17"></td></tr>
<tr><td><label for="p18">Nayarit</label></td><td><input type="text" name="p18" id="18"></td></tr>
<tr><td><label for="p19">Nuevo León</label></td><td><input type="text" name="p19" id="19"></td></tr>
<tr><td><label for="p20">Oaxaca</label></td><td><input type="text" name="p20" id="20"></td></tr>
<tr><td><label for="p21">Puebla</label></td><td><input type="text" name="p21" id="21"></td></tr>
<tr><td><label for="p22">Querétaro</label></td><td><input type="text" name="p22" id="22"></td></tr>
<tr><td><label for="p23">Quintana Roo</label></td><td><input type="text" name="p23" id="23"></td></tr>
<tr><td><label for="p24">San Luis Potosí</label></td><td><input type="text" name="p24" id="24"></td></tr>
<tr><td><label for="p25">Sinaloa</label></td><td><input type="text" name="p25" id="25"></td></tr>
<tr><td><label for="p26">Sonora</label></td><td><input type="text" name="p26" id="26"></td></tr>
<tr><td><label for="p27">Tabasco</label></td><td><input type="text" name="p27" id="27"></td></tr>
<tr><td><label for="p28">Tamaulipas</label></td><td><input type="text" name="p28" id="28"></td></tr>
<tr><td><label for="p29">Tlaxcala</label></td><td><input type="text" name="p29" id="29"></td></tr>
<tr><td><label for="p30">Veracruz</label></td><td><input type="text" name="p30" id="30"></td></tr>
<tr><td><label for="p31">Yucatán</label></td><td><input type="text" name="p31" id="31"></td></tr>
<tr><td><label for="p32">Zacatecas</label></td><td><input type="text" name="p32" id="32"></td></tr>
</table>
<input type="submit" value="Enviar..">
</form>

Ese archivo se procesaría con el archivo enviar.php

El contenido de enviar.php sería:


<?php

$server = 'localhost';
// define el servidor mysql a usar

$user = 'mi_usuario';
// define el usuario para conectarse al servidor mysql
//  Ojo. Cada servidor tiene uno diferente

$pass = 'mi_contrasena';
// define la contraseña para conectarse al servidor mysql
//  Ojo. Cada servidor tiene uno diferente

$db = 'matricula_2015';
// define la base de datos a usar


$mysqli = new mysqli($server, $user, $pass, $db);
// uso mysqli para conectarme al servidor

if ($mysqli->connect_error) 
// si no se puede conectar al servidor, muestra un mensaje de error
{
echo 'No se pudo establecer una conexión.';

exit;
}

$utf8 = $mysqli->query("SET NAMES 'utf8'");
// utiliza el encoding utf8 para mostrar los datos


$mysql_update = "UPDATE matricula_2015 SET matricula = CASE id ";
// comenzamos a construir nuestra consulta

$array_con_ids = array();
// creamos un array para almacenar los registros a modificar y utilizarlos en nuestro WHEN id IN

foreach($_POST as $campo => $valor)
// recupera campo por campo de nuestro formulario
// el name del campo se almacena en la variable $campo
// el valor del campo se almacena en la variable $valor
{
	$valor = $valor *1;
	// dado que esperamos recibir números, múltiplicamos por uno para evitar inyecciones de código
	// y si intentan hacer una inyección, el string se convierte en un número inofensivo

	$registro_id = substr($campo, 2);
	// en html el name de un input text no puede empezar con número
	// por eso nuestro campos se llaman p1, p2, p3...
	// en nuestra tabla cada registro se identifica por un id númerico
	// al name de nuestro inputu text le quitamos la letra p y listo: tenemos el id númerico de nuestro registro
	
	$registro_id = $registro_id * 1;
	// dado que esperamos recibir números, múltiplicamos por uno para evitar inyecciones de código
	// y si intentan hacer una inyección, el string se convierte en un número inofensivo

	$mysql_update .= " WHEN " . $registro_id . " THEN " . $valor;
	// concatenamos nuestra consulta y la alimentamos con los valores del formulario
	
	$array_con_ids[]=$registro_id;
	// comenzamos a añadir elementos a nuestro array
}

$ids_con_comas = implode(",", $array_con_ids);
// el array con ids lo convertimos a una string y cada elemento lo separamos con una coma
// el resultado será algo como: 1,2,3,4,5


$mysql_update .= " END WHERE id IN (" . $ids_con_comas . ");";
// terminamos de concatenar  nuestra consulta añadiendo la parte final


if($mysqli->query($mysql_update))
// ejecuta la consulta
{
	echo "ok";
	// si la consulta es exitosa, muestra la leyenda ok
}
else
{
	echo ":(";
	// si la consulta falla, muestra una carita triste
}
?>


Comenta y opina este texto     Lee mi libro de visitas
© 1997-2017 Alain García Gómez
albegago@hotmail.com
@feesgo
El vínculo a este documento es:

Puedes apoyar a este sitio invitándome a comer...

Este sitio utiliza cookies propias y de terceros para mejorar sus servicios. Si continua navegando, entendemos que acepta su uso. Política de Privacidad