MySQL INNER JOIN 3 tables and also DELETE using INNER JOIN

Question

I'm really struggling with this task so any help or guidance will be very much appreciated.

The tables:

            module_uploads
            +--------------------------+
            | upload_id | upload_name  | 
            +--------------------------+
            |   1006    | 12.png       |
            |   1007    | 18.png       |
            +--------------------------+

            module_timelimit
            +------------------------------------------------+
            | upload_id | email        | expires             |
            +------------------------------------------------+
            |   1006    | [email protected]    | 2014-05-22 16:34:54 |
            |   1007    | [email protected]  | 2015-05-20 20:34:54 |
            +------------------------------------------------+

            module_fieldvals
            -----------------------------------------+
            | upload_id | fld_id  | value            |
            -----------------------------------------+
            |   1006    | 2       | [email protected]        |
            |   1006    | 0       | mcdda8fbr        |
            |   1007    | 0       | mcdda8fbr        |
            |   1007    | 2       | [email protected]      |
            -----------------------------------------+

Ok so I want to join the 3 tables based upon the upload_id where the expiry date has passed, delete the files, send an email and then delete all the data. I have 2 problems:

• I can only seem to join the tables using fld_id = 2 and group them by expiry date. It would be nice to be able to delete data that has fld_id = 2 and fld_id = 0, but I seemed to get duplicate emails being sent because the while loop was iterating 4 times instead of just 2.
• I can't figure out how to then delete the data
• Is there a way to do it using just one SQL query?

Here is my code so far:

            $database       = cmsms()->getDb();
            $now            = $database->DbTimeStamp(time());
            $query          = "
                            SELECT 
                                module_uploads.upload_id,
                                module_uploads.upload_name,
                                module_fieldvals.upload_id,
                                module_fieldvals.fld_id,
                                module_fieldvals.value,
                                module_timelimit.upload_id,
                                module_timelimit.email,
                                module_timelimit.expires
                            FROM
                                module_timelimit
                            INNER JOIN module_uploads
                                ON module_timelimit.upload_id = module_uploads.upload_id
                            INNER JOIN module_fieldvals
                                ON module_timelimit.upload_id = module_fieldvals.upload_id
                            WHERE module_timelimit.expires < $now AND module_fieldvals.fld_id = 2
                            GROUP BY module_timelimit.expires";

            $result         = mysql_query($query);

            // Delete the rows
            $query_deletion = "
                            DELETE cms_module_uploads_timelimit, cms_module_uploads, cms_module_uploads_fieldvals
                            FROM
                                cms_module_uploads_timelimit
                            INNER JOIN cms_module_uploads
                                ON cms_module_uploads_timelimit.upload_id = cms_module_uploads.upload_id
                            INNER JOIN cms_module_uploads_fieldvals
                                ON cms_module_uploads_timelimit.upload_id = cms_module_uploads_fieldvals.upload_id
                            WHERE cms_module_uploads_timelimit.expires < $now AND cms_module_uploads_fieldvals.fld_id = 2
                            GROUP BY cms_module_uploads_timelimit.expires";

            $delete_result  = mysql_query($query_deletion);             

            // Check if any queries failed              
            if(!$result || !$delete_result) {
                if (!$result) exit("Error - The selection query did not succeed");
                if (!$delete_result) exit("Error - The deletion query did not succeed");
            } else {

                // set up the settings for FTP to delete file
                $ftp_server = 'xxxxxxx';
                $ftpuser = 'xxxxxx';
                $ftppass = 'xxxxxx';

                // set up basic connection
                $conn_id = ftp_connect($ftp_server);

                // login with username and password
                $login_result = ftp_login($conn_id, $ftpuser, $ftppass);

                if ((!$conn_id) || (!$login_result)) {

                    echo "Error could not connect to FTP";

                } else {

                    while ($row = mysql_fetch_array($result)) {
                        $upload_id = $row['upload_id'];
                        $file = $row['upload_name'];
                        $email = $row['value'];

                        // Check if file and email exists
                        if(isset($file) && isset($email) ) {

                            $file = '/uploads/adverts/'.$file;

                            // Delete file
                            if (ftp_delete($conn_id, $file)) {
                                echo "$file deleted successfully\n";
                            } else {
                                echo "could not delete $file\n";
                            }

                            // Send email using CMSMS GCB
                            $cmsmailer->Send(); 
                        }
                    }
                }

                // close the connection
                ftp_close($conn_id);
            }

Answer 1

Check your table names because they're different in your SELECT and DELETE.

While there is more than one format to delete from multiple tables, your main issue is that you must drop the GROUP BY clause from the DELETE statement:

DELETE
FROM cms_module_uploads_timelimit, cms_module_uploads, cms_module_uploads_fieldvals
USING cms_module_uploads_timelimit
INNER JOIN cms_module_uploads
  ON cms_module_uploads_timelimit.upload_id = cms_module_uploads.upload_id
INNER JOIN cms_module_uploads_fieldvals
  ON cms_module_uploads_timelimit.upload_id = cms_module_uploads_fieldvals.upload_id
WHERE cms_module_uploads_timelimit.expires < $now
  AND cms_module_uploads_fieldvals.fld_id = 2