CODEWITHSUNDEEP
phpmysql
Alan Smith
Alan Smith

Reputation: 17

Delete from multiple tables with SQL in PHP

So I am trying to deleting rows from multiple tables. I have looked at the following questions but none of them seem to solve my issue. No matter what I try I keep getting the following error:

Fatal error: Call to a member function bind_param() on a non-object

Here are some of the questions I have looked at to try help me before I asked a new question.

Delete with Join in MySQL

Deleting using LEFT JOIN

Deleting rows from multiple tables in MySQL

MySQL delete row from multiple tables

Basically what I'm trying to make is a function that allows the user to delete their account. Their account has information in 4 different tables and each table has a common 'id' but named differently.

Before I go on I should say that it might be the case that the user doesn't have any information stored in one of the tables. Don't know if this is why the error is thrown

Here is my table structure:

table users

user_id | firstname | surname 
-------------------------------
 10     | Joe       | Bloggs
 16     | Jane      | Doe


table pets

pet_id  | pet_user_id | pet_name 
-------------------------------
 1      | 10          | Rover
 2      | 16          | Jess

table report

report_id | rep_user_id | rep_content 
-------------------------------
 1        | 10          | Hello World
 2        | 16          | Goodbye World

table user_files

file_id   | file_user_id| file 
-------------------------------
 1        | 10          | agreement.pdf
 2        | 16          | cv.docx

So say I want to delete the user with the ID of 16 from each of these tables. The following are what I have tried so far.

First:

$stmt = $conn->prepare('DELETE * FROM users, pets, report, user_files WHERE user_id = ?, pet_user_id = ?, rep_user_id = ?, file_user_id = ? ');

Second:

$stmt = $conn->prepare('DELETE users.*, pets.*, reports.*, user_files.* FROM users LEFT JOIN pets ON pets.pet_user_id=users.user_id LEFT JOIN report ON report.rep_user_id=users.user_id LEFT JOIN user_files ON user_files.user_id=users.user_id WHERE user_id = ?');

Third:

$stmt = $conn->prepare('DELETE users, pets, report, user_files FROM 
  table1 as users 
  INNER JOIN  table2 as pets on users.user_id = pets.pet_user_id
  INNER JOIN  table3 as report on users.user_id=report.rep_user_id
  INNER JOIN  table4 as user_files on users.user_id=user_files.user_id
  WHERE  users.user_id= ?');

The ? is bound using $stmt->bind_param('i', $user_id); where $user_id is being defined from a session variable.

Upvotes: 0

Views: 4242

Answers (1)

stefan23
stefan23

Reputation: 46

I think your third statement should work, but I would built it with left joins.

Please try the following SQL Update:

DELETE users, pets, report, user_files 
FROM users
LEFT JOIN pets ON (pets.pet_user_id=users.user_id)
LEFT JOIN report ON (report.rep_user_id=users.user_id)
LEFT JOIN user_files ON (user_files.file_user_id=users.user_id)
WHERE users.user_id=?;

You should try to execute this statement (with parameter "16" as "user_id") on your MySQL console and check if it works.

If not, I assume a problem with your binded variable $user_id. Can you provide us the output of a var_dump($user_id)?

Upvotes: 2

Related Questions