CODEWITHSUNDEEP
phpmysql
Patrick Tannoury
Patrick Tannoury

Reputation: 93

MySQL (PHP) does not order Array according to SQL query

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

Upvotes: 1

Views: 105

Answers (1)

Salman Arshad
Salman Arshad

Reputation: 272076

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

Upvotes: 1

Related Questions