DeltaSierra DeltaSierra - 9 months ago 25
MySQL Question

Using data in <select> options in MySQL query

I would like to delete a row from an MySQL database. The row that I'd like to delete is displayed in a box, with each being obtained via a loop and SELECT statement.

I've already got the rows in the database being displayed accordingly; however, I'd like a button that once pressed, would delete the selected option from the database.

Here is my current code:

<form action="" method="post">
<label>Patient Name:</label>
<br><br>
<select name="patient" id="patient">
<?php
$conn = new mysqli("localhost", "root", "", "as2");
$result = $conn->query("SELECT patientID, patientName, address FROM patient ORDER BY patientName ASC");
while ($row = $result->fetch_assoc()){

$patientName = $row['patientName'];
$address = $row['address'];

echo "<option value=\"patient\">" .$patientName. ", ".$address."</option>";
}
?>
</select>
<input type="submit" name="delete" value="Delete Record">
</form>


How would I go about making the "Delete Record" button delete the selected option from the database?

Answer Source

first record (patientID) in a string

$patientID = $row['patientID'];

then Add $patientIDto (option value) so it become :

echo "<option value=".$patientID.">" .$patientName. ",  ".$address."</option>";

then add this code After everything (ofc outside the "while" loop) :

<?php  

$selected_patient = $_POST['patient'];

 if( $_SERVER['REQUEST_METHOD'] === 'POST' 
     && isset($_POST['delete']) && isset($_POST['patient']) ) {

  if( !empty($_POST['patient']) ){
   $patient_ID = mysql_real_escape_string($selected_patient);

 if ( $conn->query("DELETE FROM patient WHERE patientID={$patient_ID}") )    
      echo "user has been deleted successfully";
   else 
      echo "Error deleting";

  }

}

?>

now you'r good to go , after click delete button refresh your page and Boom! , the user will Disappears and if you want a real time Action u can use (Ajax)