Delete row from sql table with php issue

Question

Ask the user which row to delete based on the value selected in the select list and the value from a textbox which contains the matching data. For example, selected item = ‘city’ and the textbox = ‘Boston’. Include a button that removes the row from the table.

those are my instructions to follow. I am having two issues, the first issue is if I say.. select teamname and enter a team from the table I get an error saying:

DELETE FROM teams WHERE teamname = rockets

Column not found: 1054 Unknown column 'rockets' in 'where clause'

its trying to use rockets as the column name and not the row that's needs to be deleted. The second issue is if I select yearformed and enter in a correct year, it will delete the row/s with that year. If i enter a year that doesn't exist, my code says records deleted successfully, but nothing is actual deleted.

I think I have got somethings mixed up here, and not sure exactly how it halfway works for yearformed and doesn't for other columns....

html

<form method='post' action='phpfiles/deleteData.php'>
                    Select a column name, then enter which data to delete.
                    <br>
                    <br>
                    <label for='option1'>
                        <select name='selectColumn'>
                            <option value='teamname' id='team'>teamname</option>
                            <option value='city' id='city'>city</option>
                            <option value='bestplayer' id='best'>bestplayer</option>
                            <option value='yearformed' id='year'>year</option>
                            <option value='website' id='website'>website</option>
                        </select>
                    </label>    
                    <label for='option2'>
                        Data to delete: <input type='text' name='dataDelete'>
                    </label>
                    <br><br>
                    <input type='submit' value='Submit Delete'>
                </form>

php

<?php

    if ($_SERVER["REQUEST_METHOD"] == "POST"){ 

        $servername = "localhost";
        $username = "root";
        $password = "enigma";
        $dbname = "program09";
        $columnSelect = $_POST['selectColumn'];
        $deleteData = $_POST['dataDelete'];

        try {
            $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
            // set the PDO error mode to exception
            $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

            // sql to delete a record
            $sql = "DELETE FROM teams WHERE $columnSelect = $deleteData";

            // use exec() because no results are returned
            $conn->exec($sql);
            echo "Record deleted successfully";
            }
        catch(PDOException $e)
            {
            echo $sql . "<br>" . $e->getMessage();
            }

        $conn = null;
    }
?>

Kostas Mitsarakis · Accepted Answer · 2015-11-15T20:12:34.963

0

You need single quotes:

$sql = "DELETE FROM teams WHERE $columnSelect = '$deleteData'";

But it's better to use prepared statements to avoid SQL Injection. Also use a whitelist for your column names.

$sql = "DELETE FROM teams WHERE $columnSelect = :value1";
$stmt = $conn->prepare($sql);
$stmt->execute(array(':value1'=>$deleteData));

edited Nov 15 '15 at 20:12

answered Nov 15 '15 at 20:05

Kostas Mitsarakis

4,772
3
23
37

would you mind explaining in simple terms whats going on within that prepared statement? for instance what does the :value1 represent? I see you assign(may not be the right word) the $deletedata variable to it later, after you put it into an array? thanks – gatsby2748 Nov 15 '15 at 20:14
I think it would be better to have a look here: http://www.phpro.org/tutorials/Introduction-to-PHP-PDO.html – Kostas Mitsarakis Nov 15 '15 at 20:15
sorry about that, I do have one last question, if i enter in the text box a string which doesnt exsist, it still stays record deleted but nothing happens how can i get it to either catch the err message or state an error message? – gatsby2748 Nov 15 '15 at 23:54
if ($stmt->rowCount() > 0) { echo 'Deleted '.$stmt->rowCount().' rows'; } else {echo 'No rows deleted';} – Kostas Mitsarakis Nov 16 '15 at 00:00
awsome thank you very much – gatsby2748 Nov 16 '15 at 00:08

Delete row from sql table with php issue

1 Answers1