php - MySQL INNER JOIN 3 个表，还可以使用 INNER JOIN 进行删除

Question

我真的很努力地完成这项任务，因此非常感谢任何帮助或指导。

表格:

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

            module_timelimit
            +------------------------------------------------+
            | upload_id | email        | expires             |
            +------------------------------------------------+
            |   1006    | me@me.com    | 2014-05-22 16:34:54 |
            |   1007    | me12@me.com  | 2015-05-20 20:34:54 |
            +------------------------------------------------+

            module_fieldvals
            -----------------------------------------+
            | upload_id | fld_id  | value            |
            -----------------------------------------+
            |   1006    | 2       | me@me.com        |
            |   1006    | 0       | mcdda8fbr        |
            |   1007    | 0       | mcdda8fbr        |
            |   1007    | 2       | me12@me.com      |
            -----------------------------------------+

好吧，我想根据过期日期的 upload_id 加入 3 个表，删除文件，发送电子邮件，然后删除所有数据。我有两个问题:

• 我似乎只能使用 fld_id = 2 连接表并按到期日期对它们进行分组。如果能够删除 fld_id = 2 和 fld_id = 0 的数据就好了，但我似乎收到了重复的电子邮件，因为 while 循环迭代了 4 次而不是 2 次。
• 我不知道如何删除数据
• 有没有一种方法可以仅使用一个 SQL 查询来完成此操作？

这是迄今为止我的代码:

            $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

检查您的表名称，因为它们在 SELECT 和 DELETE 中是不同的。

虽然从多个表中删除的格式不只一种，但主要问题是必须从 DELETE 语句中删除 GROUP BY 子句:

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