title: MySQL delete across multiple tables
body: <p>I am working on a cron script (in PHP PDS for a MySQL db) that will delete data older than 30 days from the database. Ive tried to do joining and on delete cascaded on my tables and statements but they haven't worked out so I went with a simpler approach here to get it working however I know this isn't the most processor effective method.</p>
  
  <p>Please note that some of these tables (especially the links table) have hundreds of thousands of rows so that's why I wanted to be a little more clever than what I have here.</p>
  
  <p>The table structure is easy, there is a table key table that has a time stamp and an id. That id is repeated in all tables as <code>tablekey_id</code>. </p>
  
  <p>My current cron job is as follows.</p>
  
  <pre><code>/* Cron script to delete old data from the database. */
  if (@include dirname(dirname(dirname(__FILE__))) . '/dbconn/website_connections.php') {
      $conn = $connectionFromOtherFile;
      $keyTable = 'table_key';
      $linksTable = 'links';
      $domainsTable = 'link_domains';
      $terms = 'searched_domains';
  
      $query = $conn-&gt;query("SELECT `id` FROM `$keyTable` WHERE `timestamp` &lt; (NOW() - INTERVAL 30 DAY)");
      $query-&gt;setFetchMode(PDO::FETCH_ASSOC);
  
      while($row = $query-&gt;fetch()) {
          $conn-&gt;exec("DELETE FROM `$linksTable` WHERE `tablekey_id` = $row[id]");
          $conn-&gt;exec("DELETE FROM `$domainsTable` WHERE `tablekey_id` = $row[id]");
          $conn-&gt;exec("DELETE FROM `$terms` WHERE `tablekey_id` = $row[id]");
          $conn-&gt;exec("DELETE FROM `$keyTable` WHERE `id` = $row[id]");
      }
  }
  </code></pre>
  
  <p>Is there any way to get this into one statement? Please and thank you for the assistance.</p>
  
  <p>EDIT: Heres what I ended up with.</p>
  
  <pre><code>/* Cron script to delete old data from the database. */
  if (@include dirname(dirname(dirname(__FILE__))) . '/dbconn/website_connections.php') {
      $conn = $connectionFromOtherFile;
      $keyTable = 'table_key';
      $linksTable = 'links';
      $domainsTable = 'link_domains';
      $terms = 'searched_domains';
           $delete = $conn-&gt;prepare("DELETE tablekey, linktable, domaintable, searched
                              FROM `$keyTable` AS tablekey
                              LEFT OUTER JOIN `$linksTable` AS linktable on tablekey.id = linktable.tablekey_id
                              LEFT OUTER JOIN `$domainsTable` AS domaintable on tablekey.id = domaintable.tablekey_id
                              LEFT OUTER JOIN `$terms` AS searched on  tablekey.id = searched.tablekey_id
                              WHERE tablekey.id = :tablekey_id");
  
      $query = $conn-&gt;query("SELECT `id` FROM `$keyTable` WHERE `timestamp` &lt; (NOW() - INTERVAL 30 DAY)");
      $query-&gt;setFetchMode(PDO::FETCH_ASSOC);
  
      while($row = $query-&gt;fetch()) {
          $delete-&gt;execute(array('tablekey_id' =&gt; $row['id']));
      }
  }   
  </code></pre>