stack stack - 6 months ago 10
MySQL Question

How can I check an other table before updating?

I have these two tables:

// users
+----+-------+-----------------------+--------+
| id | name | email | active |
+----+-------+-----------------------+--------+
| 1 | peter | peter12@hotmail.com | NULL |
| 2 | jack | most_wanted@gmail.com | NULL |
| 3 | john | john_20016@yahoo.com | NULL |
+----+-------+-----------------------+--------+

// activate
+----+---------+---------------------+
| id | post_id | random_string |
+----+---------+---------------------+
| 1 | 2 | fewklw23523kf |
+----+---------+---------------------+


Also I have an URL like this:

http://example.com/activate.php?str=fewklw23523kf





All I'm trying to do:


  • Comparing
    GET['str']
    with
    random_string
    column from
    activate
    table

  • Checking
    active
    column for
    NULL
    where
    id = post_id
    .



And then (if there is matched row) set
1
the
active
column from
users
table. How can I do that?




$str = $_GET['str'];

$stm = $db_con->prepare( "UPDATE users SET active = 1
WHERE ( SELECT 1 FROM activate WHERE random_string = ? ) t AND
( SELECT 1 FROM users WHERE t.post_id = id AND
active IS NULL ) ");

$stmt->execute(array($str));


My query doesn't work as expected.

Answer

You can use join

UPDATE users 
INNER JOIN activate on activate.post_id = user.id
SET active = 1        
WHERE activate.random_string = ? 
AND user.active IS NULL;
Comments