Page 1 of 1

Deleting from three tables at once

Posted: Sun Nov 30, 2008 2:13 pm
by zed420
Hi All
Can someone tell me if this is possible or do I need to wake up. I'm trying to delete some records from three different tables at once via check boxes, tabls don't have any join. This doesn't give me any error but not working either. It does work with ONE table at a time thou. some help will be greatley appreciated.

Code: Select all

        if($_POST['delete']) {
        foreach($_POST as $job_id) { 
        mysql_query("DELETE FROM job_tb,blockBook,blockBook2
         WHERE job_tb.job_id='$job_id' OR 
         blockBook.job_id= '$job_id' OR 
         blockBook2.job_id= '$job_id'"); 
        if (mysql_affected_rows() > 0) { 
        print "<font color=red size=2>Job No. = $Job_id has been deleted</font><p>"; 
        } 
    }
}
Thanks
Zed

Re: Deleting from three tables at once

Posted: Sun Nov 30, 2008 2:16 pm
by Mark Baker
zed420 wrote:Can someone tell me if this is possible or do I need to wake up. I'm trying to delete some records from three different tables at once via check boxes, tabls don't have any join. This doesn't give me any error but not working either. It does work with ONE table at a time thou. some help will be greatley appreciated.
Only possible if your database supports triggers, and you write ON DELETE triggers to handle the deletions from the other tables... then you'd just delete from the one table with the trigger, and the database would delete from the other two. Even then, though you still need a join across the tables.

Otherwise, you just have to delete from each individually.
If your database supports transactions then you delete them each in turn before committing the transaction

Re: Deleting from three tables at once

Posted: Sun Nov 30, 2008 2:48 pm
by zed420
Thanks I'll have to delete them seperatley
Zed
Mark Baker wrote:
zed420 wrote:Can someone tell me if this is possible or do I need to wake up. I'm trying to delete some records from three different tables at once via check boxes, tabls don't have any join. This doesn't give me any error but not working either. It does work with ONE table at a time thou. some help will be greatley appreciated.
Only possible if your database supports triggers, and you write ON DELETE triggers to handle the deletions from the other tables... then you'd just delete from the one table with the trigger, and the database would delete from the other two. Even then, though you still need a join across the tables.

Otherwise, you just have to delete from each individually.
If your database supports transactions then you delete them each in turn before committing the transaction