PHP query to delete record from several table at once

PHP
1

Hi

I am trying to delete a record from several tables that have the same id (app_id). What could be the easiest way to perform this?

mysql_query('
DELETE FROM
`computer_skills`,
`education_training`,
`general_info`,
`jobs_info`,
`job_summary`,
`languages`,
`references`,
`special_skills`,
`work_experience
WHERE
app_id='. $_GET['app_id'] .'
');

I know this is a very noobish way but hey I am trying. :blush:

2

The first thing to do is to stop using the obsolete mysql_ interface and switch to using either mysqli_ or PDO.

Next thing is to stop wrapping table names in back ticks. It is unnecessary as long as your table names donā€™t clash with reserved words and it avoids the possivbility of mismatched back ticks as you have in the code you posted.

The next issue with the code you posted is that if app_id is a field in each table then the code in the WHERE clause needs to identify which table you are testing it from. Also to reference multiple tables you either need to specify a join between the tables so as to match up the entries from one table that match entries in another table OR you need to define foreign keys to associate the tables so that you can then CASCADE the deletes.

3

If you use PDO (and also mysqli) you can run multiple queries at once (read the article before you use)

<?php

$pdo = new pdo(....
$pdo->exec("DELETE FROM `computer_skills` WHERE app_id = ".$pdo->quote($_GET['app_id']).";
DELETE FROM `special_skills` WHERE app_id = ".$pdo->quote($_GET['app_id']).";
....");

You can also use prepared statements instead of that ->quote(

Else, just use some FOREACH and have tables into an array.

I do not agree with ā€œstop wrapping table names in back ticksā€ and I think itā€™s
a good practice because you will not worry about you names.

There may be situations where names come from variables and there are
harder to control when you work into a big project / big team.

4

I am a designer and am very new to world of PHP. I hardly manage procedural programming. May be one day when I keep my hands dirty with PHP I will dive into PDO as well.
Thank You guys, by the way my problem was solved. :slight_smile: