My script is updating all fields when I just edit one

Question

my problem it's I'm trying to develop a back-end where I need to update but my problem is, when I update one field my script update all fields and all data of my mysqli database. My code for now is:

<html>
<body>
<?php

  ini_set('display_errors', 1);
   error_reporting(~0);

   $serverName = "localhost";
   $userName = "root";
   $userPassword = "";
   $dbName = "hotel_vaniet";

   $strCustomerID = null;

   if(isset($_GET["cod"]))
   {
       $cod = $_GET["cod"];
   }


   $serverName = "localhost";
   $userName = "root";
   $userPassword = "";
   $dbName = "hotel_vaniet";

   $conn = mysqli_connect($serverName,$userName,$userPassword,$dbName);

   $sql = "SELECT * FROM quartos WHERE cod=$cod";

   $query = mysqli_query($conn,$sql);

   $result=mysqli_fetch_array($query,MYSQLI_ASSOC);

?>
<div id="main">
<form action="editar_quartos_final.php" name="frmAdd" method="post">
<br><h1>Página de Edição</h1>
<br><hr/>
<div id="login2">
<table width="284" border="1">
  <tr>
    <th width="120">Tipo</th>
    <td width="238"><input type="text" name="tipo" size="50" value="<?php echo $result["tipo"];?>"></td>
    </tr>
  <tr>
    <th width="120">Capacidade</th>
    <td><input type="text" name="capacidade" size="50" value="<?php echo $result["capacidade"];?>"></td>
    </tr>
  <tr>
    <th width="120">Preço p/ Noite</th>
    <td><input type="text" name="preco" size="50" value="<?php echo $result["preco"];?>"></td>
    </tr>
  <tr>
    <th width="120">Reservado</th>
    <td><input type="text" name="reservado" size="50" value="<?php echo $result["reservado"];?>"></td>
    </tr>
  </table>
  <br><input id="submitbuttoneditar" type="submit" value=" Editar " name="submit"/><br />
  </div>
</form>

<?php
mysqli_close($conn);
?>
</body>
</html>

This the first page ,this page send me to another where makes all changes. The second page :

<html>
<head>
<title>Página de Edição do Cliente</title>
</head>
<body>
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "hotel_vaniet";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

$sql = "UPDATE quartos SET 
            tipo = '".$_POST["tipo"]."' ,
            capacidade = '".$_POST["capacidade"]."' ,
            preco = '".$_POST["preco"]."' ,
            reservado = '".$_POST["reservado"]."'
            WHERE cod=cod";

if ($conn->query($sql) === TRUE) {
    echo "Dados actualizados com sucesso!";
    header("Location: quartos.php");
} else {
    echo "Erro na edição dos dados! " . $conn->error;
    header("Location: quartos.php");
}

$conn->close();
?>
</body>
</html>

`cod=cod` is always true. What record do you want to update exactly? — u_mulder, Jun 20 '16 at 19:52

score 0 · Answer 1 · edited May 23 '17 at 12:08

On your first page you have $cod variable which equals $_GET["cod"]. On your second page $cod variable is not defined. So your try to update

WHERE cod=cod

means - update where value of field cod is the same as value of field cod. And as it is true for all records - all your records are updated.

So, the solution is to pass your $cod value to your second script.

For example, you can do it with a hidden field from your first form:

<form action="editar_quartos_final.php" name="frmAdd" method="post">
<br><h1>Página de Edição</h1>
<br><hr/>
<input type="hidden" name="cod" value="<?php echo $cod?>" />
<div id="login2">
<table width="284" border="1">
  <tr>
    <th width="120">Tipo</th>
    <td width="238"><input type="text" name="tipo" size="50" value="<?php echo $result["tipo"];?>"></td>
    </tr>
  <tr>

See this field with hidden type?

And in your second script use $_POST['cod']:

$sql = "UPDATE quartos SET 
        tipo = '".$_POST["tipo"]."' ,
        capacidade = '".$_POST["capacidade"]."' ,
        preco = '".$_POST["preco"]."' ,
        reservado = '".$_POST["reservado"]."'
        WHERE cod=" . $POST['cod'];

And of course, your code is vulnerable to sql injections. So you should start using prepared statements asap.

I did what you told me to do , but the problem its , when I click on the button to update it does nothing , for example , if I try to change my field "Tipo" doesn't works , keeps with the same data . — Leonel Rodrigues, Jun 20 '16 at 20:11
Echo your `$sql`. Check syntax, run query directly on mysql. — u_mulder, Jun 20 '16 at 20:14
already did it , i'm getting so mad , im trying to solve this problem for almost 3 hours — Leonel Rodrigues, Jun 20 '16 at 20:20

My script is updating all fields when I just edit one

1 Answers1