Cant update mysql database with php

Question

I really dont know whats wrong with it, i can generate data with a similar php and another nearly identical without the $categoria = $_POST["CAT"]; and $sql .= "SET catPERS='$categoria' "; (there is some spanish in it, ill translate if you need it)

<?php
// PROCESO PERSONAS UPD (ACTUALIZACION)

// CONECTAR AL SERVIDOR DE BASE DE DATOS
$conex = mysql_connect("localhost","root","");

// CONTROLAR CONEXION
if (!$conex) {
    die("ATENCION!!!.. NO se pudo CONECTAR al SERVIDOR de Bae de Datos");
} // endif

// SELECCIONAR BASE DE DATOS
$selDB = mysql_select_db("database",$conex);

// CONTROLAR SELECCION DE BASE DE DATOS
if (!$selDB) {
    die("ATENCION!!!.. NO se pudo SELECCIONAR Base de Datos");
} // endif

// CAPTURAR DATOS DEL FORMULARIO
$id             = $_POST["ID"];
$nombre         = $_POST["NOM"];
$direccion      = $_POST["DIR"];
$telefono       = $_POST["TEL"];
$departamento   = $_POST["DTO"];
$categoria      = $_POST["CAT"];     

// CREAR SENTENCIA SQL PARA ACTUALIZACION
$sql  = "UPDATE Personas ";
$sql .= "SET nomPERS='$nombre', ";
$sql .= "SET dirPERS='$direccion', ";
$sql .= "SET telPERS='$telefono', ";
$sql .= "SET dtoPERS='$departamento', ";
$sql .= "SET catPERS='$categoria' ";
$sql .= "WHERE idPERS=$id";

// die($sql);

// EJECUTAR SENTENCIA SQL
mysql_query($sql,$conex); 

// CERRAR CONEXION
mysql_close($conex);

// VOLVER AUTOMATICAMENTE AL FORMULARIO DE ACTUALIZACIÓN (REDIRIGIR)
header("Location: productos.html");
?>

Answer 1

It's a bad idea to use mysql_ as it's currently deprecated. Furthermore, your query string is vulnerable to SQL injection. Time to step up your game, Santiago.

$mysqli = new mysqli('localhost', 'user', 'pass', 'database');

if($stmt = $mysqli->prepare("update Personas set nomPERS = ?, dirPERS = ?, telPERS = ?, dtoPERS = ?, catPERS = ?, where idPERS = ?")):
    $stmt->bind_param('sssssi', $_POST['ID'], $_POST['NOM'], $_POST['DIR'], $_POST['TEL'], $_POST['DTO'], $_POST['CAT'], $id);

    if($stmt->execute()):
         $stmt->close();
         header("Location: productos.html");
    endif;
endif;

This is the safe way. It will also resolve issues with your (currently) broken SQL query.

By using prepared statements in mysqli we're avoiding SQL injection that is possible in your current code.

Links

1. MySQLI
2. Prepared Statements
3. Binding Parameters
4. execute()

Answer 2

When updating multiple values in an update query, you only need one SET keyword and separate the other values with a comma.