Skip to main content
A
Anonymous
June 12, 2012
Question

Php deleting multiple table using join

  • June 12, 2012
  • 2 replies
  • 1866 views

Hi

I have three tables called  Users, Photolevel and  Photostatus.  In the Users table I have  an activekey colunm which is 1 or 0. When its 1 the user is not active when 0 the user is active.

I would like to delete all unactive users from the Users database and from Photolevel , Photostatus.

I have tried a table join with delete which is not working. can any one help.

Photolevel.useid and Photostatus.useid should join and be delete to the Users table where activekey is 1.

Heres the code.


$dbclean=mysql_query("DELETE Users.id, Users.username, Users.surname, Users.email, Users.gender, Users.age, Users.password,
Users.country, Users.city, Users.profilepic,

Users.activecode, Users.activekey, Users.DOB, Users.Date, Photolevel.pid, Photolevel.useid,

Photolevel.genre, Photolevel.start, Photolevel.end, Photostatus.stuid, Photostatus.useid, Photostatus.vgenre

FROM Users, Photolevel, Photostatus WHERE

Users.id = Photolevel.pid AND

Photolevel.pid = Photostatus.stuid AND

Users.activekey='1'");

This topic has been closed for replies.

2 replies

B
bregent
Participating Frequently
June 12, 2012

>I have three tables called  Users, Photolevel and  Photostatus.

>In the Users table I have  an activekey colunm which is 1 or 0.

>When its 1 the user is not active when 0 the user is active.

That's not really logically correct. If the column is called activekey, then 1 should be active and 0 inactive. The boolean true should express the true state of the column name. If you called it inactivekey, then 1 would be inactive and 0 would be active.

B
bregent
Participating Frequently
June 12, 2012

First of all, you don't specify columns in a delete statement. An SQL Delete will delete the entire row. Next, although it's possible to delete from multiple tables in a single statement, it can be risky. Use three independent delete statements instead.

A
Anonymous
June 12, 2012

Hi

I would have reffered to delete table by table but I need to know which user id to delete on Photolevel and Photostatus which is

avaliable through the Users table.

activekey is like a colunm like username etc the only difference is if the user is not active. I update the activekey on their column in the

database as 1 and if their active I update it to 0. Which I know is not logical correct but  to difficult to fix at the moment many users already

in database.

I would like to be able to delete every user hows activekey is 1 also deleting any data with their useid on the Photolevel table and Photostatus table.

Which both have useid columns.

Thanks.

B
bregent
Participating Frequently
June 12, 2012

A simple method is:

Delete from Photostatus where stuid in (select id from Users where activekey = '1')

Delete from Photolevel where pid in (select id from Users where activekey = '1')

Delete from Users where activekey='1'

Obviously you need to test this thoroughly before deploying to production.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices