php - 如果只有一行，SQL 拒绝删除行

Question

我有一个临时上传表，当我尝试从临时表中删除上传的文件时，它会删除除一行之外的所有行。请帮忙，我不知道为什么会发生这种情况，我已经检查了我所有的 sql 和 PHP。

delete from `message_attachments_temp` 
                             where 
                            `from` = 1 AND 
                            `to`   = 2

出于某种原因，SQL 将删除所有内容，但留下一行。有谁知道这可能是什么原因造成的。

这是我的 php 函数，用于提交消息上传并将它们从临时上传表中删除。

 else if($rtype == 23) {/* submit message */
    $friends_username = isset($_REQUEST['to']) ? $Wall->filterData($_REQUEST['to']) : "";
    /* get link preview data from ajax */
    $linkPicture = isset($_POST['linkPicture']) ? trim($_POST['linkPicture']) : "";
    $linkTitle = isset($_POST['linkTitle']) ? trim($_POST['linkTitle']) : "";
    $linkDescription = isset($_POST['linkDescription']) ? trim($_POST['linkDescription']) : "";
    $linkURL = isset($_POST['linkURL']) ? trim($_POST['linkURL']) : "";
    $linkVideoID = isset($_POST['linkVideoID']) ? trim($_POST['linkVideoID']) : "";
    $linkVideoProvider = isset($_POST['linkVideoProvider']) ? trim($_POST['linkVideoProvider']) : ""; 
    if(isset($logged_user_id) && isset($friends_username)) {/* if the user is logged in and there is a friend id then */
        $queryed = trim(strip_tags(htmlspecialchars($_POST["sendMessageUID"])));
        function no_magic_quotesed($queryed) {
            $dataed = explode("\\",$queryed);
            $cleaneded = implode("",$dataed);
            return $cleaneded;
        }
        $checkfor_attachments = mysqli_query($asktippyDatabaseConnection, " SELECT * from `message_attachments_temp` 
                                                  where 
                                                 `from` = ".mysqli_real_escape_string($asktippyDatabaseConnection, $logged_user_id)." and 
                                                 `to`   = ".mysqli_real_escape_string($asktippyDatabaseConnection, strip_tags($friends_username))." 
                                                  order by `id` asc");
        if(mysqli_num_rows($checkfor_attachments) == 0 && $queryed == "") {/* if no temporary attachments are found then */
            echo "empty";
            exit;
        }
        $date = mysqli_real_escape_string($asktippyDatabaseConnection, strtotime(date("Y-m-d H:i:s")));
        $group_id = $Wall->get_group_nbr(mysqli_real_escape_string($asktippyDatabaseConnection, strip_tags($_POST['to'])), mysqli_real_escape_string($asktippyDatabaseConnection, strip_tags($_POST["from"])));
        mysqli_query($asktippyDatabaseConnection, "insert into `messages` values( '', 
                                                    '".mysqli_real_escape_string($asktippyDatabaseConnection, strip_tags($_POST['to']))."', 
                                                    '".mysqli_real_escape_string($asktippyDatabaseConnection, strip_tags($logged_user_id))."', 
                                                    '".mysqli_real_escape_string($asktippyDatabaseConnection, no_magic_quotesed($queryed))."', 
                                                    '0', 
                                                    '0', 
                                                    '".mysqli_real_escape_string($asktippyDatabaseConnection, $date)."', 
                                                    '0',
                                                    '".$group_id."')");     
        $mesgid = mysqli_insert_id($asktippyDatabaseConnection);
            ### attach
        error_log(print_r('number of temporary attachments: '.mysqli_num_rows($checkfor_attachments), true));
            if(mysqli_num_rows($checkfor_attachments) > 0) {/* if there are temporary message attachments then */
                while($getall_attachments = mysqli_fetch_array($checkfor_attachments)) {
                    mysqli_query($asktippyDatabaseConnection, "insert into `message_attachments` values( '', 
                                                                            '".$mesgid."', 
                                                                            '".$getall_attachments["file"]."', 
                                                                            '0', 
                                                                            '0', 
                                                                            '".strip_tags(time())."',
                                                                            '".$linkTitle."', 
                                                                            '".$linkDescription."', 
                                                                            '".$linkURL."', 
                                                                            '".$linkPicture."', 
                                                                            '".$linkVideoProvider."', 
                                                                            '".$linkVideoID."',                                                                     
                                                                            '0',
                                                                            ''
                                                                            )");
                }
                mysqli_query($asktippyDatabaseConnection, "delete from `message_attachments_temp` 
                             where 
                            `from` = ".mysqli_real_escape_string($asktippyDatabaseConnection, $logged_user_id)." AND 
                            `to`   = ".mysqli_real_escape_string($asktippyDatabaseConnection, strip_tags($friends_username))."");


error_log(print_r("delete from `message_attachments_temp` 
                             where 
                            `from` = ".mysqli_real_escape_string($asktippyDatabaseConnection, $logged_user_id)." AND 
                            `to`   = ".mysqli_real_escape_string($asktippyDatabaseConnection, strip_tags($friends_username))."", true));
            }
            ### attach
            if(mysqli_real_escape_string($asktippyDatabaseConnection, strip_tags($_POST['newmessage_load_prev_chat'])) == 1) {
                $check_all_available_conversations = mysqli_query($asktippyDatabaseConnection, "SELECT * FROM `messages` where 
                                                                 `touser` = '".mysqli_real_escape_string($asktippyDatabaseConnection, $logged_user_id)."' and 
                                                                  `fromuser` = ".mysqli_real_escape_string($asktippyDatabaseConnection, strip_tags($friends_username))." and 
                                                                  `deleted` = '0' || `touser` = ".mysqli_real_escape_string($asktippyDatabaseConnection, strip_tags($friends_username))." and 
                                                                  `fromuser` = ".mysqli_real_escape_string($asktippyDatabaseConnection, $logged_user_id)." and `outdeleted` = '0' order by `mesg_id` asc");
            } else {
                $check_all_available_conversations = mysqli_query($asktippyDatabaseConnection, "SELECT * from `messages` where 
                                                                 `touser` = '".mysqli_real_escape_string($asktippyDatabaseConnection, $logged_user_id)."' and 
                                                                 `fromuser` = '".mysqli_real_escape_string($asktippyDatabaseConnection, strip_tags($friends_username))."' and 
                                                                 `deleted` = '0' || `touser` = '".mysqli_real_escape_string($asktippyDatabaseConnection, strip_tags($friends_username))."' and 
                                                                 `fromuser` = '".mysqli_real_escape_string($asktippyDatabaseConnection, $logged_user_id)."' and `outdeleted` = '0' order by `mesg_id` desc limit 1");
            }
            while($get_all_available_conversations = mysqli_fetch_array($check_all_available_conversations)) {
                $check_friends_full_details = mysqli_query($asktippyDatabaseConnection, "select * from `users` where `mem_id` = ".mysqli_real_escape_string($asktippyDatabaseConnection, strip_tags($get_all_available_conversations['fromuser']))."");
                while($get_friends_full_details = mysqli_fetch_array($check_friends_full_details)) {
                    $avatar = $Wall->get_user_avatar($get_friends_full_details['mem_id']);
                    if($get_friends_full_details['mem_id'] == $logged_user_id) { ?>
                        <div class="message-popup-convers-item2">
                            <div class="message-popup-convers-content998">
                                <div class="message-popup-convers-text998">
                                <?php if($get_all_available_conversations['message']) { ?>
                                     <?php echo nl2br(strip_tags(stripslashes($get_all_available_conversations['message'])));?>  
                                <?php } ?>
                                </div><!--message-popup-convers-text-->   
                            </div><!--message-popup-convers-content-->
                        </div><!--message-inbox-item-->
                        <div class="message-popup-convers-item2-content">
                            <div class="message-popup-convers-content99820">
                                <?php echo get_posts_html($get_all_available_conversations['mesg_id'], $logged_user_id); ?>
                            </div><!--message-popup-convers-content-->
                        </div><!--message-inbox-item-->
<?php                   $last_message_time = strip_tags(stripslashes($Wall->timeFormat($get_all_available_conversations['datesent'])));
                    } else { ?>
                        <div class="message-popup-convers-item">
                            <img class="message-popup-convers-avatar9982" src="<?php echo $avatar; ?>" alt="">
                            <div class="message-popup-convers-content">
                                <div class="message-popup-convers-text">
                                <?php if($get_all_available_conversations['message']) { ?>
                                     <?php echo nl2br(strip_tags(stripslashes($get_all_available_conversations['message'])));?>  
                                <?php } ?>
                                </div><!--message-popup-convers-text-->   
                            </div><!--message-popup-convers-content-->
                        </div><!--message-inbox-item-->
                        <div class="message-popup-convers-item-content">
                            <div class="message-popup-convers-content20">
                                <?php echo get_posts_html($get_all_available_conversations['mesg_id'], $logged_user_id); ?> 
                            </div><!--message-popup-convers-content-->
                        </div><!--message-inbox-item-->
<?php                   $last_message_time2 = strip_tags(stripslashes($Wall->timeFormat($get_all_available_conversations['datesent'])));
                    }
                }
            }
        $Wall->insertNotificationTrack(mysqli_real_escape_string($asktippyDatabaseConnection, strip_tags($_POST["from"])), 0, 18, mysqli_real_escape_string($asktippyDatabaseConnection, strip_tags($_POST["to"]))); // sent in message
    } else {
        echo "<div id='message_info'>Sorry, something went wrong.</div>";
    }
}

Answer 1

如果您要截断表并且您有外键，请尝试以下操作:

SET FOREIGN_KEY_CHECKS = 0; 
TRUNCATE table $table_name; 
SET FOREIGN_KEY_CHECKS = 1;

如果您要删除特定行，请尝试选择所有 id 并删除这些 id，希望这会起作用

DELETE FROM `table_name` WHERE `id` IN (your query);