Delete or change sql data using php

Asked

Viewed 995 times

0

I am creating a page where some information will be uploaded, and this information will be transferred to a database and displayed on the same page, as below:captura

As highlighted in blue, I created a list of options that the user can choose x actions and change the displayed data.

But my main question is: how, for example, to delete a row from the database, using the checkbox?

I tried several tutorials, but all without success.

On the page, I used these codes to import, store and display the data:

<?php
if ($_FILES[csv][size] > 0) { 

  //get the csv file 
  $file = $_FILES[csv][tmp_name]; 
  $handle = fopen($file,"r"); 

  //loop through the csv file and insert into database 
  do { 
      if ($data[0]) { 
          mysqli_query($connect, "INSERT INTO `pedidos` (`emissaoPed`, `nPedido`, `pedCliente`, `nomeAbrev`, `vlr`, `status`) VALUES 
              ( 
                  '".addslashes($data[0])."', 
                  '".addslashes($data[1])."', 
                  '".addslashes($data[2])."',
                  '".addslashes($data[3])."',
                  '".addslashes($data[4])."',
                  '".addslashes($data[5])."'
              ) 
          "); 
      } 
  } while ($data = fgetcsv($handle,1000,",","'")); 
} 

?> 

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> 
<html xmlns="http://www.w3.org/1999/xhtml"> 
<head> 
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" /> 
<title>Import a CSV File with PHP & MySQL</title> 
</head> 

<body> 

<?php if (!empty($_GET[success])) { echo "<b>Your file has been imported.</b><br><br>"; } //generic success notice ?> 

  <form action="" method="post" enctype="multipart/form-data" name="form1" id="form1"> 
    Choose your file: <br /> 
    <input name="csv" type="file" id="csv" /> 
    <input type="submit" name="Submit" value="Submit" /> 
  </form> 
  <form action="" method="post" enctype="multipart/form-data" name="form2" id="form2"> 
    <label>Selecione o status:</label>
    <select name="changePed">
      <option value="separacao">Em Separação</option>
      <option value="cancelado">Cancelado</option>
      <option value="faturado">Faturado</option>
      <option value="exp">Expedido</option>
  </select>
    <input type="submit" value="Alterar">
  </form>
<?php
  $result = mysqli_query($connect, "SELECT * FROM `pedidos`");

echo "<table border='1'>
<tr>
<th><input type='checkbox' name='select-all' id='select-all' /></th>
<th>Data de emissão</th>
<th>EMS</th>
<th>Pedido do  cliente</th>
<th>Cliente</th>
<th>Valor do pedido</th>
<th>Status</th>
</tr>";

while($row = mysqli_fetch_array($result))
{
  echo "<tr>";
  echo "<td><input name='checkbox[]' type='checkbox'></td>";
  echo "<td>" . $row['emissaoPed'] . "</td>";
  echo "<td>" . $row['nPedido'] . "</td>";
  echo "<td>" . $row['pedCliente'] . "</td>";
  echo "<td>" . $row['nomeAbrev'] . "</td>";
  echo "<td>" . $row['vlr'] . "</td>";
  echo "<td>" . $row['status'] . "</td>";
  echo "</tr>";
}
echo "</table>";
?>
php html mysql sql-update

  • putting as checkbox value the id or qq unique identifier of that line

  • Does your table have any columns that identify the row as unique? an auto increment id?

  • Hello Leo, thank you for answering? Do you say the table in mysql? Yes, it has an id column with auto increment

1 answer

2


HTML

1 - put as value in the checkbox name='checkbox[]' id as follows

value='. $row['id']

2 - transfer the closing tag </form> from the second form to after the closing tag </table>

<form action="" method="post" enctype="multipart/form-data" name="form1" id="form1"> 
    Choose your file: <br /> 
    <input name="csv" type="file" id="csv" /> 
    <input type="submit" name="Submit" value="Submit" /> 
</form> 
<form action="" method="post" enctype="multipart/form-data" name="form2" id="form2"> 
    <label>Selecione o status:</label>
    <select name="changePed">
      <option value="separacao">Em Separação</option>
      <option value="cancelado">Cancelado</option>
      <option value="faturado">Faturado</option>
      <option value="exp">Expedido</option>
   </select>
   <input type="submit" value="Alterar">

<?php
$result = mysqli_query($connect, "SELECT * FROM `pedidos`");

echo "<table border='1'>
<tr>
<th><input type='checkbox' name='select-all' id='select-all' /></th>
<th>Data de emissão</th>
<th>EMS</th>
<th>Pedido do  cliente</th>
<th>Cliente</th>
<th>Valor do pedido</th>
<th>Status</th>
</tr>";

while($row = mysqli_fetch_array($result))
{
  echo "<tr>";
  echo "<td><input name='checkbox[]' type='checkbox' value=" . $row['id'] . "></td>";
  echo "<td>" . $row['emissaoPed'] . "</td>";
  echo "<td>" . $row['nPedido'] . "</td>";
  echo "<td>" . $row['pedCliente'] . "</td>";
  echo "<td>" . $row['nomeAbrev'] . "</td>";
  echo "<td>" . $row['vlr'] . "</td>";
  echo "<td>" . $row['status'] . "</td>";
  echo "</tr>";
}
echo "</table>";
echo "</form>";
?>

PHP

as the checkbox name is bracketed at the end, php will recognize it as an array and so can be used

if(isset($_POST['checkbox'])){
    $arr = filter( $_POST['checkbox'] );
    $sql = 'DELETE FROM pedidos WHERE id IN('.implode( ',', $arr ).')';
    $result = mysqli_query($connect,$sql);
}
function filter( $dados ){
    $arr = Array();
    foreach( $dados AS $dado ) $arr[] = (int)$dado;
    return $arr;
}

Only one NOTE: In most cases, you will not need to use this attribute enctype="multipart/form-data" in everything. The default value (i.e., if you do not use this attribute at all) is "application/x-www-form-urlencoded" which is sufficient for almost any form data. In case of file upload, you must use "Multipart/form-data"

  • Leo, thanks for the layout again! But php.ini points out an error that I was already falling for. When including chekbox value to id, it points to the following error Parse error: syntax error, Unexpected '' (T_ENCAPSED_AND_WHITESPACE), expecting Identifier (T_STRING) or variable (T_VARIABLE) or number (T_NUM_STRING) in /home/sites/2a/9/98b6f35433/public_html/attpedidos.php on line 103 Line 103 is as follows: echo "<td><input name='checkbox[]' type='checkbox' value='. $Row['id'] . '></td>";

  • corrected that line to not give that error

  • 2

    Leo, a tip: use foreach will perform numerous operations in the database and this can be specific for the application. Alternatively, you can delete all desired records by doing DELETE FROM pedidos WHERE id IN (1, 2, 3, ...). In this case, with PHP, you can use the function $excluir = implode(",", $_POST["checkbox"]) to generate a string in the format 1, 2, 3 and pass in query "DELETE FROM pedidos WHERE id IN ({$excluir})", for example.

  • 2

    And generally use the checkbox in the table header is to increase usability, so that the user can select all the records at once or deselect the ones that are selected. This would be done with Javascript in the case. It does not affect your solution, but I thought it was valid to comment given your tip 1 in the reply.

  • Leo and @Andersoncarloswoss, you saved my project, thanks for the help! : D It worked perfectly. I think from here I can manage ^^

Browser other questions tagged php html mysql sql-update

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