Update does not work

Asked

Viewed 818 times

2

In my system has a registration page and a page with a table with the information of registered. In this table there is an edit button that links with an equal form of the registration page.

What should happen:

When clicking the save button, the modified fields should be changed in the record.

What’s going on:

When I try to change the record appears the alert "successfully saved" and back to the previous page, as it should. But looking at phpMyAdmin, the record remains unchanged

The connection: 

<?php
    $connection = mysqli_connect("localhost", "root", "", "db_formacao");

    if (mysqli_connect_errno())
    {
       echo "Failed to connect to MySQL: " . mysqli_connect_error();
    }
?>

Editing form:

<?php
    require 'conn.php';
    $queryColaboradores = mysqli_query($connection, "SELECT FORMACAO FROM participantes");
    $turma = filter_input(INPUT_POST, 'TURMA');
    $formacao = filter_input(INPUT_POST, 'FORMACAO');
    $colaborador = filter_input(INPUT_POST, 'COLABORADOR');
    $Realizado = filter_input(INPUT_POST, 'REALIZADO');
    $id = filter_input(INPUT_POST, 'ID');
    var_dump($queryColaboradores);
?>

    <div class="container">
        <div class="row">
            <div class="col-lg-12 text-center">
                <h1 style="
                    margin-top:100px;">Inscrição</h1>
                <p> </p>
                <p class="lead"></p>
                <ul class="list-unstyled">
                    <form id="cadastro" method="post" action="banco/updateEdicao.php" style="
                        text-align: left;
                        margin-top:50px;">
                        <fieldset disabled>
                            <div class="col-lg-12">
                                <div class="form-group" style="
                            text-align: left;">
                                    <label  for="FORMACAO">Formação: </label>
                                    <input  type="text" required class="form-control" id="FORMACAO" name="FORMACAO" value="<?php echo $formacao; ?>">
                                 </div>
                            </div>
                        </fieldset>
                        <fieldset disabled>
                            <div class="col-lg-12">
                                <div class="form-group" method="post" style="
                            text-align: left;">
                                    <label  for="TURMA">Turma: </label>
                                    <input  type="text" required class="form-control" id="TURMA" name="TURMA" value="<?php echo $turma; ?>">
                                 </div>
                            </div>
                        </fieldset>
                        <fieldset disabled>
                            <div class="col-lg-12">
                                <div class="form-group" method="post" style="
                            text-align: left;">
                                    <label  for="TURMA">Colaborador: </label>
                                    <input  type="text" required class="form-control" id="COLABORADOR" name="COLABORADOR" value="<?php echo $colaborador; ?>">
                                 </div>
                            </div>
                        </fieldset>
                        <fieldset disabled>
                            <div class="col-lg-12">
                                <div class="form-group" method="post" style="
                                text-align: left;">
                                    <label  for="TURMA">ID participante: </label>
                                    <input  type="text" required class="form-control" id="PARTICIPANTE" name="PARTICIPANTE" value="<?php echo $id; ?>">
                                    <input type="hidden" name="id" value="<?php echo $id ?>" />
                                </div>
                            </div>
                        </fieldset>
                        <div class="col-lg-12">
                            <fieldset disabled>
                                <div class="form-group">
                                    <label for="previsto">Status</label>
                                    <input type="text" id="PREVISTO" name="PREVISTO" class="form-control" value="Previsto">
                                </div>
                            </fieldset>
                        </div>
                        <div class="col-lg-12">
                            <div class="form-group" style="
                                text-align: left;">
                                <label  for="REALIZADO">Realizado: </label>
                                <input  type="text" required class="form-control" id="REALIZADO" name="REALIZADO" value="Realizado">
                            </div>
                        </div>
                        <div class="">
                            <button type="submit" class="btn btn-primary btn-lg btn-block">Salvar</button>
                        </div>
                    </form>
                </ul>
            </div>
        </div>
    </div>

The update:

<?php

$previsto = filter_input(INPUT_POST, 'PREVISTO');
$realizado = filter_input(INPUT_POST, 'REALIZADO');
$id = filter_input(INPUT_POST, 'ID');

$strcon = mysqli_connect('localhost', 'root', '', 'db_formacao') or die('Erro ao conectar ao banco de dados');
$sql = " UPDATE participantes SET REALIZADO = '$realizado' WHERE ID = '$id' ";
mysqli_query($strcon,$sql) or die("Erro ao tentar atualizar registro. " . mysqli_error($strcon));
mysqli_close($strcon);

echo '<script type="text/javascript">
            alert("Salvo com Sucesso !");
            window.history.go(-1);
        </script>';

var_dump($id)
?>

inserir a descrição da imagem aqui

php mysql sql-update crud

  • This column of the PAID bank and this id are what kind of field? Varchar? If yes missing a single quote

  • The ID is an auto_increment int, whereas the REALIZED is a varchar

  • Now it appears that Alert with the "saved successfully" warning redirects to the previous page as it should be, but when I open phpmyadmin the record has not been changed.

  • Have you tried to echo $sql generated to see if this right and even run it in hand to be sure

  • I even gave a var_dump($forecast, $realized, $id) gave NULL NULL NULL. I gave a no sql too and gave the line of code that sql receives but without the ID

  • 1

    So that’s why. Your Where clause is coming with the empty value. You are saying to your mysql: "Update the DONE column where the id is equal to nothing". As you have no record where the id is equal to nothing it has not changed anything.

  • Yes, but now I’ve introduced the ID, but it’s still not updating. I gave another var_dump($id) and the answer is still NULL.

  • But if you changed in $sql for $ID, you have to change everywhere too, anything for $id you have to change to $ID

  • Not the $id but the ID. 'ID'=$id

Show 4 more comments

3 answers

2

See if this update works! If you look at it well, there are some double-quote and single-quote conflicts, as well as point and comma errors.

To edit only the column with the id you need, the $id you will also have to pick up as POST:

<?php
require 'conn.php';
$queryColaboradores = mysqli_query($connection, "SELECT FORMACAO FROM participantes");
$turma = filter_input(INPUT_POST, 'TURMA');
$formacao = filter_input(INPUT_POST, 'FORMACAO');
$colaborador = filter_input(INPUT_POST, 'COLABORADOR');
$Realizado = filter_input(INPUT_POST, 'REALIZADO');
$id = filter_input(INPUT_POST, 'ID');
var_dump($queryColaboradores);
?>

<div class="container">
    <div class="row">
        <div class="col-lg-12 text-center">
            <h1 style="
                margin-top:100px;">Inscrição</h1>
            <p> </p>
            <p class="lead"></p>
            <ul class="list-unstyled">
                <form id="cadastro" method="post" action="banco/updateEdicao.php" style="
                      text-align: left;
                      margin-top:50px;">
                    <fieldset disabled>
                        <div class="col-lg-12">
                            <div class="form-group" style="
                                 text-align: left;">
                                <label  for="FORMACAO">Formação: </label>
                                <input  type="text" required class="form-control" id="FORMACAO" name="FORMACAO" value="<?php echo $formacao; ?>">
                            </div>
                        </div>
                    </fieldset>
                    <fieldset disabled>
                        <div class="col-lg-12">
                            <div class="form-group" method="post" style="
                                 text-align: left;">
                                <label  for="TURMA">Turma: </label>
                                <input  type="text" required class="form-control" id="TURMA" name="TURMA" value="<?php echo $turma; ?>">
                            </div>
                        </div>
                    </fieldset>
                    <fieldset disabled>
                        <div class="col-lg-12">
                            <div class="form-group" method="post" style="
                                 text-align: left;">
                                <label  for="TURMA">Colaborador: </label>
                                <input  type="text" required class="form-control" id="COLABORADOR" name="COLABORADOR" value="<?php echo $colaborador; ?>">
                            </div>
                        </div>
                    </fieldset>
                    <fieldset disabled>
                        <div class="col-lg-12">
                            <div class="form-group" method="post" style="
                                 text-align: left;">
                                <label  for="TURMA">Id: </label>
                                <input  type="number" required class="form-control" id="ID" name="ID" value="<?php echo $id; ?>">
                            </div>
                        </div>
                    </fieldset>
                    <div class="col-lg-12">
                        <fieldset disabled>
                            <div class="form-group">
                                <label for="previsto">Status</label>
                                <input type="text" id="PREVISTO" name="PREVISTO" class="form-control" value="Previsto">
                            </div>
                        </fieldset>
                        <div class="form-check">
                            <label class="form-check-label">
                                <input class="form-check-input" type="radio" name="REALIZADO" id="REALIZADO" value="REALIZADO" aria-label="...">REALIZADO
                            </label>
                        </div>
                        <div class="">
                            <button type="submit" class="btn btn-primary btn-lg btn-block">Salvar</button>
                        </div>
                    </div>
                </form>
            </ul>
        </div>
    </div>
</div> 


    $previsto = filter_input(INPUT_POST, 'PREVISTO');
    $realizado = filter_input(INPUT_POST, 'REALIZADO');
    $id = filter_input(INPUT_POST, 'ID');

    $strcon = mysqli_connect('localhost', 'root', '', 'db_formacao') or die('Erro ao conectar ao banco de dados');
    $sql = " UPDATE participantes SET REALIZADO = '$realizado' WHERE id = '$id' ";
    mysqli_query($strcon, $sql) or die("Erro ao tentar atualizar registro");
    mysqli_close($strcon);

    echo '<script type="text/javascript">
                alert("Salvo com Sucesso !");
                window.history.go(-1);
            </script>';
    ?>

  • Ah, much cleaner and organized! So, I copied your code but not yet updated the REALIZED column which is the one that should update.

  • Where does that $id variable come from?

  • 1

    Then remove the WHERE id = '$id' and tell me if it works. put this $id variable in my view, it is not being reported anywhere.

  • I removed and the Realized column FINALLY was changed, but the problem is that it was changed from all the records, and I wanted to change only in the record that is being edited.

  • You will have to take this $id per POST as well, just like the PREDICTED and REALIZED, something like: $id = filter_input(INPUT_POST, 'ID'); Ai in this case, will send along with the EXPECTED and REALIZED. I edited it so you’d understand better.

  • So, I just did that, but it’s back to not changing.

  • Yes, put you are not sending the id by POST, you will have to submit in the editing form. As you do with the EXPECTED and ACCOMPLISHED. You’ll also have to send an ID, I edited it once more, see if you can understand.

  • I made the recommended changes, only changed the id to 'ID' but is not changing yet. Do you think it would be better if I made a field in the form to store this ID?

  • Changed id to ID where?

  • In the $sql variable

  • Let’s go continue this discussion in chat.

Show 6 more comments

2

Ever tried to give a var_dump in Query that is rotating and rotating it directly on Phpmyadmin? Usually the Mysql provides more relevant information on the ERROR of Query...

Another tip is to try to create the Query directly on Mysql, (in your case, in the Phpmyadmin), with static data, and once it works, migrate it to the PHP.

1

In your query, UPDATE participantes SET REALIZADO='$realizado' WHERE id='$id', the WHERE clause attempts to use the $id variable, which was not declared before, and is also not being passed by the form. You need to create the ID field in the HTML form and pass it.

<input type="hidden" name="id" value="<?php echo $id ?>" />

Other points to observe:

Error message

This message does not help much, it would be interesting to also log the error, or write even the error directly in the message, but this last case may not be interesting for showing the problem to the end user.

If you have no problem showing, you can change the code to:

mysqli_query($strcon,$sql) or die("Erro ao tentar atualizar registro. " . mysqli_error($strcon));

Use "Prepared statement"

The way it is, your query is not safe, take a look at it. Learn more here: https://www.w3schools.com/php/php_mysql_prepared_statements.asp

Writing in attributes

In the current code, if by chance the employee name is written as João "Mito" da Silva, will break your attributes. Use where write the following attributes:

<label  for="COLABORADOR">Colaborador: </label>
<input  type="text" required class="form-control" id="COLABORADOR" name="COLABORADOR" value="<?php echo htmlentities($colaborador, ENT_QUOTES); ?>" />

Note also that I changed the attribute for of label, for he is the label of that input.

  • Hey, thanks for the answer! I made the field Hyden and he’s pulling the registration ID, but the registration is still not being changed. I’ll edit my question.

  • Let me ask a little extra question. I included Hidden in the ID field and the value of my ID field received the ID. So if I include Hidden in my other fields, the value of my other fields will also receive their information stored in the database?

Browser other questions tagged php mysql sql-update crud

You are not signed in. Login or sign up in order to post.