Delete or change sql data using php

Question

Delete or change sql data using php

Navigation

#1 by (2 votes)

0

I'm creating a page where some information will be uploaded, and that information will be transferred to a database and displayed on that same page, as follows:

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

But my main question is: how to exclude 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 mysql html sql-update

asked by anonymous 13.05.2017 / 22:08

1 answer

Direct link does not work with wget on ubuntu [closed] Import decimal with comma

score 2 · Accepted Answer

HTML

1 - put as value in 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 name of the checkboxes are in brackets at the end, php will recognize them as an array and therefore you can use

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 OBS: In most cases, you will not need to use this enctype="multipart/form-data" attribute at all. The default value (that is, if you do not use this attribute at all) is "application / x-www-form-urlencoded", which is sufficient for almost any form data type. In case of uploading files, you should use "multipart / form-data"