Deleting multiple values in the database with their ids with different tables

Question

I want to delete the entire content of the subject in the database when its id is deleted.

if(isset($_GET['subject_id'])){
$subject_id = $_GET['subject_id'];}

$deleteexams = mysql_query("SELECT * FROM exam_exams where exam_subject_id = '$subject_id'")or die(mysql_query());
while($ids = mysql_fetch_array($deleteexams)){
$selected = $ids['exam_id'];
}
$deletequestions = mysql_query("SELECT * FROM exam_questions where question_exam_id = '$selected'")or die(mysql_query());
while($ids2 = mysql_fetch_array($deletequestions)){
$selectedids = $ids2['question_id'];
}
$deleteanswers = mysql_query("SELECT * FROM exam_answers where answer_question_set_id = '$selectedids'")or die(mysql_query());
while($ids3 = mysql_fetch_array($deleteanswers)){
$selectedidsans = $ids3['answer_id'];
}

     /// I want to delete all of the answers inside the questions of the exams in the subject  
    mysql_query("DELETE * FROM exam_answers where answer_id = '$selectedidsans'")or die(mysql_error());
    /////i want to delete all the questions
        mysql_query("DELETE * FROM exam_questions where answer_id = '$selectedids'")or die(mysql_error());
    ////this will work because this has the subject ids
        mysql_query("DELETE * FROM exam_exams where exam_subject_id = '$subject_id'")or die(mysql_error());
    /////and this too
        mysql_query("DELETE * FROM exam_subjects where subject_id = '$subject_id'")or die(mysql_error());

My problem is how can I delete the answers and questions because it has no subject_id on its fields.

you should learn `IN` clause. – Dipesh Parmar Aug 19 '13 at 06:49 — Dipesh Parmar, Aug 19 '13 at 06:49
I think that IN clause has nothing to do with this. – Dark King Rayleigh Aug 19 '13 at 06:55 — Dark King Rayleigh, Aug 19 '13 at 06:55
well see all answer they all suggest you `IN` clause, – Dipesh Parmar Aug 19 '13 at 07:42 — Dipesh Parmar, Aug 19 '13 at 07:42
ok I got it now its really the in. hehe, thanks anyway, – Dark King Rayleigh Aug 19 '13 at 08:03 — Dark King Rayleigh, Aug 19 '13 at 08:03

score 1 · Accepted Answer · answered Aug 19 '13 at 07:13

Delete * from is not a correct syntax.

You do need to use IN as mentioned in the comment because then you can select all the answer_id s in the table using exam id, field names may change as per your situation but logic would be

   DELETE
FROM exam_answers
WHERE answer_id IN
    (SELECT answer_id
     FROM questions
     WHERE question_id IN
         (SELECT exam_id
          FROM exams
          WHERE subject_id=<yoursubjectid>))

Not an efficient method, actually you may be better off storing subject id in each table even if it is redundant

score 0 · Answer 2 · edited May 23 '17 at 12:28

Using IN with subquery would be my first option too but here are some others as well.

Are you using foreign keys in mysql tables? You could let mysql solve this for you via referential integrity and cascade. Look here: MySQL foreign key constraints, cascade delete

If you are looking at PHP solution only you should first delete records from tables where you are using foreign keys. E.g. first delete exams subject, questions and answers then delete the exam itself.

Another option would be not to delete records at all (if you need to have some sort of exam history). You could flag these records as deleted or unactive and filter them later when you select data from database. Like this: SELECT * FROM exam_exams where active = 1;

Can you provide us with your mysql create table script or ERA diagram?

Deleting multiple values in the database with their ids with different tables

2 Answers2