Get one item of duplicate if found

[Mr.M]

Hi DW. I'm creating a chat system which will alow guests to send DMs to the operator and the operator can reply to it. I'm using MYSQL as my database, fingerprint2js for identifying the user since user has not yet logged in or registered but want to first communicate with the admin.

Scenario here is that the user has sent 2 messages which are bot recorded separately on the database but have the same uid which is the id used to identify the user. My problem is that even the admin when replay he will also use this same uid so the uid is having many duplication as they communicate, issue is that it's not only the two people communicating but others as well which makes it have many duplicates of each uid.
What want to achieve withe the select is to get only a single uid of each even if its its duplicate or not but get one of each so that will be able to iterate through each user and be able to sort their messages for displaying purpose. At the moment I have 2 rows with the same uid but when I use select and while since the number of rows are two it runs twice which end up producing same result twice. On the display side get 4 messages because for each iteration it return both rows when on my select statement I select where uid equal id which is returned by a select all from xchat table where msgfrom equal user. This returned all the uids of the messages since it's only the user that starts a chat now the issue is that a user or admin can send more than one message, how can I get all uids but for duplicates only get 1 id of each so that when I work with it only work once even if there are more than one same uid on the database and also get those that are not duplicates as well.

Thanks.

[larry29936]

Sounds like you might need an autoincrementing id field that's a primary key. Then retrieve based on max value of the id field.

[pritaeas]

Show some data. What do you have in your database, and what do you want as output?

[Mr.M]

I have uid,status,mdate,msgfrom,msg 5 fields as uid,status,mdate,msgfrom,msg

Now I have data as
1111,new,14/03/22 11:41:25, user, Hello

3254,read,13/03/22 09:05:10, user, Morning
1111,new,14/03/22 11:42:07, user, I have a question for you.

Now as you can see I have 3 records which two of it has the same uid which is 1111, now I want to select all records with msgfrom equal user then for each get the uid which I will use to get a complete chat for that user and the admin respond to that user as it will all have the same uid.

[pritaeas]

If you query the following:

SELECT * FROM yourtable WHERE msgfrom='user' ORDER BY uid, mdate DESC

You have all the information you need in the correct order.

[Mr.M]

Thanks but since I have 3 records with 2 duplicate uid I only need to get two row back where one is 1111 and another one is 3254.

That because inside this select * query I then loop through each to get other relavent data that has the same uid so I only need to get one 1111 not two as they are duplicate. If I use Distinct get intenal error.

[pritaeas]

That because inside this select * query I then loop through each to get other relavent data that has the same uid so I only need to get one 1111 not two as they are duplicate.

My example is there to show you that you can do it in a single query (and do the rest in code), instead of three queries when you use your solution. Just something to think about.

If I use Distinct get intenal error.

Show your query.

[Mr.M]

Solution was DISTINCT but the problem now is that it return two recodes but when I make it to return 1 of the duplicate it then return the uid but I think it don't know which msg to return as they are two rows with the same uid but different messages, so now I get the uid as intended but it doesn't return message which are on another column.

[rproffitt]

For Mr.M. Now that you have the idea down pat what about ORDER? Maybe the ORDER can pop the row you want to the top. I don't see enough code and table information so I could be very far off base or my rocker.

[Mr.M]

Please check my second reply message above for the sample database.

My query is very simple.
I first get all the messages from user so that I will get the uids. Now since the uids has duplicates I then used distinct instead of SELECT * FROM mytable WHERE msgfrom = 'user' I have SELECT DISTINCT uid FROM mytable WHERE msgfrom = 'user' this solve the issue of when displaying this be repeated twice because now it only return exact without duplicate. Now the issue is with the other select statement that I have inside this select statement which that one is for actually pulling the data.

Remember the first query is for getting the uids so that with this query inside we get the last message of that uid. This is for creating the user chat list where you get a list of chats and beneath it there will be a short display of the message but not the full message because of the space, so when you select the chat user you will then see the chat messages on the right hand side.

But I'm not yet retrieving messages for the chat box but for creating a users chats list which is a list of people with their last messages. I use a similar select query except that this time i replaced WHERE msgfrom = 'user' whith WHERE uid = $row['uid']

So at the moment what is the problem is that yes it returns 1 uid of a duplicate as if the uid is 1 in the data base but it doesn't return any message from msg column. My suspicion is that it doesn't know which message to return since there are two from with the same uid but distinct the uid using the uid.

[Biiim]

a bit messy but you could use a subquery to filter it out, something like:

SELECT user_id, message_time, status, message, `etc` FROM `table` t
LEFT JOIN (SELECT user_id, MAX(message_time) last_message FROM `table` GROUP BY user_id) `sub_table` st ON t.user_id = st.user_id AND t.message_time = st.last_message
WHERE st.user_id IS NOT NULL

That will only pull only the last message for each user_id - so 1 row per person, but it loses the indexing due to being a sub query so it could get slow if your tables go in the 100,000+ figures or even 10,000's depending on your server hardware.

Could be your UI programming that needs looking at also, I just pull out the data and sort it in indexed arrays afterwards. IE pull all the messages out and then loop through indexing them inside an array indexed on the user_id, then you'd have all messages by "Joe" in one array, or index an array on "conversations" and filter them by conversations - whatever you're trying to do.

like foreach($data as $value){$by_user[$value['user_id']] = $value;$by_conversation[$value['conversation_id']] = $value;}