CODEWITHSUNDEEP
phpmysql
Joshua Greenhough
Joshua Greenhough

Reputation: 81

Update another table from one table value with timestamps

I am trying to update a table called "Orders" when a row on another table (called "store") is more than 30 days old.

When a value on "store" is created, it is given a timestamp of the time it was added to the table. But as soon as that value turns over 30 days old, I need it to get the ID (which is a column that has a unique ID per row) of that cell, and check the table called "orders" for that ID, and update another column where that ID is present.

So far, I've written this, but it is clearly wrong:

<?php

    include ('connectdb.php'); 

    $query = $db->query("SELECT * FROM store WHERE open_time < (NOW()- INTERVAL 30 DAYS)");
    $update = $db->prepare("UPDATE orders SET notified=-1 WHERE unique_id=$query");

    $db = null;

?>

Upvotes: 0

Views: 82

Answers (1)

Aykut &#199;evik
Aykut &#199;evik

Reputation: 2088

If the tables are on the same database just use a subselect in the WHERE clause, like here: MYSQL UPDATE with IN and Subquery

Otherwise aggregate the IDs into an array and concatenate them in the WHERE clause with implode. Don't forget to quote every ID.

Also, try to select only the columns you need (unique_id) and not all at once (*).

Upvotes: 1

Related Questions