DELETE FROM users WHERE user_id = $id

Question

I have a script simple PHP+Mysql query:

<table >
  <tr>
    <td ><h2>GERENCIAR  ANUNCIOS </h2></td>
  </tr>
  <tr>
    <td >

<table >
  <tr>
    <th >ID</th>
    <th >Nome</th>
    <th >Email</th>
    <th >Ação</th>

</tr>
<?php

// Aqui você se conecta ao banco
$mysqli = new mysqli('127.0.0.1', 'root', '', 'login');

// Executa uma consulta
$sql = "SELECT `user_id`, `user_name` , `user_email` FROM `users`";
$query = $mysqli->query($sql);
while ($dados = $query->fetch_assoc()) {
    $id        = $dados["user_id"];
    $nome      = $dados["user_name"];
    $email = $dados["user_email"];

    echo "<tr>
    <td > $id </td>
    <td > $nome </td>
    <td > $email </td>
    <td > <a href='excluir.php?user_id=$id'>excluir </a></td>
    </tr>\n";
    }

echo 'Registros encontrados: ' . $query->num_rows . '<br><br>';

?>
</table>
</td>
</tr>
</table>

And a script excluir.php

<?php
    $mysqli = new mysqli('127.0.0.1', 'root', '', 'login'); 
    $id = 'user_id';

    $strSQL = "DELETE FROM users WHERE user_id = $id";
    mysqli_query($strSQL);

    // Fechar conexão com Banco de Dados
    mysql_close();
    header("Location: index.php");
    ?>

I can’t delete records from the database. What I’m doing wrong?

Answer 1

You are trying to delete a record that contains the value 'user_id'. Change your line 2 of excluir.php for: $id = $_GET['user_id'];

Answer 2

In deleting.php in addition to already spoken, missed calling the method correctly, change:

mysqli_query($strSQL);

for

$mysqli->query($strSQL);

mysqli can be used in object-oriented or procedural mode, avoid mixing styles, in the procedural model it is mandatory to pass the connection as the first argument in the functions, see the example of query

To avoid the sql Injection as it was commenting can change your code to that way:

$id = $_GET['user_id'];

$strSQL = "DELETE FROM users WHERE user_id = ?";
$stmt = $mysqli->prepare($sql); //prepara e transforma a string em uma consulta
$stmt->bind_param('i', $id); //informa que será enviado um integer ao banco
if($stmt->execute() === false){ // efetua a operação
   echo $stmt->error;
}

While doing tests on the code comments the redirect lines so it is possible to view the errors.

bind_param - manual

prepare - manual

Answer 3

The problem is in select, you’re not selecting database records, you’re just returning user_id values, user_name , user_email, try taking Select’s simple quotes like this:

 // Executa uma consulta
 $sql = "SELECT user_id, user_name , user_email FROM users";