CODEWITHSUNDEEP
phpmysqljoin
user961882
user961882

Reputation: 237

php sql update join

Just learning about sql joins and things, and I have a question.

Can you JOIN on an update query? Or is it only for select ones. Because I have this code;

$five_ago = date("Y-m-d H:i:s", strtotime("$now - 5 mins"));

$sql_result23 = mysql_query("SELECT * FROM usersonline WHERE timestamp < '$five_ago'", $db);
while ($rs23 = mysql_fetch_array($sql_result23)) { 
    mysql_query("UPDATE users SET status='gone' WHERE id ='$rs23[userID]'"); 
}

It picks out from the usersonline table all the ones that are older than 5 minutes, then finds them in the users table and updates their record.

I'm not a JOIN wizard or anything but i think a join would simplify that. Can someone clarify this?

Upvotes: 6

Views: 6247

Answers (4)

David B&#233;langer
David B&#233;langer

Reputation: 7438

I will introduce you to the command IN.

Example :

WHERE id IN(1,2,3,4,5,6)

What you gonna do here ? First. Create an array.

$five_ago = date('Y-m-d H:i:s', strtotime("$now - 5 mins"));

$Array = array();

$sql_result23 = mysql_query('SELECT * FROM `usersonline` WHERE `timestamp` < "'.$five_ago.'"', $db);
while ($rs23 = mysql_fetch_assoc($sql_result23)){ 
    $Array[] = $rs23['userID'];
}

mysql_query('UPDATE `users` SET `status`= "gone" WHERE `id` IN ('.join(',', $Array).')');

Upvotes: 0

Michael Berkowski
Michael Berkowski

Reputation: 270637

Yes, you can JOIN in an UPDATE statement, but I would probably use the IN () subquery as suggested elsewhere, as I find the syntax more straightforward than the awkward JOIN below:

UPDATE users 
  JOIN usersonline ON users.id = usersonline.userid
  SET users.status='gone'
WHERE usersonline.timestamp < DATE_SUB(NOW(), INTERVAL 5 MINUTE);

Note also the use of MySQL's own DATE_SUB() so you don't have to handle that in PHP beforehand.

Upvotes: 2

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115550

Using IN:

UPDATE users 
SET status='gone' 
WHERE id IN 
      ( SELECT userID 
        FROM usersonline 
        WHERE timestamp < '$five_ago'
      )

Using JOIN:

UPDATE users 
  JOIN usersonline 
    ON usersonline.userID = users.id
SET users.status='gone' 
WHERE usersonline.timestamp < '$five_ago'

Using EXISTS:

UPDATE users 
SET status='gone' 
WHERE EXISTS  
      ( SELECT *
        FROM usersonline 
        WHERE timestamp < '$five_ago'
          AND userID = users.id
      )

You could also skip the 5 minutes ago calculations in PHP and let the MySQL engine do that work, with:

WHERE timestamp < NOW() - INTERVAL 5 MINUTE

Upvotes: 8

geilt
geilt

Reputation: 805

You mean like this??

UPDATE users, usersonline 
SET users.status='gone' 
WHERE users.id ='$rs23[userID]' 
AND usersonline.timestamp < '$five_ago';

Should work well.

Upvotes: 0

Related Questions