Delete operation in MySQL using PHP

Delete Statements in PHP

Following the Common Steps to execute any SQL statements in PHP, we define a string variable “$sql_statement” which contains the actual SQL statement to delete a record from a mysql table user_info which has two fields userid and password. This is explained as below.

$sql_statement = “Delete from user_info”;

The above SQL statement when executed, will delete all the records from the table user_info. If you want this to happen, It’s OK. If you want any specific record to be deleted, you should use the where clause of the SQL statement as in the below PHP statement.

$sql_statement = “Delete from user_info where userid=’john’”;

The above statement can be more refined to validate the password of user john also before deleting the record as in the example below.

$sql_statement = “Delete from user_info where userid=’john’ AND password=‘secret’”;

The above statement would delete only a record which has the values of userid and password as john and secret. In other words, the SQL statement will not delete the record if the password is wrong (i.e. does not matches the value in database for john).

A dynamic SQL statement to delete any specific record in PHP would be something like this.

$sql_statement = “Delete from user_info where userid=’”.$userid.”’ AND password=‘”.$password.”’”;

Notice the use of dot operator and quotes.

After creating the statement, we execute this query using the PHP inbuilt function mysql_query () as explained below.

mysql_query ($sql_statement, $conn);

As explained earlier, $conn is the database connection string retrieved using the mysql_connect () function.

Hence the complete code to update a mysql table would be as below.

$sql_statement = “Delete from user_info where userid=’”.$userid.”’ AND password=‘”.$password.”’”;
mysql_query($sql_statement,$conn) or die(“Could not delete the user”);

After the execution of this statement, the record satisfying the delete criteria (Condition specified by the where clause) will be deleted.

Delete MySQL Table using PHP
To delete a table the following sql statement can be used.

$sql_statement = "DROP TABLE ".$table_name;

This statement should then be executed using mysql_query();
Delete MySQL Database using PHP
To delete a database the following sql statement can be used.

$sql_statement = "DROP DATABASE ".$database;

This statement should then be executed using mysql_query();
Example to delete records from a MySQL table

This source code creates a webform. The user is allowed to enter the name of the database, the name of the table and an optional "where" condition. A typical where condition would be something like "fldname > 5". This web form is processed and the records are delete from the database.

Error is thrown if anything goes wrong. The name of this file is "delete_table.php". Try it yourself on your local machine!


<?php
//Retrieve the database and table name
$database = $_GET["db"];
$table = $_GET["table"];
$where = $_GET["where"];

//Continue if database name is non blank
if($database!="" && $table!="")
{
$sql_statement = "Delete from ".$table." ".$where;

//Connect to the MySQL Database
$conn = mysql_connect ("host","userid", "password");;

//Select the database
mysql_select_db($database,$conn);
//Execute query
mysql_query($sql_statement,$conn) or die("Could not update table");
echo "All records from ".$table. " satisfying the condition have been deleted from database ". $database;
}
?>
<html>
<head>
<title>Delete Records</title>
</head>
<body>
<form action="delete_table.php">
<table align=center>
<tr><td>Database Name:</td><td><input type=text name=db></td></tr>
<tr><td>Table Name:</td><td><input type=text name=table></td></tr>
<tr><td>Where Clause:</td><td><input type=text name=where></td></tr>
</form>
</body>
</html>

  • 387 reads