MySQL PHP Query delete

Mysql PHP Query delete is used to execute Mysql function ( ) delete query from a database table. MySQL PHP Query is used to delete the records from the table 'MyTable' based on condition specified in Where Clause.

Understand with Example

The Tutorial grasp you an illustrative example on 'MySQL PHP Query delete'. To understand the example we create a table 'MyTable' that has the required fieldnames and datatypes respectively.

Query for creating table named MyTable:

mysql> CREATE TABLE MyTable (
    ->              Empid int(10),
    ->              Empname varchar(60)
    ->              Salary int(90)
    ->            );
Query OK, 0 rows affected (0.13 sec)

Query for inserting data in table:

To add the records or rows to the table 'MyTable' we use insert into query.

mysql>   insert into MyTable values(01,'Girish','20000');
Query OK, 1 row affected (0.02 sec)

mysql>     insert into MyTable values(02,'A','21000');
Query OK, 1 row affected (0.01 sec)

mysql>     insert into MyTable values(03,'C','22000');
Query OK, 1 row affected (0.00 sec)

mysql>     insert into MyTable values(04,'V','23000');
Query OK, 1 row affected (0.00 sec)

mysql>     insert into MyTable values(05,'B','24000');
Query OK, 1 row affected (0.00 sec)

mysql>     insert into MyTable values(06,'E','25000');
Query OK, 1 row affected (0.00 sec)

mysql>     insert into MyTable values(07,'Q','26000');
Query OK, 1 row affected (0.01 sec)

mysql>     insert into MyTable values(08,'W','27000');
Query OK, 1 row affected (0.01 sec)

mysql>     insert into MyTable values(09,'AS','28000');
Query OK, 1 row affected (0.00 sec)`

Query to view data inserted in table:

mysql> select * from mytable;
+-------+---------+--------+
| Empid | Empname | Salary |
+-------+---------+--------+
| 1     | Girish  | 20000  |
| 2     | A       | 21000  |
| 3     | C       | 22000  |
| 4     | V       | 23000  |
| 5     | B       | 24000  |
| 6     | E       | 25000  |
| 7     | Q       | 26000  |
| 8     | W       | 27000  |
| 9     | AS      | 28000  |
+-------+---------+--------+
9 rows in set (0.01 sec)

Deleting table data using php select:

The PHP server side scripts include a server side code that include the function deleteData ( ), that is used to delete a records from table using delete Syntax. The Where Clause specifies which record or records should be omitted. To get PHP to execute the statement we use mysql _query function ( ). The function send the Mysql delete function to table 'mytable' in the database and the output is given below the code.

<?php
echo "<table><tr>";
echo "<td>".getValue()."</td>";
deleteData(8);
echo "<td>".getValue()."</td>";
echo "</tr></table>";

function deleteData($id){
$database="girish";
$user="root";
$password="root";
$host="192.168.10.126";
$link= mysql_connect($host,$user,$password) or die("Could not connect: ".mysql_error());
mysql_select_db($database,$link) or die("Error in selecting the database:".mysql_error());
$sql="delete from mytable where empid= ".$id;
mysql_query($sql,$link) or exit("Sql Error".mysql_error());
}
function getValue(){
$database="girish";
$user="root";
$password="root";
$host="192.168.10.126";
$link= mysql_connect($host,$user,$password) or die("Could not connect: ".mysql_error());
mysql_select_db($database,$link) or die("Error in selecting the database:".mysql_error());
$sql="select * from mytable";
$sql_result=mysql_query($sql,$link) or exit("Sql Error".mysql_error());
$sql_num=mysql_num_rows($sql_result);
echo "<b>Table before Deleting data</b>";
echo "<table border=\"1\" bgcolor=\"#CCCCFF\" >";
echo "<tr>
<td><b>Empid</b></td>
<td><b>Empname</b></td>
<td><b>Salary</b></td>
</tr>" ;
while($sql_row=mysql_fetch_array($sql_result))
{
$Empid=$sql_row["Empid"];
$Empname=$sql_row["Empname"];
$Salary=$sql_row["Salary"];
echo "<tr>";
echo "<td>".$Empid."</td>";
echo "<td>".$Empname."</td>";
echo "<td>".$Salary."</td>";
echo "</tr>";
} 
echo "</table>";
}
?>

Output:

After the deletion of record from table 'mytable', the table is look like as

MySQL PHP Query delete

MySQL PHP Query delete

MySQL PHP Query delete

Tutorials