Delete data from MySQL using PHP | Delete Multiple Data from MySQL using PHP
After Inserting Data in MySQL using PHP and Fetching MySQL data using PHP, now we are going to see how to delete data from MySQL using PHP. In this post we have describe the way by which you can delete single as well as multiple records at a time using MySQL and PHP.
How to Delete Multiple Data from MySQL using PHP
Delete from table_ name where column_name= value;
In this code, First of all we have fetch all the available data of MySQL table i.e. emp table using PHP and with that we have provided checkbox, by clicking on it you can delete one or more than one record on emp table.
// Code for Fetching data with Checkbox option for deletion :
<table>
<?php
$con=mysql_connect("localhost","root",""); // opening MySQL connection
if(!$con)
{ echo "Connection error".mysql_error();
}
else
{ $res=mysql_select_db("employee",$con); // Select the Database
if(!$res)
{
echo "Database not FOund...".mysql_error();
}
else
{ $res=mysql_query("Select * from emp"); // Execute query that will fetch all records of emp table.
$count=mysql_num_rows($res); // mysql_num_rows() will count the total number of records of emp table.
while($r=mysql_fetch_row($res)) // Loop to fetch all records form table.
{
echo "<tr>";
echo "<td align='center'><input name='checkbox[]' type='checkbox' id='checkbox[]' value='". $r[0] . "'> </td>";
echo "<td align='center'>$r[0]</td>";
echo "<td width='200'>$r[1]" . " $r[2]</td>";
echo "<td align='center' width='40'> $r[3]</td>";
echo "<td align='center' width='200'>$r[4]</td>";
echo "<td width='100' align='center'>$r[5]</td>";
echo "</tr>";
}
}
}
?>
<tr> <td colspan="6" align="center"><input id="delete" name="delete" type="submit" value="Delete"></td> </tr>
</table>
Output :
In this code we have used :
- mysql_connect() : Used to open connection in MySQL and takes three parameter i.e. SERVER,USER_NAME and PASSWORD. we take default values for these parameters i.e. localhost, root and empty string respectively. This function returns a MySQL link identifier on successful connection and return false if fails to open connection.
- mysql_error() : this returns the text of the error message from previous MySQL operation and empty string if no error occurred.
- mysql_select_db() : Sets the current active database on the server which is associated with specified link identifier returned by mysql_connect().
- mysql_query() : This sends a query to current active database on the server. It takes SQL query as parameter.
- mysql_fetch_row() : This function get a result row as an enumerated array. It returns numerical array corresponds to the fetched row and moves the internal data pointer ahead. mysql_fetch_row() fetches one row of data from the result associated with the specified result identifier. The row is returned as an array and each result column is stored in array offset which starts at offset 0.
- mysql_num_rows() : This function retrieves number of rows from a result set.
// Code to Delete MySQL Data using PHP:
<?php
if(isset($_POST["delete"]))
{
$checkbox = $_POST['checkbox'];
for($i=0; $i<$count ; $i++)
{
$delete_id = $checkbox[$i];
$query = "delete from emp where empId='$delete_id'";
$del_res = mysql_query($query) or die("Invalid query");
}
// On successful deletion, it will redirect to emp_delete.php
if($del_res)
{
echo "<meta http-equiv=\"refresh\" content=\"0;URL=emp_delete.php\">";
}
}
mysql_close(); // closing the MysQL Connection
?>
This is the code that will delete records from database and will refresh data i.e. you will show updated data from table.
Complete Code of Deletion of Data using PHP :
<html>
<head> <title> Delete Data of MySQL uding PHP </title> </head>
<body>
<form name="frmDelete" method="post" action="emp_Delete.php">
<center>
<h2 align="center">Employee Data</h2>
<table border="1">
<tr align="center">
<th><input name='checkbox' type='checkbox'> </th>
<th>Emp_Id </th>
<th>Emp_name </th>
<th>Age</th>
<th>Designation</th>
<th>Salary</th>
</tr>
<?php
$con=mysql_connect("localhost","root",""); // opening MySQL connection
if(!$con)
{ echo "Connection error".mysql_error();
}
else
{ $res=mysql_select_db("employee",$con); // Select the Database
if(!$res)
{
echo "Database not FOund...".mysql_error();
}
else
{ $res=mysql_query("Select * from emp"); // Execute query that will fetch all records of emp table.
$count=mysql_num_rows($res); // mysql_num_rows() will count the total number of records of emp table.
echo "<tr><td colspan='6'></td></tr>";
while($r=mysql_fetch_row($res)) // Loop to fetch all records form table.
{
echo "<tr>";
echo "<td align='center'><input name='checkbox[]' type='checkbox' id='checkbox[]' value='". $r[0] . "'> </td>";
echo "<td align='center'>$r[0]</td>";
echo "<td width='200'>$r[1]" . " $r[2]</td>";
echo "<td align='center' width='40'> $r[3]</td>";
echo "<td align='center' width='200'>$r[4]</td>";
echo "<td width='100' align='center'>$r[5]</td>";
echo "</tr>";
}
}
}
?>
<tr> <td colspan="6" align="center"><input id="delete" name="delete" type="submit" value="Delete"></td> </tr>
</table>
// code to delete record(s) of table
<?php
if(isset($_POST["delete"]))
{
$checkbox = $_POST['checkbox'];
for($i=0; $i<$count ; $i++)
{
$delete_id = $checkbox[$i];
$query = "delete from emp where empId='$delete_id'";
$del_res = mysql_query($query) or die("Invalid query");
}
// On successful deletion, it will redirect to emp_delete.php
if($del_res)
{
echo "<meta http-equiv=\"refresh\" content=\"0;URL=emp_delete.php\">";
}
}
mysql_close(); // closing the MysQL Connection
?>
</center>
</form>
</body>
</html>
