How to show unique sender id from sql query conversation

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • help How to show unique sender id from sql query conversation

    Hi,

    I was trying to print only unique/ distinct sender name in my conversation list.

    Here is my current code

    $sql = "SELECT
    a.name, b.id, b.byuid, b.unread, b.starred FROM all_users_table a
    INNER JOIN private_messages b ON a.id = b.byuid
    WHERE b.touid='".$myid."' AND starred='0'
    ORDER BY b.timesent DESC, b.unread
    LIMIT $limit_start, $items_per_page
    ";

    This currently printing

    user1
    user1
    user1
    user2
    user2
    user3

    What I wanted to print

    >user1
    >user2
    >user3

    Is it possible ?

    I tried with Group By buyuid but it returns random user NOT order by tme sent / unread one first.

    How can I solve this ? !
    Last edited by a4techwap; 01.03.18, 06:41.

  • #2
    Try this, because i think the problem would be the b.unread that came after the DESC order...that shouldn't be there......
    $sql = "SELECT
    a.name, b.id, b.byuid, b.unread, b.starred FROM all_users_table a
    INNER JOIN private_messages b ON a.id = b.byuid
    WHERE b.touid='".$myid."' AND starred='0'
    ORDER BY b.timesent DESC LIMIT $limit_start, $items_per_page
    ";

    Comment


    • #3
      you should try this one.
      Code:
      $sql = "SELECT
      a.name, b.id, b.byuid, b.unread, b.starred FROM all_users_table a
      INNER JOIN private_messages b ON a.id = b.byuid
      WHERE b.touid='".$myid."' AND starred='0' AND b.timesent=(SELECT MAX(timesent) FROM private_messages WHERE b.touid='".$myid."' AND byuid=b.byuid) GROUP BY byuid
      ORDER BY b.timesent DESC, b.unread
      LIMIT $limit_start, $items_per_page
      ";

      Comment


      • #4
        Originally posted by GumSlone View Post
        you should try this one.
        Code:
        $sql = "SELECT
        a.name, b.id, b.byuid, b.unread, b.starred FROM all_users_table a
        INNER JOIN private_messages b ON a.id = b.byuid
        WHERE b.touid='".$myid."' AND starred='0' AND b.timesent=(SELECT MAX(timesent) FROM private_messages WHERE b.touid='".$myid."' AND byuid=b.byuid) GROUP BY byuid
        ORDER BY b.timesent DESC, b.unread
        LIMIT $limit_start, $items_per_page
        ";
        Thank you so much for your reply. It seems the server take time (loading and loading) to execute the query.

        This is my private_messages table. Can we optimize the sql query ?



        All I want is to (For touid = 4)
        1. print all unique byuid as a output.
        2. Order By Unread DESC
        3. Order By timesent DESC
        4. Limit 0, 2 (for pagination purpose)
        So that the output will be instead of:

        Code:
        1
        1
        1
        2
        3
        TO:
        Code:
         1 2 3
        Thank you so much once again GumSlone.
        Last edited by a4techwap; 03.03.18, 12:25.

        Comment


        • #5

          Comment


          • #6
            i think that your id is auto increment so you can also oder it by id instead of timesent like this:
            Code:
            SELECT t1.* FROM private_messages t1 WHERE t1.touid=4 AND t1.id=(SELECT MAX(id) FROM private_messages WHERE t1.touid=4 AND byuid=t1.byuid) GROUP BY t1.byuid ORDER BY t1.unread DESC, t1.id DESC LIMIT 0,2

            Comment

            Working...
            X