Do a mysql cascade delete on PHP

Question

I'm helping my friend in doing some database management stuff. Basically he has 2 databases, one is called city and one is called shipping rate. Each tables are displayed in a web page with edit and remove button beside each record. So he can delete the data in database via web.

The deal is, I want to do a cascade delete on this mysql database, so that every time I delete a row in city database, a corresponding row in shipping rate database with the same city_ID is deleted as well. I tried to do some mysql query combination, but it didn't work. Here's what I've got

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'm just putting that mysql_query on his previous

$dbo->delete();

code which managed to delete the city, but not its related shipping rate.

I'm fairly novice in using mysql inside PHP, so can anybody help me pointing out where my mistake is? Thank you very much :)

update: I've tried some solutions from the answers. none of them working. even when I simplified things. Which makes me wonder, is there any mistake in terms of connecting the php to the database?

score 2 · Answer 1 · answered Mar 05 '12 at 16:40

2

The best approach to this would be to amend the database setup to include on delete cascade to the foreign key for shipping_rate_loc table, e.g.

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
);

This way, when you delete a record from city table with

DELETE FROM city WHERE id = myid

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

answered Mar 05 '12 at 16:40

Aleks G

56,435
29
168
265

hi mate, I got this error _#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FOREIGN KEY (ID_city) references city (ID_city) on delete cascade ) ENGINE=Inno' at line 6_ when I tried to add foreign keys. any idea? – gaban Mar 05 '12 at 17:31
What's your full SQL statement? – Aleks G Mar 05 '12 at 17:37
CREATE TABLE IF NOT EXISTS `shipping_rate_loc` ( `ID_city` int(11) NOT NULL, `ID_shipping_method` int(11) NOT NULL, `shipping_rate` float NOT NULL, PRIMARY KEY (`ID_city`,`ID_shipping_method`) foreign key (ID_city) references city(ID_city) on delete cascade ) ENGINE=InnoDB – gaban Mar 05 '12 at 17:38
@gaban Add a comma before `foreign key` – Aleks G Mar 05 '12 at 17:45
this one works! however, I'm keeping it as alternative. I'll have to convince my fried to use InnoDB first. Cheers mate! – gaban Mar 05 '12 at 17:54

score 1 · Answer 2 · answered Mar 05 '12 at 16:34

1

Technically the cascade should be done on the tables themselves. But this requires that foreign keys be setup, which in turn requires InnoDB (which your tables may or may not be).

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

The problem with this is you don't actually specify which id to remove in the SQL. Instead of

"WHERE c.ID_city = s.ID_city"

try

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

and use parameter binding to pass the POST id into the query.

see http://www.php.net/manual/en/ref.pdo-mysql.php for additional details

answered Mar 05 '12 at 16:34

Mr Griever

4,014
3
23
41

Thanks Matt. that explains. But,I'm sorry, but how to do parameter binding? – gaban Mar 05 '12 at 16:49
Check out http://www.php.net/manual/en/pdo.prepare.php for a lot of information about using pdo to create a prepared statement, parameter binding and query execution. – Mr Griever Mar 05 '12 at 17:51
Binding parameters with PDO is critical when using data from a user (ie $_POST, $_REQUEST, etc.) to avoid SQL injection. Don't forget to sanitize your values as well. – Mr Griever Mar 05 '12 at 17:57

score 1 · Answer 3 · answered Mar 05 '12 at 16:37

1

you can use a left joined delete to perform a cascading delete.

this would look something like:

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

answered Mar 05 '12 at 16:37

dqhendricks

19,030
11
50
83

mm, what's that $blah in the end refers to? – gaban Mar 05 '12 at 16:53
I apply it like this `$query = "DELETE city, shipping_rate_loc FROM city LEFT JOIN shipping_rate_loc ON city.ID_city = shipping_rate.ID_city WHERE city.ID_city is null";` but it still doesn't work. the row on both tables still there. – gaban Mar 05 '12 at 17:02
The syntax may differ, but the effect is the same. "FROM a,b WHERE a.c=b.c" is exactly the same as "FROM a JOIN b ON a.c=b.c". The important thing here is the "WHERE city.id=$blah", $blah being the city_id taken from the $_POST. "WHERE city.ID_city is null" ensures the query will never accomplish anything. – Mr Griever Mar 05 '12 at 17:48
@gaban yes $blah is supposed to be the sanitized city id. otherwise how would it know which city to delete? also, please be sure to read up about mysql injection before you do something that may endanger your server. – dqhendricks Mar 05 '12 at 18:54

score 1 · Answer 4 · answered Mar 05 '12 at 16:42

You may want to write a stored procedure to do it.

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, deleting a City is as simple as:

call DeleteCity(5);

score 0 · Answer 5 · edited May 23 '17 at 10:30

0

When you create your table you should have set up the CASCADE delete/update then.

check this post which will answer your question.

edited May 23 '17 at 10:30

Community

1
1

answered Mar 05 '12 at 16:31

Hassan

301
1
13

1

that requires me to change the db engine to InnoDB, which my friend is somewhat reluctant to. so i think some ancient mysql is needed here. – gaban Mar 05 '12 at 16:33

Do a mysql cascade delete on PHP

5 Answers5