DELETE statement
In this lesson we are going to write a PHP script that deletes records in MySQL database using the DELETE statement.
We can execute any sql query like insert, update, delete, select etc. using the mysql_query() function.
In this lesson we need to three pages namely the list.php, deleteform.php and deleteprocess.phpHere is the code(list.php):
<?php//including the database connection file include("mysql_connect.php");mysql_select_db("my_db");$result = mysql_query("SELECT * FROM employee_record ");echo "<table border='1'><tr> <th>ID</th> <th>Firstname</th> <th>Lastname</th> <th>Position</th> <th>Age</th> <th>Salary</th> <th>Email</th> <th>Action</th> </tr>";while($row = mysql_fetch_array($result)) { echo "<tr>"; echo "<td>".$row['id']."</td>"; echo "<td>".$row['f_name']."</td>"; echo "<td>".$row['l_name']."</td>"; echo "<td>".$row['position']."</td>"; echo "<td>".$row['age']."</td>"; echo "<td>".$row['salary']."</td>"; echo "<td>".$row['email']."</td>"; echo "<td><a href=\"deleteform.php?id=$row[id]\">Delete</a></td>"; } echo "</table>"; ?>
In the above example it will select all the data stored in our employee_reecord table. Name this file as list.php.
The function of the code below is to get the id of the selected row and pass the value to deleteform.php.
echo “<td><a href=\”deleteform.php?id=$row[id]\”>Delete</a> </td>”;
Now, let’s create the deleteform.php.
Here is the code:
<?phpinclude('mysql_connect.php');$query = "SELECT * FROM employee_record where id=$_GET[id]";$result = mysql_query($query);while($rows = mysql_fetch_array($result)) { $id = $rows['id']; $f_name = $rows['f_name']; $l_name = $rows['l_name']; $position = $rows['position']; $age = $rows['age']; $salary = $rows['salary']; $email = $rows['email']; } ?> <html> <br><br> <form action='deleteprocess.php?id=<?php echo $id?>' method='POST'>ID: <?php echo $id?><br><br> Firstname: <?php echo $f_name?><br><br> Lastname: <?php echo $l_name?><br><br> Position: <?php echo $position?><br><br> Age: <?php echo $age?><br><br> Salary: <?php echo $salary?><br><br> Email: <?php echo $email?><br><br><input type='submit' name='submit' value='Delete'> <input type='submit' name='cancel' value='Cancel > </form> </html>
In the deleteform.php, first we have included the mysql_connect.php which is the file we need to establish a connection in our database.
Then we have declared $query variable that will store the sql command. In our query, we have selected the record of the employee where his/her id is equivalent to the id selected in list.php.
Next we have declared a $result variable that will handle the result set returned by our query.
Next, we use the mysql_fetch_array() function to fetch a row from a recordset as an array.
Then, we place the mysql_fetch_array() function within the conditional statement of the while loop. It means that the while loop will continue to execute as long as there a row to fetch.
Then we have created a HTML form that will display the record of the employee whose id where selected in the list.php.
The record that is being displayed in this page will be the record that we are going to delete.
This page will submit the record of the employee (id, f_name, l_name, position, age, salary, email) to the page deleteprocess.php when the submit button is clicked.
Now we are going to write our deleteprocess.php.
Here is the code:
<?phpinclude('mysql_connect.php');if(isset($_POST['submit'])) { $id = $_GET['id']; $query = "Delete from employee_record where id=$id"; mysql_query($query); echo "<br>Successfully Deleted!"; echo "<br><a href='list.php'>Back</a>"; } else { echo "<br><a href='list.php'>List</a>"; } ?>
In the deleteprocess.php, first we have included the mysql_connect.php which is the file we need to establish a connection in our database.
Then, we have declared variables to store the value data that will be used to delete the record.
Next, we will execute the delete statement to delete the record. It will display Successfully Deleted! saying that we have successfully deleted the record in the database.
Finally, we have created a link back to the list.php.
html pud nah tutorial.. ty