0

I want to make a cronjob which will execute every hour, select from two tables and if record isn't found in both tables and is older than 2 hours -> delete it.

So far I've build this simple delete statement but I realize that this will delete all records older than 2 hours because I don't check second table. So I need a bit help with how exactly I can select and if() then delete..

<?php
     $servername = "localhost";
     $username = "";
     $password = "";
     $dbname = "";
try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);



    $select = "SELECT * 
               FROM orders
               LEFT JOIN payments ON orders.order_id = payments.orderID
               WHERE orders.created_at < DATE_SUB( CURDATE( ) , INTERVAL 1 HOUR ) 
              AND payments.processed < 1";

    $conn->exec($select);

    if($select > 0)
        // sql to delete a record
        $sql = "delete from `orders` where ...)";

        $conn->exec($sql);
    }
catch(PDOException $e)
{
    echo $sql . "<br>" . $e->getMessage();
}
?>

Obviously I had a problem with construction if condition part for deletion. Can you help me a bit with this?

S.I.
  • 3,250
  • 12
  • 48
  • 77
  • What is the condition for you to know that a record is not in both tables ? – Anwar Sep 13 '16 at 09:15
  • Please next time try to narrow your question a certain problem. Stack Overflow is not a free debugging service. – Your Common Sense Sep 13 '16 at 09:16
  • You can use `id` to delete the record from your query. – Virb Sep 13 '16 at 09:16
  • You could try subquery (subselect), something like delete from X where DELETE FROM xxxxx AS x WHERE x.xxxx = something AND x.someotherthing IN (select from maybe.tmp) – Denis Solakovic Sep 13 '16 at 09:18
  • The condition is `AND payments.processed < 1` if is less than 1 this means that record isn't in `payments` table and should be deleted – S.I. Sep 13 '16 at 10:05

0 Answers0