delete row

[webchambers]

Hi guys i am trying to delete a row in my database

the view.php code is

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

// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}

$sql = "SELECT DATE, VENUE, PRICE FROM gigs ORDER BY DATE ASC";

$result = $conn->query($sql);

if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {

echo DATE("d F Y", strtotime($row['DATE']));

echo "<BR>";

echo "" .$row["VENUE"]. "<br>";

echo "" .$row["PRICE"]. "<br><br>";

echo "<a href=\"delete.php?id=$res[id]\">Delete</a> <br><br>";

}

}

else {
echo "No Messages";
}
$conn->close();
?>

the delete.php code is

// get id to delete
$id = $_GET['id'];

// connect to mysql
$connect = mysqli_connect($hostname, $username, $password, $databaseName);

$id = $_GET['id'];
mysqli_query("DELETE FROM gigs WHERE id = id");

$result = mysqli_query($connect, $query);

if($result)
{
    echo 'Data Deleted';
}else{
    echo 'Data Not Deleted';
}
mysqli_close($connect);

just returns Data Not Deleted

[NogDog]

You used "id" in the SQL, where I presume you want the variable "$id". I'd suggest you sanitize it first, though:

$id = intval($_GET['id']);
mysqli_query("DELETE FROM gigs WHERE id = $id");

(this assumes id is an integer?)

[Weedpacket]

You might also want to be a bit consistent in your programming style - use the procedural MySQLi interface or the object-oriented one. Jumping back and forth between them is a good way to get confused. (You forgot, for example, that MySQLi can report error messages when something like a DELETE fails.)

[NogDog]

Heh...I wasn't paying close enough attention -- just saw the issue with the variable without noticing the two different calls to mysqli_query(). That last section could instead be as follows, adding Weedpacket's suggestion to do some error reporting or such:

$connect = mysqli_connect($hostname, $username, $password, $databaseName);

$id = intval($_GET['id']);
$result = mysqli_query($connect, "DELETE FROM gigs WHERE id = $id");

if($result)
{
    // since the query can succeed with no rows being deleted (no matching id)
    echo mysqli_affected_rows($connect) . ' rows deleted.';
}else{
    echo 'Delete query failed, error logged.';
    error_log(mysqli_error($connect));
}
mysqli_close($connect);

[pbismad]

You need to set php's error_reporting to E_ALL and display_errors to ON, preferably in the php.ini on your system, so that php will help you by reporting and displaying all the errors it detects.

You have two mistakes in the first piece of code that you could find based on the php errors you would be getting. Not going to specifically list the problems, because you need to learn how to debug your code, by making use of php/database errors you get, and determining where problems are at by finding where your code/data are dong what you expect and where they are not.

Next, you should use a post method form when inserting, updating, or deleting data. This is more than just a web convention, it is so that search engines indexing your site won't cause data to be inadvertently created, modified, or deleted. Search engines will only make get requests to your pages. You will also eventually need to add a user permission system to control who has permission to insert, update, or delete data.

Once you switch to use a post method form, your form processing code needs to -

1) Detect that a post method form was submitted.

2) Trim all the input data. This will allow you to detect if all white-space characters were submitted.

3) If the form processing code handles more than one 'action' (insert, update, or delete) it needs to detect which action was submitted (a switch/case statement is commonly used.)

4) Validate the input data. If the expected/required input data for any particular action isn't valid, there's no point in using it in the rest of the code. If you store validation errors in a php array variable, the array is also an error flag. if the array is empty, there are no errors. If the array is not empty, there are errors. To display the error(s) on the web page, when you re-display the form, just loop over the array holding the errors and output each one.

5) If there are no validation errors, use the submitted data.

[Weedpacket]

NogDog and then the next line probably fails because $query is not set, and that's what returns false

The first mysqli_query line is missing the connection argument, so it would have been failing as well.

Hence

pbismad You need to set php's error_reporting to E_ALL and display_errors to ON, preferably in the php.ini on your system, so that php will help you by reporting and displaying all the errors it detects.

pbismad Next, you should use a post method form when inserting, updating, or deleting data. This is more than just a web convention, it is so that search engines indexing your site won't cause data to be inadvertently created, modified, or deleted.

http://thedailywtf.com/articles/The_Spider_of_Doom

[Weedpacket]

Longer term: you should consider not deleting any data. Use an additional boolean field to show that the record is current or not and, when displaying, only select current records.

There may come a time when you want to go back through historical records, or someone might get around your login security and start "deleting" stuff at random. If you really do delete it you won't be able to get any of it back.

[sneakyimp]

Correct me if I'm wrong, but won't this command delete every record in the gigs table?

DELETE FROM gigs WHERE id = id;

[NogDog]

sneakyimp

Heh...yeah, and then the next line probably fails because $query is not set, and that's what returns false and triggers the else condition. 🙂