SQL ping delete from different tables single query

[sspoke]

Hello man,

 

I'm trying to make this process in a single query without doing like

 

 

SELECT * FROM users WHERE ping < DATE_SUB(CURRENT_TIMESTAMP(), INTERVAL 15 SECOND)
Go threw each $row['id']
mysql_query("DELETE FROM users WHERE userId = $row['id']");
mysql_query("DELETE FROM chats WHERE userId = $row['id']");
mysql_query("DELETE FROM chats WHERE randomUserId = $row['id']");

 

I already optmized to skip some php load of users table but i can't seem to link it up to chat's table since chat table also has randomUserId and userId both being possible candidates for deleting.

 

Got ATM

mysql_query("DELETE FROM users WHERE ping < DATE_SUB(CURRENT_TIMESTAMP(), INTERVAL 15 SECOND)");

 

how i join chats table too is my question for deleting in single query.

 

 

Thank you 

[sspoke]

Came up with this

 

DELETE FROM users,chats
WHERE users.ping < DATE_SUB(CURRENT_TIMESTAMP(), INTERVAL 15 SECOND)
OR users.id = chats.userId
OR users.id=chats.randomUserId

 

but it doesn't look right.

Because note the users would exist but the chats might either not have userId or randomUserId or maybe both missing.

[fenway]

Then you'd need a left join.

[sspoke]

can someone do this for me just saying i need a left join won't help me that much sorry if this makes anyone angry but i am one of these simple thousand query coder who wishes that sometime someone can provide me with something awesome as a one liner..

 

btw just realized that there is another query i have to run to delete from

mysql_query("DELETE FROM typing WHERE id = $row['id']");

 

which makes it now

 

mysql_query("DELETE FROM users WHERE userId = $row['id']");
mysql_query("DELETE FROM chats WHERE userId = $row['id']");
mysql_query("DELETE FROM chats WHERE randomUserId = $row['id']");
mysql_query("DELETE FROM typing WHERE id = $row['id']");

 

3 different table accessors will that require 2 left joins?

[sspoke]

DELETE users.*, chats.*, typing.*
FROM users u, chats c, typing t
WHERE u.userId = $row['id']
AND c.userId = $row['id']
AND c.randomUserId = $row['id']
AND t.id = $row['id']

 

like that? but some places *could* be already empty like typing/chats.

 

i've looked into LEFT JOIN seems it only joins one other table how can I do 3 at once?

To delete data from all 3 tables based on that one row[id] variable

[fenway]

Then you haven't looked enough.

 

DELETE u.*, c.*, c2.*, t.*
FROM users u 
LEFT JOIN chats c ON c.userId = $row['id']
LEFT JOIN chats c2 ON c.randomUserId = $row['id']
LEFT JOIN typing t ON t.id = $row['id']
WHERE u.userId = $row['id']

[sspoke]

Thanks fenway you're awesome, very smart man. Works like a charm!

 

Although if a user's userId is already deleted all the other queries fail but i doubt that will happen any other way except if I edit the database by hand.

[sspoke]

You did mean

 

DELETE u.*, c.*, c2.*, t.*

FROM users u

LEFT JOIN chats c ON c.userId = $row['id']

LEFT JOIN chats c2 ON c2.randomUserId = $row['id']

LEFT JOIN typing t ON t.id = $row['id']

WHERE u.userId = $row['id']

 

right? c2.randomUserId not  c.randomUserId  ?