Mysqli -> delete


#1

If I have the following cod, and assume that there is no ID=4, I am still getting “record deleted successfully”. Is this a correct code to code? I saw some website program in this way, but will it work for all possible scenarios?

$sql = "DELETE FROM MyGuests WHERE id=4";
if (mysqli_query($conn, $sql)) {
   echo "Record deleted successfully"; 
} else {
   echo "Error deleting record: " . mysqli_error($conn); 
}

Kindly advise the necessary. Thank you.


#2

The query is valid and was successfully processed by the server - that tells you nothing about what data has changed. “record should be deleted but does not exist” is no error in this case, because the record has never existed, what leads to the same result. You have to implement this behavior by yourself.

define all possible scenarios.


#3

The query is indeed valid. Telling the database to delete all Bananas from your table is still a valid command if there are no Bananas. The database simply does nothing, and has complied with your command.

What you can check is the number of affected rows to see if the database did anything.


Mysqli_error()
#4

I am (or the end user) will be confused by the “record deleted successfully” when it actually cannot even locate ID=4. There is nothing deleted from the system and how can we display a successful message?

mysqli_query($conn, $sql) will always return 1 and hence the IF clause is forever true.

All possible scenarios mean if DELETE is successful and unsuccessful.


#5

Then you should rephrase the message.

Unless you have an error in your SQL statement, which is what mysqli_error() is for.


#6

Thanks. I have no doubt about the query itself. There is nothing wrong with the query.

Delete From MyGuests Where name =“no such name”, the output cannot be “your record is deleted successfully”. I can’t teach someone to program this way. If the record is not found, then the outcome is “record not found in system, nothing is deleted!”

Sorry for my negative feedback, I always hate myself for being so “stubborn” :):roll_eyes:


#7

What you’re looking for is not the return value of mysqli_query()–which only indicates that the query didn’t fail–but the affected rows as @m_hutley already mentioned.


#8

What does your personal expectation of a frontend workflow has to do with the current implementation of the database engine? If that’s what you want to do - just do it, nobody holds you back, write a program that behaves like this, or re-implement the SQL driver, it’s open source.


#9

If you tell someone to go empty the plates out of the dishwasher but the dishwasher doesn’t have any plates in it then by your logic they failed to do their job. Does that make logical sense? Determining the number of plates to empty out of the dishwasher is a separate process. A process that could be a prerequisite before asking the plates be emptied out of the dishwasher. That said as others have stated in MySQL you can determine the number of affected rows by a separate query after the items are deleted.