How to DELETE from a table.
TAGS: SQL, DELETE, PHP, MySQL


In this posting we will be economical and recycle most of the code, most of the HTML anyway. In this posting, I will show how you can delete a row from the table 'contacts', that we have created earlier, and populated with information about our contacts. Put all the code in a file named 'delete.php'.
So, lets go.

http://127.0.0.1/delete.php

<?php 

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

function dodelete() { 

$IDnummer = $_POST['contact']; 

$con=mysqli_connect('127.0.0.1', 'root', 'Your_Password', 'dbname'); 
if (mysqli_connect_errno()) { 
  echo "Error connecting to DB"; 
} 
mysqli_query($con, 'DELETE FROM contacts WHERE AutoID=' . $IDnummer . ''); 
mysqli_close($con); 
} 
echo 'Success, you have deleted a row from your table.'; 

?>

<HTML> 
<BODY> 
<FORM METHOD='POST' ACTION=''> 
<TABLE> 
<TR> 
<TD>Our contacts: </TD> 
<TD><SELECT NAME='contact'><OPTION>Make your choice 

<?php 
$con = mysqli_connect('127.0.0.1', 'root', 'Your_Password', 'dbname'); 

if (mysqli_connect_errno()) { 
  echo 'Error connecting to DB'; 
} 

$SQL_String = mysqli_query($con, 'SELECT * FROM contacts ORDER BY AutoID ASC'); 

while($row = mysqli_fetch_array($SQL_String))  { 
  echo '<OPTION VALUE=' . $row['AutoID'] . '>' . $row['Firstname'] . ' ' . $row['Lastname']; 
} 
mysqli_close ($con); 
?> 

</SELECT> 
</TD> 
</TR> 
<TR> 
<TD></TD> 
<TD><INPUT TYPE='submit' NAME='readme' VALUE='Okay, delete'> 

</TR> 
</TABLE> 
</FORM> 
</BODY> 
</HTML> 

What happens is that we populate a SELECT, with all entries from your table. Here you can make a selection and by clicking the submit, the AutoID of the row is read by the PHP program, that then will delete the entire row from the table.
The thing doing it, is by query the SQL string. The commando is 'DELETE', and we specify the exact row, by the AudoID, that we read from the SELECT. Like this:

DELETE FROM contacts WHERE AutoID=$IDnummer


When you delete this way you wont get any warnings. The row is instantly deleted and gone. Look at the SELECT again, the entry you wanted to delete should no longer be in the list.

Now things are rolling. You soon have an arsenal of handy SQL knowledge. The next blogposting here on this site will be about updating an existing row in your table. Imagine one of your contacts will change her emailaddress. You will then have to change it in your contact database. Updating.

Look out for the next post.