We will now use our users table inside the database examples:
Our users table has 3 columns: id, name, surname.
To DELETE a row from a table we will:
- connect to the server (localhost)
- select a database (examples)
- create a SQL DELETE query
- run the query and store the result
- check if the query has been successful
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
<?php #deleteData.php //connecting to server $dbc = mysqli_connect('localhost','root','') or die('could not connect: '. mysqli_connect_error() ); //selecting the database mysqli_select_db("examples") or die('could not select db'); // OLD DATA: SUPPOSE WE HAVE A ROW WITH... /* * id = 15; * name = "jane"; * surname = "doe"; */ // AND WE WANT TO DELETE IT //THE ID OF THE ROW TO DELETE $delete_id = 15; //CREATING A DELETE QUERY (use LIMIT to be sure you delete only that row ) /* * DELETE FROM `DB`.`TABLE` WHERE `TABLE`.`FIELD` = value LIMIT 1 ; */ $q = "DELETE FROM `examples`.`users` WHERE `users`.`id`= '$delete_id' LIMIT 1 "; //EXECUTING THE QUERY AND STORING THE RESULT IN A VARIABLE $result = mysqli_query($dbc, $q); //CHECKING IF THE QUERY AFFECTED A ROW USING mysqli_affected_rows() if(mysqli_affected_rows($dbc) == 1 ){ //positive response echo "user deleted"; } else { //negative response echo "could not delete user"; } //CLOSING DATABASE CONNECTION mysqli_close($dbc); ?> |
PLEASE NOTE: the DELETE command is quite powerful, if you do not specify which row you want to delete, ALL the rows will be deleted. That’s why you should always use a WHERE clause.