Delete rows from multiple tables using a single sql query in CodeIgniter

Today I have writed a function that allows users to delete their account and all their data saved in the database. I'm using CodeIgniter and I want to show you my way to delete multiple rows from multiple tables using a single sql query.

For example, an user has send multiple messages and each message has meta data saved in other table. I'm deleting the user messages and meta data with this sql query:

// a simple function to delete all user's messages
$sql = "DELETE m,a FROM messages m JOIN messages_meta a ON m.message_id = a.message_id WHERE m.user_id=?";
$this->db->query($sql, array($user_id));

I have joined two tables: messages and messages meta. Next i delete all data by user_id. You can use this query in each aspx, php and python framework.

Leave a Comment