PHP delete based on the values selected

Question

I am trying to delete rows in a table based on the values selected. For example, I have a select list and a textbox When you select the row from the select list, and type in city, it should delete that row. I can't get it work properly.

<p>Select which row to <span class="del">DELETE</span> by selecting a Row from the dropdown and verifying by typing in the city.</p>
<br>
<form method="POST" action"<?php echo $_SERVER['PHP_SELF']; ?>">
<select>
  <option value=""></option>
  <option value="teamnameD">Team Name</option>
  <option value="cityD">City</option>
  <option value="bestplayerD">Best Player</option>
  <option value="yearformedD">Year Formed</option>
  <option value="websiteD">Website</option>
</select>
<br><br>
Verify <span class="del">DELETE</span> by Typing in Name of city&nbsp
<input type="text" name="cityD1">
<br><br>
  <input class="styled-button" type="submit" name="selectList" value="Submit"><br><br>
</form>
<?php

if (isset($_POST['selectList'])){
 }

if (isset($_POST['teamnameD'])) {
  teamnameD();
}

function teamnameD() {




$servername = "localhost:3306";
$username = "";
$password = "";
$dbname = "";

  $conn = new mysqli($servername, $username, $password, $dbname);

  if ($conn->connect_error) {
      echo "Cannot Connect to database ";
  }

  $stmt = $conn->prepare ("DELETE FROM Teams WHERE $selectName LIKE    $selectCity");

  $selectName = $_POST['teamnameD'];
  $selectCity = $_POST['cityD1'];
  $stmt->execute();

  if ($conn->query($sql) === TRUE) {
     echo "Record deleted successfully";
  } else {
      echo "Error deleting record: " . $conn->error;
  }

  $conn->close();


}

Answer 1

The code won't enter the function properly.

I think you didn't create a proper cheak for the selected option on the list. You should give the select a name changing

<select>

into

<select name="foo">

then change

if (isset($_POST['teamnameD'])) {
  teamnameD();
}

into

if (isset($_POST['foo']) && $_POST['foo'] == 'teamnameD') {
  teamnameD();
}

You also have problems inside the function.

You are using $selectName and $selectCity before declared. Try to place the declaration

$selectName = $_POST['foo'];    //notice the change here!
$selectCity = $_POST['cityD1'];

to a place before this line, where you used them

$stmt = $conn->prepare ("DELETE FROM Teams WHERE $selectName LIKE    $selectCity");

also, try changing

$stmt = $conn->prepare ("DELETE FROM Teams WHERE $selectName LIKE    $selectCity");

to

$sql = "DELETE FROM Teams WHERE $selectName LIKE    $selectCity";

then delete this line

$stmt->execute();

dont forget that $selectName and $selectCity still have to get thier values before '$sql' does!

Answer 2

There are a few problems. The select didn't have a name so in the below I set it to "fieldName". I changed the check if submitted conditional to check the submit button value.

Then you should bind the parameters to the prepared query and then execute. In the below code I've also added error checking (simple echo and exit on fail).

<p>Select which row to <span class="del">DELETE</span> by selecting a Row from the dropdown and verifying by typing in the city.</p>
<br>
<form method="POST" action"<?php echo $_SERVER['PHP_SELF']; ?>">
<select name="fieldName">
  <option value=""></option>
  <option value="teamnameD">Team Name</option>
  <option value="cityD">City</option>
  <option value="bestplayerD">Best Player</option>
  <option value="yearformedD">Year Formed</option>
  <option value="websiteD">Website</option>
</select>
<br><br>
Verify <span class="del">DELETE</span> by Typing in Name of city&nbsp;
<input type="text" name="cityD1">
<br><br>
  <input class="styled-button" type="submit" name="selectList" value="Submit"><br><br>
</form>
<?php
if (isset($_POST['selectList'])){
    teamnameD();
}
function teamnameD() {
    // check for a selection
    if (empty($_POST['fieldName']) || empty($_POST['cityD1'])) { 
        echo 'Please select a row and city'; 
        exit(); 
    }
    // database config
    $servername = "localhost:3306";
    $username = "";
    $password = "";
    $dbname = "";
    // connect to the database
    $conn = new mysqli($servername, $username, $password, $dbname);
    // check for connection error
    if ($conn->connect_error) {
      echo "Cannot Connect to database ";
      exit();
    }
    // prepare the query
    if (!($stmt = $conn->prepare("DELETE FROM Teams WHERE ? = ?"))) {
        echo "Prepare failed: (" . $conn->errno . ") " . $conn->error;
        exit();
    }
    // bind the parameters
    if (!$stmt->bind_param("ss", $_POST['fieldName'], $_POST['cityD1'])) {
        echo "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;
        exit();
    }
    // execute the query
    if (!$stmt->execute()) {
        echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
        exit();
    } else {
        echo "Record deleted successfully";
    }
    $conn->close();
}
?>

Answer 3

  $stmt = $conn->prepare ("DELETE FROM Teams WHERE $selectName LIKE    $selectCity");

  $selectName = $_POST['teamnameD'];
  $selectCity = $_POST['cityD1'];

should be like this

  $selectName = $_POST['teamnameD'];
  $selectCity = $_POST['cityD1'];
  $stmt = $conn->prepare ("DELETE FROM Teams WHERE $selectName LIKE    $selectCity");