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.

A
Anonymous
June 13, 2012

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


Hi

Thank you very much. Every thing all fixed.

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