SQL refuses to delete row if there is only one row

Question

I have a temporary uploads table and when I try to delete uploaded files from the temporary table it deletes all rows except for one row. Please help I don't know why this is happening I have checked all of my sql and PHP.

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

For some reason SQL will delete everything but leave one row behind. Does anyone know what might be causing this.

Here is my php function that submits the message uploads and removes them from the temporary uploads table.

 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>";
    }
}

can you give a screenshot of how your table looks like please ? — Kishen Nagaraju, May 16 '18 at 08:11
If you want to delete all rows why don't you do `TRUNCATE message_attachments_temp` ? — apokryfos, May 16 '18 at 08:11
I only need to delete rows whose files have been submitted once I hit the submit button for messages. and just for testing purposes I have tried TRUNCATE and even that's not working. One row always remains in the table. — Romeo Hennessy, May 16 '18 at 08:14
Does it return any error? Do you have any foreign key constraints that refers that table? Maybe on fields from/to? — Jack Skeletron, May 16 '18 at 08:16
Hmm foreign key constraints? Perhaps that might be the issue. Could that prevent one row from being deleted. When there is more than one row it will delete all rows except for one. I need it to delete all of the rows that match the query. — Romeo Hennessy, May 16 '18 at 08:20
No errors are returned. Everything works fine. The issue is that it refuses to delete one row. But if there is more than one row then it will delete all rows except one. This is really strange behavior. I think the issue is in the way my table is setup maybe? — Romeo Hennessy, May 16 '18 at 08:22
Your script is wide open to [SQL Injection Attack](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) Use [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) in either the `MYSQLI_` or `PDO` API's — RiggsFolly, May 16 '18 at 08:30
Thanks for the friendly reminder @RiggsFolly I plan on using prepared statements before going live, but the issue that I'm concerned with right now is this sql issue. I just changed my storage engine to innoDB and I'm going to take a look at the relational table. — Romeo Hennessy, May 16 '18 at 08:35
@RomeoHennessy Are you able to delete that row through phpMyAdmin GUI? — Jacob Mulquin, May 16 '18 at 08:37
@mulquin Yes I am that is the strange part. I cannot delete it from PHP but when I enter the query manually in PHPMYADMIN it works. — Romeo Hennessy, May 16 '18 at 08:42
That is bizzarre, so it deletes from PHP if to and from are not 1 and 2? — Jacob Mulquin, May 16 '18 at 08:56
Yea in PHPMYADMIN delete from `message_attachments_temp` where `from` = 1 AND `to` = 2 that works but not in PHP. It's weird. What could be the issue? — Romeo Hennessy, May 16 '18 at 09:09

score 0 · Answer 1 · answered May 16 '18 at 08:39

if you are going to TRUNCATE the table and you have foreign key, try this:

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

if you're going to delete specific rows try to select all ids and delete these ids, Hope this will work

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

SQL refuses to delete row if there is only one row

1 Answers1