Guillaume de Maleprade Guillaume de Maleprade - 1 month ago 6
MySQL Question

Delete the row with the highest id in mysql, using php

I'm working on a really simple minichat program (

php/mysql
) which displays the last 10 messages.

I wanted to add a button to delete the last message (using a form, leading to a
php
file like the one under).

I'm really a beginner with
php
and
mysql
so, I don't understand why it doesn't work.

Follows my code:

<?php
// Create connection
$cn = new mysqli("localhost","root","","test");

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

$sql = "DELETE FROM `minichat` WHERE `minichat`.`id` = ('SELECT MAX(`id`) FROM `minichat`')";

if($cn->query($sql) === TRUE){
echo "Deleted succesfully";
}
else
{
echo "Error deleting record: " . $cn->error;
}

//header('Location: connexion.php');
?>

Answer

According to the manual on DELETE Syntax:

Subqueries

You cannot delete from a table and select from the same table in a subquery.

So instead you should do something like:

DELETE FROM minichat ORDER BY id DESC LIMIT 1

And you probably want a condition to make sure a user can only delete his / her own comment..