MySQL Deleting row if

Question

I would be glad if somebody could help me solve this problem. I have a music database. There are 3 tables:

album (id, album_name)
album_has_song (album_id, song_id)
song (id, song_name)

I inserted a song to an album (filled in all 3 tables), but now I would like to find an easy way to delete everything when I delete certain album.

$query1 = mysql_query("DELETE FROM album_has_song WHERE album_id=$albumID");
$query2 = mysql_query("DELETE FROM album WHERE id=$albumID");
$query3 = mysql_query("DELETE FROM song JOIN album_has_song ON song.id=song_id WHERE album_id=$albumID AND SELECT EXISTS(SELECT * FROM song JOIN album_has_song ON song.id=song_id WHERE album_id=$albumID)");

$query1 and $query2works without any problems. Now $query3.. I would like to check if album has any songs and after that delete all songs that are on the certain album.

[Your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — Jay Blanchard, Nov 11 '15 at 19:27
If you can, you should [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). [These extensions](http://php.net/manual/en/migration70.removed-exts-sapis.php) have been removed in PHP 7. Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really not hard](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Nov 11 '15 at 19:27
Please [don't use `mysql_*`](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php?rq=1); the `mysql_*` functions are outdated, [deprecated](http://us3.php.net/manual/en/intro.mysql.php), and insecure. Use [`MySQLi`](http://us3.php.net/manual/en/book.mysqli.php) or [`PDO`](http://us3.php.net/manual/en/intro.pdo.php) instead. — elixenide, Nov 11 '15 at 19:28
You should use a stored procedure as this process requires a modicum of logic unless you want to put the logic in PHP. — Jay Blanchard, Nov 11 '15 at 19:28

score 1 · Accepted Answer · answered Nov 11 '15 at 19:33

1

You will need to use foreign key and with one query "DELETE CASCADE" you can delete all rows in all tables.

See examples here.

answered Nov 11 '15 at 19:33

Rogerio Kleinkauf

78
6

You just beat my answer ;-) I agree 100%. This is the approach I'd take. – wally Nov 11 '15 at 19:39

score 0 · Answer 2 · answered Nov 11 '15 at 19:32

Interface and SQL-injection issues aside (to be fixed later), but to get the logic working you need:

$query3 = mysql_query("DELETE FROM song JOIN album_has_song ON song.id=song_id WHERE album_id=$albumID AND EXISTS(SELECT * FROM song JOIN album_has_song ON song.id=song_id WHERE album_id=$albumID)");

score 0 · Answer 3 · answered Nov 11 '15 at 19:37

There's a multitude of ways - but they get smaller if you need to be transactionally safe (that is, you want everything to happen "as one action", so nothing else running at the same time can get confused or cause data inconsistencies).

One (of a couple of) obvious ways is to use foreign keys.

These are simple in concept - they enforce referential integrity - that is to say they enforce that data in one table can only exist if data in another table matches (for example, a song can only exist in an album if the album exists).

Setting up foreign keys is a bit cumbersome with plain SQL, any half-decent MySQL graphical client will make light work of it.

The key feature of foreign keys in MySQL which will solve your problem is referential actions. You can instruct MySQL to cascade deletion, so if your album is deleted, all the associated songs go with it. No more coding needed!

You'll need to be using an appropriate database engine, like InnoDB (the default I believe since MySQL 5.6).

Have a look around at the links provided, and let us know if you're stuck!

Thanks for the explanation! It's just for a school project but I want to learn MySQL anyways. So these type of information about 'cascade' and other stuff helps to give me a wider view on coding or however I could say that. Just thank you for your help! — Martin15, Nov 13 '15 at 13:26
You're very welcome ... Unfortunately what doesn't seem to be taught at school (or University in the UK - I'm assuming you're from the US?) is "real world" problem solving. They teach "normalize everything" and steer away from transactional safety etc ... which is a shame, because once you hit the real world, these things become important. You might also want to look at PostgreSQL which is free and provides a LOT of cool functionality which MySQL does not. (It'll help you appreciate that not all DBs are like MySQL, which I unfortunately didn't have appreciation for when I graduated :)) — wally, Nov 13 '15 at 14:46

MySQL Deleting row if

3 Answers3