CODEWITHSUNDEEP
phpmysqljoininner-join
Shopmaster
Shopmaster

Reputation: 94

UPDATE Query from a SELECT Join

I know this is probably simple and I'm just missing something but I can't seem to figure this out.

I can't get the variable in the UPDATE statement. If I put in the user_id it works so somehow I'm not passing the variable off.

$query = "SELECT accounts.username, email_activate.email, accounts.user_id FROM accounts INNER JOIN email_activate ON accounts.user_id = email_activate.user_id WHERE dt_welcome = '0000-00-00 00:00:00' AND dt_start <= DATE_SUB(NOW(), INTERVAL 3 DAY)";
$result = mysql_query($query) or die(mysql_error());
while ($row = mysql_fetch_array($result)) {


$message =  "Hello <b>".$row['username']."</b>, <br/><br/>";

$to = "".$row['email']."";

$result2 = mysql_query("UPDATE email_activate SET dt_welcome = NOW() WHERE user_id = ".$row['user_id']." ");

[other mail stuff .....]

}

Again, I know it's probably something simple.

Thanks!

Upvotes: 0

Views: 50

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269923

Why are you using two queries and a loop, when you can do this all in one query?

UPDATE email_active ea JOIN
       accounta a
       ON a.user_id = ea.user_id
    SET dt_welcome = NOW()
WHERE dt_welcome = '0000-00-00 00:00:00' AND
      dt_start <= DATE_SUB(NOW(), INTERVAL 3 DAY);

EDIT:

The problem with your query is that you need single quotes around the user id:

UPDATE email_activate
    SET dt_welcome = NOW()
    WHERE user_id = '".$row['user_id']."' "

Of course, you should be using mysqli_ or PDO and have parameterized queries. The latter would have prevented you from having this problem in the first place.

Upvotes: 1

Related Questions