Problem when updating records in the database

3

The code returns the values that are registered in the database, but at the time of modifying the data and pressing the save button, the changes are not executed.

RecordDelegations.php

<?
 $conect = new mysqli("localhost", "mpadioce_horacio", "MPADT-2018", "mpadioce_MPA");
 $sql = "SELECT * from InformacionGeneral";
 $resultado=mysqli_query($conect,$sql);

 while($fila=mysqli_fetch_assoc ($resultado)){
?>     
      <tr>
        <td><?php echo $fila["Delegacion"]?></td>   
        <td><?php echo $fila["Parroquia"]?></td>
        <td><?php echo $fila["Correo"]?></td>
        <td><?php echo $fila["Tios_Rensponsables"]?></td>
        <td><?php echo $fila["Telefono_TioRes"]?></td>
        <td><?php echo $fila["Asessor_Espiritual"]?></td>
        <td><?php echo $fila["Fecha"]?></td>
        <td><?php echo $fila["Tios_apoyo"]?></td>
        <td><?php echo $fila["Telefono_TioApoy"]?></td>
        <td><a href="actualizarprueba.php?idg=<?php echo $fila["Id_General"]?>"><center><img src='img/edit.png' width="40" height="40" class='img-rounded'></center></td>
        <td><a href="EliminarDelegacion.php?idg=<?php echo $fila["Id_General"]?>"><center><img  src='img/delete.png' width="40" height="40" class='img-rounded' with:10px;/></a></center></td>
      </tr>   
      <?php
 }
 ?>

updatetest.php

<?
 $conect = new mysqli("localhost", "mpadioce_horacio", "MPADT-2018", "mpadioce_MPA");
 $Id_General=$_GET['idg'];
 $sql = "SELECT * from InformacionGeneral WHERE Id_General='$Id_General'";
 $resultado=mysqli_query($conect,$sql);
 $fila=mysqli_fetch_assoc ($resultado);
?>     

<!DOCTYPE html>
<html>
<head>
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>    
  <title>Administrador</title>
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
</head>
<body>

<div class="jumbotron text-center">
  <h1>BIENVENIDO SR. HORACIO DEL VALLE</h1>
  <p>ADMINISTRADOR DEL SITIO</p> 


            <form action="ejecutaactualizar.php" method="POST name="actualizar">
            <input type="hidden" name="Id_General" value="<?php $Id_General?>">
            <h4>Delegacion</h4>  <input type="text" name="delega" value="<?php echo $fila['Delegacion'] ?>"><br>
            <h4>Parroquia</h4>  <input type="text" name="parroquia" value="<?php echo $fila['Parroquia'] ?>"><br>
            <h4>Correo</h4>  <input type="email" name="correo" value="<?php echo $fila['Correo'] ?>"><br>
            <h4>Tios Responsables</h4>  <input type="text"  name="tiosr" value="<?php echo $fila['Tios_Rensponsables'] ?>"><br>
            <h4>Telefono Tio Responsables</h4>  <input type="text"  name="tel0" value="<?php echo $fila['Telefono_TioRes'] ?>"><br>
            <h4>Asesor Espiritual</h4>  <input type="text"  name="asesor" value="<?php echo $fila['Asessor_Espiritual'] ?>"><br>
            <h4>Fecha</h4>  <input type="date"  name="fecha" value="<?php echo $fila['Fecha']?>"><br>
            <h4>Tios de Apoyo</h4>  <input type="text"  name="apoyo" value="<?php echo $fila['Tios_apoyo'] ?>"><br>
            <h4>Telefono Tios Apoyo</h4>  <input type="text" name="tel1" value="<?php echo $fila['Telefono_TioApoy'] ?>"><br>

                <br>
                <input type="submit" value="Guardar" class="btn btn-success btn-primary">
            </form>


</body>
</html>

runupdate.php

<?php
    $conect = new mysqli("localhost", "mpadioce_horacio", "MPADT-2018", "mpadioce_MPA");

    $IDGENERAL=$_POST['Id_General'];
    $DELEGACION=$_POST['delega'];
    $PARROQUIA =$_POST['parroquia'];
    $CORREO =$_POST['correo'];
    $TIOSRESPONSABLES =$_POST['tiosr'];
    $TELEFONOTIO =$_POST['tel0'];
    $ASESOR =$_POST['asesor'];
    $FECHA =$_POST['fecha'];
    $TIOSDEAPOYO =$_POST['apoyo'];
    $TELEFONOTIOSAPOYO =$_POST['tel1'];

    $sql="UPDATE InformacionGeneral SET Delegacion='$DELEGACION',Parroquia ='$PARROQUIA',Correo='$CORREO',Tios_Rensponsables='$TIOSRESPONSABLES',Telefono_TioRes='$TELEFONOTIO',Asessor_Espiritual='$ASESOR',Fecha='$FECHA',Tios_apoyo='$TIOSDEAPOYO',Telefono_TioApoy='$TELEFONOTIOSAPOYO' WHERE Id_General='$IDGENERAL'";
    $resultado=mysqli_query($conect,$sql);  

    if ($resultado==null) {
        echo "Error de procesamieno no se han actuaizado los datos";
                    echo '<script>alert("ERROR EN PROCESAMIENTO NO SE ACTUALIZARON LOS DATOS")</script> ';
                    header("location: RegistroDelegaciones.php");

                    echo "<script>location.href='RegistroDelegaciones.php'</script>";
                }else {
                    echo '<script>alert("REGISTRO ACTUALIZADO")</script> ';

                    echo "<script>location.href='RegistroDelegaciones.php'</script>";
                }
            ?>
    
asked by Jose Luis Santiago Pacheco 06.09.2018 в 10:27
source

2 answers

2

A part of the quote that you lacked in the attribute method of the form ( <form action="ejecutaactualizar.php" method="POST" name="actualizar"> , reported by @Dubas in this answer ), I add the following:

The most serious problem I find in your code is that you do not escape the strings correctly before adding them to SQL, so a simple quote could cause the syntax of your SQL to be incorrect and, worse, may suffer the < a href="https://www.youtube.com/watch?v=EpKIhF7naSY"> serious security problems associated with SQL injection .

To solve this we can use prepared queries (see next point) or use mysqli::real_escape_string() to escape previously the content of the variable as follows:

<?php
/* Establecemos la conexión */
$conect = new mysqli(
  "localhost",
  "mpadioce_horacio",
  "MPADT-2018",
  "mpadioce_MPA"
);

/* Escapamos las cadenas correctamente antes de agregarlas al SQL: */
$IDGENERAL = $conect->real_escape_string($_POST['Id_General']);
$DELEGACION = $conect->real_escape_string($_POST['delega']);
$PARROQUIA = $conect->real_escape_string($_POST['parroquia']);
$CORREO = $conect->real_escape_string($_POST['correo']);
$TIOSRESPONSABLES = $conect->real_escape_string($_POST['tiosr']);
$TELEFONOTIO = $conect->real_escape_string($_POST['tel0']);
$ASESOR = $conect->real_escape_string($_POST['asesor']);
$FECHA = $conect->real_escape_string($_POST['fecha']);
$TIOSDEAPOYO = $conect->real_escape_string($_POST['apoyo']);
$TELEFONOTIOSAPOYO = $conect->real_escape_string($_POST['tel1']);

/* Ahora pueden concatearse al SQL de manera segura: */
$sql = "
    UPDATE InformacionGeneral
    SET
        Delegacion = '$DELEGACION',
        Parroquia = '$PARROQUIA',
        Correo = '$CORREO',
        Tios_Rensponsables = '$TIOSRESPONSABLES',
        Telefono_TioRes = '$TELEFONOTIO',
        Asessor_Espiritual = '$ASESOR',
        Fecha = '$FECHA',
        Tios_apoyo = '$TIOSDEAPOYO',
        Telefono_TioApoy = '$TELEFONOTIOSAPOYO'
    WHERE Id_General = '$IDGENERAL'
";
/* Convierto tu llamada a mysqli_query a OOP */
$resultado = $conect->query($sql);

/* Si $resultado vale null hubo un problema con la consulta (sintaxis, etc) */
if ($resultado === null) {
    /* Las cabeceras deben ser lo primero en ser enviadas, después
        va el contenido. Aún así para depurar un error SQL lo mejor
        es no forzar la redirección aún para ver el error con tranquilidad */
    /*header("location: RegistroDelegaciones.php");*/
    echo "Error de procesamiento no se han actualizado los datos:";
    /* Toda salida de texto al navegador debe pasar por htmlspecialchars() */
    echo '<pre>', htmlspecialchars($conect->error), '<pre>', PHP_EOL;
    /*echo '<script>alert("ERROR EN PROCESAMIENTO NO SE ACTUALIZARON LOS DATOS")</script> ';
    echo "<script>location.href='RegistroDelegaciones.php'</script>";*/
} else {
    echo '<script>alert("REGISTRO ACTUALIZADO")</script> ';
    echo "<script>location.href='RegistroDelegaciones.php'</script>";
}

I have slightly changed the code when an error is detected with the query so that it does not go to the next page. With this modification you can read the SQL error message with ease to debug it.

Note the use of htmlspecialchars() to also escape the character strings that will be sent to the browser and urlencode() for strings that are added to a URL. This is especially important in the following PHP script to protect you from attacks XSS :

<?php
$conect = new mysqli(
    "localhost",
    "mpadioce_horacio",
    "MPADT-2018",
    "mpadioce_MPA"
);
$sql = "SELECT * FROM InformacionGeneral";
$resultado = $conect->query($sql);
if ($resultado === null) {
    die('ERROR SQL:<pre>' . htmlspecialchars($conect->error) . '<pre>');
while($fila = mysqli_fetch_assoc($resultado)) { ?>
      <tr>
        <td><?= htmlspecialchars($fila["Delegacion"]) ?></td>   
        <td><?= htmlspecialchars($fila["Parroquia"]) ?></td>
        <td><?= htmlspecialchars($fila["Correo"]) ?></td>
        <td><?= htmlspecialchars($fila["Tios_Rensponsables"]) ?></td>
        <td><?= htmlspecialchars($fila["Telefono_TioRes"]) ?></td>
        <td><?= htmlspecialchars($fila["Asessor_Espiritual"]) ?></td>
        <td><?= htmlspecialchars($fila["Fecha"]) ?></td>
        <td><?= htmlspecialchars($fila["Tios_apoyo"]) ?></td>
        <td><?= htmlspecialchars($fila["Telefono_TioApoy"]) ?></td>
        <td><a href="actualizarprueba.php?idg=<?= htmlspecialchars(urlencode($fila["Id_General"])) ?>"><center><img src='img/edit.png' width="40" height="40" class='img-rounded'></center></td>
        <td><a href="EliminarDelegacion.php?idg=<?= htmlspecialchars(urlencode($fila["Id_General"])) ?>"><center><img  src='img/delete.png' width="40" height="40" class='img-rounded' with:10px;/></a></center></td>
      </tr>   
<?php } ?>

NOTE: I remind you that the PHP opening tag is <?php and not <? . The latter is only available if you activate the option short_open_tag in php.ini , so you could have problems with a large number of servers that do not have or do not allow you to enable it. The <?= tag is always available since PHP 5.4.

Finally, the form itself:

<?php
$conect = new mysqli(
    "localhost",
    "mpadioce_horacio",
    "MPADT-2018",
    "mpadioce_MPA"
);
/* Si no se recibe el idg por GET terminamos la ejecución */
if (empty($_GET['idg'])) {
    die('Debe proporcionar un "idg"');
}
/* No es necesario ni recomendable usar variables intermedias */
/*$Id_General = $_GET['idg'];*(
$sql = 'SELECT * from InformacionGeneral WHERE Id_General = ?';
/* Preparamos la consulta */
$consulta = $conect->prepare($sql);
/* Asignamos al primer ? el valor de idg pasado por GET */
$consulta->bind_param('s', $_GET['idg']);
/* Obtenemos los resultados */
$resultado = $consulta->get_result();
/* Ahora podemos obtener el registro */
$fila = $resultado->fetch_assoc();
/* Si devuelve false es porque no se encontró el registro */
if ($fila === false) {
    die('No se encontró el registro solicitado');
}
?><!DOCTYPE html>
<html>
<head>
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
  <title>Administrador</title>
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
</head>
<body>

<div class="jumbotron text-center">
  <h1>BIENVENIDO SR. HORACIO DEL VALLE</h1>
  <p>ADMINISTRADOR DEL SITIO</p>
  <!-- Que no se te olvide la comilla tras el post -->
  <form action="ejecutaactualizar.php" method="POST" name="actualizar">
    <input type="hidden" name="Id_General" value="<?= htmlspecialchars($fila['Id_General']) ?>">
    <h4>Delegacion</h4>
    <input type="text" name="delega" value="<?= htmlspecialchars($fila['Delegacion']) ?>"><br>
    <h4>Parroquia</h4>
    <input type="text" name="parroquia" value="<?= htmlspecialchars($fila['Parroquia']) ?>"><br>
    <h4>Correo</h4>
    <input type="email" name="correo" value="<?= htmlspecialchars($fila['Correo']) ?>"><br>
    <h4>Tios Responsables</h4>
    <input type="text" name="tiosr" value="<?= htmlspecialchars($fila['Tios_Rensponsables']) ?>"><br>
    <h4>Telefono Tio Responsables</h4>
    <input type="text" name="tel0" value="<?= htmlspecialchars($fila['Telefono_TioRes']) ?>"><br>
    <h4>Asesor Espiritual</h4>
    <input type="text" name="asesor" value="<?= htmlspecialchars($fila['Asessor_Espiritual']) ?>"><br>
    <h4>Fecha</h4>
    <input type="date" name="fecha" value="<?= htmlspecialchars($fila['Fecha']) ?>"><br>
    <h4>Tios de Apoyo</h4>
    <input type="text" name="apoyo" value="<?= htmlspecialchars($fila['Tios_apoyo']) ?>"><br>
    <h4>Telefono Tios Apoyo</h4>
    <input type="text" name="tel1" value="<?= htmlspecialchars($fila['Telefono_TioApoy']) ?>"><br>
    <br>
    <input type="submit" value="Guardar" class="btn btn-success btn-primary">
  </form>
</div>
</body>
</html>
    
answered by 06.09.2018 / 18:59
source
2

At a glance on your page "updatetest.php" is incorrect

   <form action="ejecutaactualizar.php" method="POST name="actualizar">

A double quote is missing after POST so the POST action would not be running and therefore on the next page the values of $_POST are not being obtained.

It should be

   <form action="ejecutaactualizar.php" method="POST" name="actualizar">

On the other hand, you should improve security to avoid injections of code. At a minimum, use the function mysql_real_escape_string to not use the POST input data as is. (But this is another battle you should think about to rearrange all of your code)

The problem could also be derived from the server configuration. If AutoCommit mode is not activated, you should perform a commit of the modification operations. Likewise control errors with rollback functions. (This is executed on the connection resource)

Example (very simple):

$connection = mysql_connect(DB_SERVER, DB_USER, DB_PASS) or die(mysql_error());

// operaciones SQL

mysql_query("COMMIT", $connection);

Also if you use the MySQLI driver as an object (PH5 / 7) you have the method of commit and rollback

    
answered by 06.09.2018 в 12:41