1

I'm having a problem when I'm retrieving conversations from my MySQL database. When I run the query on phpMyAdmin, it gives me the order;

  1. 41 (2021-07-24)
  2. 371 (2021-07-20)
  3. 361 (2021-07-19)
  4. 351 (2021-07-17)
  5. 21 (2021-07-11)

But when I run my code in PHP, I get this order;

  1. 371 (2021-07-20)
  2. 361 (2021-07-19)
  3. 351 (2021-07-17)
  4. 41 (2021-07-24)
  5. 21 (2021-07-11)

Heres the code in PHP:

<?php
    include 'config.php';
    
    $requestId = $conn->real_escape_string($_GET['requestid']);
    $requestIdPost = $conn->real_escape_string($_POST['requestid']);

    if($requestId == 1){ //Find all unique chat_id's
        $id = $conn->real_escape_string($_GET['userid']);
        $chatid = $conn->real_escape_string($_GET['chatid']);
        $sender = $conn->real_escape_string($_GET['sender']);
        
        $sql = "SELECT DISTINCT chat_id FROM chatdb WHERE (account_id='$id' OR counterpart='$id') 
        AND active=1 ORDER BY chat_time DESC";

        $result = mysqli_query($conn,$sql); 
        $rows = mysqli_num_rows($result); 
        if(mysqli_num_rows($result) > 1){
            for($i=0; $i < $rows; $i++){
                $rs=mysqli_fetch_array($result);
                $chatid = $rs['chat_id'];
                $chatid = utf8_encode($chatid);
                $points[$i][0]=($chatid);
            }
            echo (json_encode($points));
        }
        else if(mysqli_num_rows($result) == 1){
            $rs=mysqli_fetch_array($result);
            $chatid = $rs['chat_id'];
            $chatid = utf8_encode($chatid);
            echo json_encode($chatid);
        }
        else{
            echo (0);
        }
    }
?>

Does anyone know why the array created by my For-loop messes up the order of the chat-ids? Keep in mind that the array (if #rows > 1) is echoed back to an ajax request in javascript:

function populateChatList(){
    var loginId = getCookie("a_user");
    var newArrayLoginId = loginId.split(',');
    var accountid = newArrayLoginId[0];
    $.ajax(
        {
            url: './PHP/inbox.php',
            dataType: 'text',
            method: 'GET',
            data: {
                requestid: 1,
                userid: accountid,
            },
            success: function(response){
                var response = JSON.parse(response);
                console.log(response)
                if(Array.isArray(response) == false){ //single chatbox
                    sessionStorage.setItem('c_id', `${response}`);
                    $.ajax(
                        {
                            url: './PHP/inbox.php',
                            dataType: 'text',
                            method: 'GET',
                            data: {
                                requestid: 2,
                                chatid: sessionStorage.getItem('c_id'),
                            },
                            success: function(response){
                                var query = JSON.parse(response);
                                if(query !== 0){
                                    conversationPopulate(query);
                                }
                                else{ //no messages, display no-message-svg
                                    document.getElementById('no-message-svg-wrapper').style.display = "flex";
                                }
                            },
                        }
                    );
                }
                else if(response.length > 1){ //multiple chatbox
                    response.forEach(element => {
                        $.ajax(
                            {
                                url: './PHP/inbox.php',
                                dataType: 'text',
                                method: 'GET',
                                data: {
                                    requestid: 2,
                                    chatid: element[0],
                                },
                                success: function(response){
                                    var query = JSON.parse(response);
                                    conversationPopulate(query, element);
                                },
                            }
                        );
                    });
                }
                else if(response == 0){
                    document.getElementById('noconversations').style.display = "block";
                    document.getElementById('noconversations').innerText = "Inga Konversationer"
                }
            },
        }
    );
};
2
  • If you just run $result = mysqli_query($conn,$sql); and print_r(mysqli_fetch_all($result, MYSQLI_ASSOC)); is order incorrect? The $result = mysqli_query($conn,$sql); $rows = mysqli_num_rows($result); if(mysqli_num_rows($result) > 1){ for($i=0; $i < $rows; $i++){ $rs=mysqli_fetch_array($result); seems like it could be simplified with a while loop. The utf8_encode I also don't think is needed. Commented Sep 1, 2021 at 13:28
  • It did not retrieve the query in the correct order... I'll look into the option of adopting a while loop instead of a for loop, thanks for the feedback! Commented Sep 1, 2021 at 14:03

1 Answer 1

1

You are using a column in ORDER BY that is not specified inside SELECT DISTINCT clause. MySQL should not allow this unless you're using obsolete, discouraged behavior. Use GROUP BY instead of DISTINCT so that you can use aggregate functions in ORDER BY:

SELECT chat_id
FROM chatdb
WHERE ...
GROUP BY chat_id
ORDER BY MAX(chat_time) DESC -- find max time for each chat_id group and
                             -- order the overall results by that aggregate
Sign up to request clarification or add additional context in comments.

1 Comment

This was actually great, and works perfectly! Thank you very much for the help!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.