How to order a column based on another column?

[Lukeidiot]

Hey there,

 

I am trying to order my messages by last message received instead of last message created.

 

(Live Site if you want to try a live preview: https://snaps.im) send a message to: admin

 

Here is how it is currently:

 

 

Here is my mysql table:

 

I basically need it to order exactly how imessages are stored on an iphone or phone. 

Edited March 21, 2016 by Lukeidiot

[benanamen]

Rather basic. Add ORDER BY column ASC or DESC. You have too many queries. Just use a join. You are also opening and closing php unnecessarily.

 

More importantly, you hang pictures on a wall. Next time post your code.

Edited March 22, 2016 by benanamen

[Lukeidiot]

Rather basic. Add ORDER BY column ASC or DESC

 

I know that, but I have a sql query inside of another sql query, so how do I order by the 2nd query?

[Lukeidiot]

Rather basic. Add ORDER BY column ASC or DESC. You have too many queries. Just use a join. You are also opening and closing php unnecessarily.

 

More importantly, you hang pictures on a wall. Next time post your code.

 

<?php
        $sql_messages = mysqli_query($connect, "SELECT DISTINCT group_id FROM message_group WHERE username = '$username' || recipients = '$username' ORDER BY id DESC;");
        $num_messages = mysqli_num_rows($sql_messages);
        if($num_messages == 0){ ?>
        <div id="messagegroups">
        <div align="center" style="padding: 5px;" id="nomsg">You have no messages at this time.</div>
        <div id="messagegroupholder">
        </div>
        <?php } else { ?>
        <div id="messagegroups">
           <?php while($row_messages = mysqli_fetch_assoc($sql_messages)){ ?>
           <?php
           $snm = mysqli_query($connect, "SELECT * FROM message_group WHERE group_id = '$row_messages[group_id]' ORDER BY id DESC");
           $rm = mysqli_fetch_assoc($snm);
           $nm = mysqli_num_rows($snm);


           $ur = mysqli_query($connect, "SELECT * FROM users WHERE username = '$rm[recipients]' ORDER BY id DESC;");
           $rr = mysqli_fetch_assoc($ur);


           $um = mysqli_query($connect, "SELECT * FROM messages WHERE group_id = '$row_messages[group_id]' ORDER BY id DESC;");
           $rrr = mysqli_fetch_assoc($um);


           ?>
           <div id="messagegroupholder">
            <div class="qo cj ca js-msgGroup groupclick" id="msg" data-id="<?php echo $rm['group_id']; ?>">
                 <a class="b">
                  <div class="qf">
                    <span class="qj">
                    <img class="cu qh" src="./img/<?php echo $rr['image']; ?>" style="height: 42px; width: 42px;">
                    </span>
                    <div class="qg">
                      <strong><?php echo $rr['username']; ?></strong> and <strong><?php echo $nm - 1 ?> others</strong>
                      <div class="aof">
                        <?php echo substr($rrr['message'], 0, 60); ?> …
                      </div>
                    </div>
                  </div>
                </a>
              </div>
              </div>
          <?php } ?>
        <?php } ?>

[benanamen]

You shouldn't have have a second query or third query. You need to do a single query with a join as I previously posted and you should be asking for specific columns, not using *.

Edited March 22, 2016 by benanamen