Update data associated with a specific name in a PHP and SQL database

Asked

Viewed 53 times

0

UPDATE: This question will be long - notice that I am very inexperienced in PHP. I have been (trying) to create a web page that can access a database created in Phpmyadmin in the last few days, this at this time can insert data into the database without problem.

However I found quite a few problems in the update part of the data. I will present everything I have so far and at the end leave images of how I would like the code to proceed.

upgrade.php - First of all the code of the main page, this only has a text box with an associated script for autocomplete (in order to make it easier to choose the name to search for). The purpose of this script is to send the name to the next script.

<!DOCTYPE html>
<html>
    <head>
        <meta charset="UTF-8">
        <link rel="stylesheet" type="text/css" href="mystyle.css">
            <script type="text/javascript" src="jquery-1.4.2.min.js"></script>
            <script type="text/javascript" src="jquery.autocomplete.js"></script>
            <script> 
                jQuery(function(){ 
                $("#search").autocomplete("search.php");
                });
            </script>
    </head>

    <body>
        <form method="get" action="get_form.php">
        Nome a pesquisar: <input type="text" name="q" id="search" placeholder="Escreva um nome">
        <input type="submit" value="Submit"/>
        </form>
    </body>
</html>

From here is where my difficulties begin, these last 2 following scripts.

get_form.php - The goal in this is to create a table with the searched data to allow later editing of the same. The table looks like this. 

    <?php

        $servername = "localhost";
        $username = "root";
        $password = "";
        $dbname = "rhumanos";

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

    //problem variable
    $nome = $_GET['q'];
    // echo $nome;

    $sql = "SELECT * FROM trabalhador WHERE nome like '%".$nome."%' ";
    $result = $conn->query($sql);
?>

    <!doctype html>
    <html>
    <body>
    <h1 align="center">Detalhes</h1>
    <table border="1" align="center" style="line-height:25px;">

        <tr>
        <th>Nome</th>
        <th>Horario</th>
        <th>Salario</th>
        <th>Cargo</th>
        <th>Telefone</th>
        <th>E-mail</th>
        <th>Setor</th>
        <th>Localidade</th>
        </tr>

    <?php
    //Fetch Data form database
    if($result->num_rows > 0){
        while($row = $result->fetch_assoc()){
            ?>
            <tr>
            <td><?php echo $row['nome']; ?></td>
            <td><?php echo $row['horario']; ?></td>
            <td><?php echo $row['salario']; ?></td>
            <td><?php echo $row['cargo']; ?></td>
            <td><?php echo $row['telefone']; ?></td>
            <td><?php echo $row['e_mail']; ?></td>
            <td><?php echo $row['setor']; ?></td>
            <td><?php echo $row['localidade']; ?></td>
            <!--Edit option -->
            <td><a href="edit.php?edit_id=<?php echo $row['nome']; ?>" alt="edit">Editar</a></td>
            </tr>
            <?php
        }
    }
    else
    {
        ?>
        <tr>
        <th colspan="2">Nome nao encontrado</th>
        </tr>
        <?php
    }
    ?>
    </table>
    </body>
    </html>

Edit.php - The last script creates a table for later editing and the data are, supposedly updated but in the end I explain my problem

<?php
//Database Connection
// include 'conn.php';
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "rhumanos";

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

//Get ID from Database
 if($_GET['edit_id']){

    $sql = "SELECT * FROM trabalhador WHERE nome= '".$_GET['edit_id']."'" ;
    $result = mysqli_query($conn, $sql);
    $row = mysqli_fetch_array($result);
}

//Update Information

if(isset($_POST['btn-update'])){

    $nome = $_POST['nome'];
    $cargo = $_POST['cargo'];
    $email = $_POST['email'];
    $localidade = $_POST['localidade'];
    $setor = $_POST['setor'];
    $telefone = $_POST['telefone'];
    $salario = $_POST['salario'];
    $horario = $_POST['horario'];

    $update = "UPDATE trabalhador SET nome='$nome', cargo='$cargo', e_mail='$email', localidade='$localidade', setor='$setor', email='$email', telefone='$telefone', salario='$salario', horario='$horario' 
    WHERE nome=". $_GET['edit_id'];

    $up = mysqli_query($conn, $update);
    if(!isset($sql)) {
        die ("Erro $sql" .mysqli_connect_error());
    }
    else {  
        header("location: get_form.php");
    }
}
?>
<!--Create Edit form -->
<!doctype html>
<html>
    <head>
    <meta charset="UTF-8">
    </head>
<body>
    <form method="post">
    <h1>Editar Informação</h1>
        <label>Nome:</label><input type="text" name="nome" placeholder="Nome" value="<?php echo $row['nome']; ?>"><br/><br/>
        <label>Cargo:</label><input type="text" name="cargo" placeholder="Cargo" value="<?php echo $row['cargo']; ?>"><br/><br/>
        <label>E-mail:</label><input type="text" name="email" placeholder="E-Mail" value="<?php echo $row['e_mail']; ?>"><br/><br/>
        <label>Localidade:</label><input type="text" name="localidade" placeholder="Localidade" value="<?php echo $row['localidade']; ?>"><br/><br/>
        <label>Setor:</label><input type="text" name="setor" placeholder="Setor" value="<?php echo $row['setor']; ?>"><br/><br/>
        <label>Telefone:</label><input type="text" name="telefone" placeholder="Telefone" value="<?php echo $row['telefone']; ?>"><br/><br/>
        <label>Salário:</label><input type="text" name="salario" placeholder="Salário" value="<?php echo $row['salario']; ?>"><br/><br/>    
        <label>Horário:</label><input type="text" name="horario" placeholder="Horário" value="<?php echo $row['horario']; ?>"><br/><br/>            
        <button type="submit" name="btn-update" id="btn-update" onClick="update()"><strong>Update</strong></button>
    <a href="get_form.php"><button type="button" value="button">Cancel</button></a>
</form>

    <!-- Alert for Updating -->
    <script>
    function update(){
        var x;
        if(confirm("Atualizado") == true){
            x= "update";
        }
    }
    </script>

</body>
</html>

In the end i get this mistake.

The variable q is not being set at the end where I confirm the change (update) of the data by what I understand, although it was set at the beginning, which leaves me confused because I do not know how to solve.

This error itself shows all the data, of all users in the database and I just want you to show me what has changed.

Once again I appreciate any help that is made available... this will be a big one if it is arranged.

PS: I know the code is leaving the system vulnerable due to potential SQL Injections but since I am the only one doing this project and fiddling with the database because it is only accessed by Phpmyadmin I am not prioritizing security yet. I am aware of the danger however as I have already been informed on another question that I posted.

php sql phpmyadmin sql-update

  • The problem is that it is not entering this if > if(isset($_GET['q'])) then the variable $row was not defined. And this happens because there is no $_GET['q']

  • The variable q is not being defined occurs because when executing header("location: get_form.php"); on the page edit.php in url parameter is not being sent q

2 answers

0

I think you forgot to define the GET method in your form. It should look like this.

<form method="get" action="get_form.php">

If it still doesn’t work, try checking this line by adding an Else. It turns out that GET['q'] is not enough and the $Row variable is not defined,

if(isset($_GET['q'])){

  • I changed the form as suggested but it did not work, the error still appears. However, the error is in this variable that is not getting the information you want. I put a small condition else { echo 'erro isset'; to confirm.

  • the question has been updated

  • You view the $_GET['q'] variable in the browser?

0

  • Variable q is not being defined

    occurs because while executing header("location: get_form.php"); on the page edit.php in url parameter is not being sent q

  • and I just want you to show me what has changed

To solve both problems:

put two parameters in the Location header url

header("location: get_form.php?up=ok&q=".$_GET['edit_id']);

the parameter up indicates that UPDATE has been done and the parameter value q is from what underwent update.

The page get_form.php

//problem variable
$nome = $_GET['q'];
//echo $nome;

if (isset($_GET['up'])){
    $sql = "SELECT * FROM trabalhador WHERE nome = '$nome'";
}else{
    $sql = "SELECT * FROM trabalhador WHERE nome like '%".$nome."%' ";
}
$result = $conn->query($sql);

NOTE: this syntax

$update = "UPDATE trabalhador SET ......... WHERE nome=".$_GET['edit_id'];

doesn’t seem to work

I suggest this syntax

$get = $_GET['edit_id']; $update = "UPDATE trabalhador SET ......... WHERE nome='$get'";

Browser other questions tagged php sql phpmyadmin sql-update

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