DELETE data in multiple tables mySQLi Prepared Statement efficiently

Question

I'm deleting data from the dB in multiple tables. I'm sending the data username over to delete-member.php using a modal to confirm (delete) like this:

<script>
    $('.delete-button').on('click', function (e) {
    var username = $(this).attr('data-id');
     $('.confirm-delete').attr('data-id',username);

    });
    $(".confirm-delete").on('click', function (e) {
        var username = $(this).attr('data-id');
        console.log(username);

        //Ajax for deleting category
        $.ajax({
            url: 'handlers/delete_member.php',
            type: 'GET',
            data: {username: username},
            error: function() {
                alert('Something went wrong');
            },
            success: function(data) {
                console.log("Member Deleted");
            }
        });
        location.href="site_members.php?username="+username;
    });
</script>

Everything works fine here, I $_GET the username and delete the data. The challenge I'm having is if that particular user has been using the system for awhile. In order to do a hard delete of that user, I need to mk. sure that username is deleted in multiple tables. 15 to be exact.

Using prepared statements, I set about deleting each occurrence as follows:

if(isset($_GET["username"])) {
$userFromURL = $_GET["username"];

//delete member information from users
global $con;
$delete_mbrQ = mysqli_prepare($con, "DELETE FROM users WHERE username=?");
$delete_mbrQ->bind_param("s", $userFromURL);
$delete_mbrQ->execute();
$delete_mbrQ->close();

//delete member posts
$delete_postsQ = mysqli_prepare($con, "DELETE FROM posts WHERE added_by=?");
$delete_postsQ->bind_param("s", $userFromURL);
$delete_postsQ->execute();
$delete_postsQ->close();

//delete messages
$del_msgsQ = mysqli_prepare($con, "DELETE FROM messages WHERE user_to=? OR user_from=?");
$del_msgsQ->bind_param("ss", $userFromURL, $userFromURL);
$del_msgsQ->execute();
$del_msgsQ->close();

etc...(I won't put all 15 but you get the gist) I did this for all 15 tables where username occurs and it works fine. However, looking at this big page of recurring code, I'm wondering if there is a more efficient way to do this? Can I combine this into one or two statements to achieve the same thing?

I've been researching on this, but most of what I've read talks more about setting up a foreign key, which I've done in some cases but clearly not for this. I also don't find much for Prepared Statements and instances where the column names are different. Any help, examples, links etc. would be appreciated.

score 2 · Answer 1 · edited Jun 20 '20 at 09:12

You can use MySQL joins; `inner join` (or `left join`)

Using the examples you have above, you can construct a single SQL query that combines various tables together using join queries and delete all results from these joins.

DELETE users, posts, messages FROM users 
       INNER JOIN posts 
             ON posts.added_by = users.username
       INNER JOIN messages 
             ON messages.user_to = users.username OR messages.user_from = users.username
WHERE users.username= ?

This should do what you need, however with 15 tables this could easily be more vebose and possibly slower if your indexing is not well set out.

Sources:

Also...

To maintain MySQL efficiency you can simply wrap your PHP Prepared Statements in a loop and run the loop per table , depending on how your tables are set out this seems to be possibly easy but only most of the time. Some repetition may still remain.

... Use foreign keys in a new table a delete_table which references the occurance of the row in all (15) other tables, then simply delete from the delete_table and watch the delete cascade!

Thanks for this. I'm trying to understand the solution you suggest: wrapping my Prepared Statements in a loop and running it per table. Do you mean `while(something) {all of my prepared statements}` ? — Raylene, Feb 10 '19 at 04:41
@Raylene yes, kind of, but it would work fully if the column name `username` was the same across all tables and the count of `?` was the same, but this appears to only be so on *some* iterations so ..... it can be done but requires more *loop-exception* coding — Martin, Feb 10 '19 at 14:05

score 1 · Answer 2 · answered Feb 09 '19 at 12:48

In MySQL you can define a foreign key on a column that cascades deletions once the referenced row gets deleted. See https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html

You can alter your posts table so that the added_by column becomes a foreign key, which means that its values identify rows in the referenced table. Once a row in the users table gets removed, the database must take an action on all the rows in the posts table that reference the removed user row to maintain consistency. You can choose not to allow the removal if any post instances exist for the removed user or you can instruct to also remove all post instances when that happens. You can do it with this statement:

ALTER TABLE posts ADD FOREIGN KEY (added_by) REFERENCES users (username) ON DELETE CASCADE

The OP has already mentioned foreign keys and how they want to find another variation without foreign keys to do the same thing — Martin, Feb 09 '19 at 13:00

DELETE data in multiple tables mySQLi Prepared Statement efficiently

2 Answers2

You can use MySQL joins; inner join (or left join)

Also...

You can use MySQL joins; `inner join` (or `left join`)