Php deleting multiple table using join

[Guest]

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'");

[bregent]

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.

[Guest]

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.

[bregent]

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.

[Guest]

Hi

Thanks alot this seams like the way to solve the problem.

$delete_photostatus=mysql_query("Delete from Photostatus where stuid in(Select id from Users where activekey='1')");

I have never used " in " syntax before in a query. Is the code above a valid way to incorparate the pseudo you provided?

Thanks again for all the help.

all the best.

[Guest]

Hi

I recent made some new modification but scared this could delete User data from database. I added where Photolevel.genre=Photolevel.genre to keep

it from deleting any user records could you have a look and tell what you think.

Heres the code:

$sqlgo = mysql_query("DELETE FROM Users.id, Users.activekey, Photolevel.pid, Photolevel.useid, Photolevel.genre, Photolevel.start, Photolevel.end FROM Photolevel INNER JOIN

Users ON Photolevel.useid=Users.id and Users.activekey='1' and Photolevel.genre='Photolevel.genre'");

Thanks.

[bregent]

>$sqlgo = mysql_query("DELETE FROM Users.id, Users.activekey, Photolevel.pid, Photolevel.useid, Photolevel.genre, Photolevel.start, Photolevel.end FROM Photolevel INNER >JOIN

>Users ON Photolevel.useid=Users.id and Users.activekey='1' and Photolevel.genre='Photolevel.genre'");

Again, your syntax is incorrect. You do not specifiy a column list in a delete statement. If you want to delete rows from a table, you specify the table name after the DELETE operator. You can then qualify which rows to delete using joins in the FROM clause and/or conditions in the WHERE clause.

$sqlgo = mysql_query("DELETE Photolevel FROM Photolevel INNER JOIN Users ON Photolevel.useid=Users.id and Users.activekey='1' ");

This statement yields the same results as the one I provided earlier that used the subquery.

>and Photolevel.genre='Photolevel.genre'");

I'm not sure what you are trying here. A column always equals itself so that condition will always evaluate as TRUE.

[Guest]

Hi

Thank you very much. Every thing all fixed.

[bregent]

You're welcome Tony.

[bregent]

>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.