Delete mysql cascade in PHP

Question

I help my friend with working with databases. It basically has 2 databases, one called the city, and one called the delivery speed. Each table is displayed on a web page with an edit and delete button next to each record. Therefore, it can delete data in the database via the Internet.

The deal is that I want to cascade delete mysql in this database, so that every time I delete a row in the city database, the corresponding row in the delivery speed database with the same city_ID is also deleted. I tried to execute some combination of mysql queries, but that didn't work. Here is what i have

require_once('../../model/city.php'); if(isset($_POST['id'])){ //edit or remove $id = $_POST['id']; $dbo = City::get_by_id($id); if($_POST['action'] == 'remove'){ //remove //$dbo->delete(); mysql_query("DELETE city.*, shipping_rate_loc.* FROM city c, shipping_rate_loc s WHERE c.ID_city = s.ID_city"); echo "Data is removed."; } 

As you can see, I just put this mysql_query in my previous

 $dbo->delete(); 

who managed to remove the city, but not associated with the delivery rate.

I am new to using mysql inside PHP, so can anyone help me point out where is my error? Many thanks:)

update: I tried some solutions from the answers. none of them work. even when I simplified things. What makes me wonder if there is any error in terms of connecting php to the database?

Answer 1

A better approach would be to make changes to the database configuration to include on delete cascade in the foreign key for the shipping_rate_loc table, for example.

 create table city ( id integer, name varchar, ... primary key (id) ); create table shipping_rate_loc ( id integer, city_from integer, city_to integer, ... foreign key (city_from) references city(id) on delete cascade, foreign key (city_to) references city(id) on delete cascade ); 

That way, when you delete an entry from the city table with

 DELETE FROM city WHERE id = myid 

all records from the shipping_rate_loc table where this city is in the city_from or city_to column will be automatically deleted by the database - without having to do anything in PHP.

Answer 2

Technically, the cascade must be performed on the tables themselves. But this requires foreign keys to be installed, which, in turn, requires InnoDB (which tables you may or may not have).

 "DELETE city.*, shipping_rate_loc.* FROM city c, shipping_rate_loc s WHERE c.ID_city = s.ID_city" 

The problem with this is that you are not actually specifying which identifier to remove in SQL. Instead

 "WHERE c.ID_city = s.ID_city" 

to try

 "WHERE c.ID_city=:city_id AND s.ID_city=:city_id" 

and use the parameter binding to pass the POST identifier to the request.

see http://www.php.net/manual/en/ref.pdo-mysql.php for more information

Answer 3

you can use left merge deletion to perform a cascade deletion.

it looks something like this:

 $query = "DELETE city, shipping_rate FROM city LEFT JOIN shipping rate ON city.id = shipping_rate.city_id WHERE city.id = $blah"; 

Answer 4

You can write a stored procedure for this.

 CREATE PROCEDURE DeleteCity(IN CityID INT) BEGIN delete from city where ID_city = CityID; delete from shipping_rate_loc where ID_city = CityID; END $$ 

After that, removing the City is as simple as:

 call DeleteCity(5); 

Answer 5

When you create your table, you must configure CASCADE to delete / update.

Check out this article that will answer your question.