help How to show unique sender id from sql query conversation

[a4techwap]

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 ? !

[tupac]

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

[GumSlone]

you should try this one.

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

[a4techwap]

you should try this one.

$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:

1
1
1
2
3

TO:

 1 2 3

Thank you so much once again GumSlone.

[a4techwap]

[GumSlone]

i think that your id is auto increment so you can also oder it by id instead of timesent like this:

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