0

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.

Community
  • 1
  • 1
Alan Smith
  • 17
  • 1
  • 10
  • wouldn't it be easier/better to set up a trigger in the db to delete the related items in related tables? – Jeff Jan 28 '17 at 22:20
  • @Jeff I never heard of triggers before but I just looked it up on google and found this article https://www.sitepoint.com/database-triggers-events/ which says I should use foreign key constraints. – Alan Smith Jan 28 '17 at 22:26
  • your query failed so you need to find out why and check for the real error. What you posted isn't enough code and as to which one(s) does get executed and how? Seems like you're wanting to perform a multi-query here; given what you posted. Error reporting and `mysqli_error($conn)` can be of use here. – Funk Forty Niner Jan 28 '17 at 22:29
  • @Fred-ii- They aren't all being used at the same time. It's just the various different methods I have tried. I have error reporting turned on which returns the fatal error. – Alan Smith Jan 28 '17 at 22:33
  • @AlanSmith Yes, foreign key kinda result in the same, but automaticly. Depends on your mysql version if possible. I find triggers or foreign keys simpler and cleaner compared to joined delets. But that's only my opinion. – Jeff Jan 28 '17 at 22:33
  • Can you post the code where you create the connection? Do you use mysqli or PDO? – Jannes Botis Jan 28 '17 at 22:43
  • Try adding `ON DELETE CASCADE` on the `FOREIGN KEY` references? Deleting record from the parent table would automatically remove any referenced records from the related tables. – Dhruv Saxena Jan 28 '17 at 22:43
  • @JannesBotis I use mysqli...I know I should use PDO but I'll get round to that. The connection is fine though it has been used in plenty of other functions. – Alan Smith Jan 28 '17 at 22:52
  • @Jeff I think I take a better look at your suggestion. What I have just read of it so far seems a lot more robust than what I've been trying. – Alan Smith Jan 28 '17 at 22:52

1 Answers1

2

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)?

stefan23
  • 46
  • 3
  • I changed it to LEFT JOIN and the query executed as expected when I inserted the user_id directly, rather than through a variable. Trying with the $user_id variable I receive an error "Unknown column '$user_id' in 'where clause'" The contents of var_dump($user_id) is int(30) – Alan Smith Jan 28 '17 at 23:42
  • actually you know what, I was messing around with it and had the variable $user_id in the query rather than binding the param. Changed it now and this works. Changing from INNER JOIN to LEFT worked. Thanks. – Alan Smith Jan 28 '17 at 23:46