Combine 2 columns as 1 with other columns as well

Question

I have this table

myid             his_id

  7                1

  1                7

  1                6

  1                3

But its giving me 4 records instead of 3, i tried using mysqli "join" , "group by" but didnt work.

Im trying to develop a chat system with pure php and jquery, in d table above, user 7 chatted with user 1 and user 1 chatted with user 7 so i want to see it as just the same chat and the rest of the 2 ( user 1 chatting with user 6 and user 1 chatting with user 3)

I want it to give me just 3 records instead of 4 records, pls any help will be appreciated, thank you

Sorry im not connected to my system, im using a smart phone, forgive my bad formatted query below

$sql = mysqli_query($connectn, "select a.id, b.his_id, a.myid, a.status, a.date_replied FROM chats a JOIN chats b ON b.myid=b.myid WHERE b.his_id='$regestered_id' or b.myid='$regestered_id' GROUP BY b.his_id ORDER BY a.id DESC") ;

I use so and cancelled so many sql querries, infact im even confused right now — Chinny Enechukwu, Sep 10 '16 at 12:00
Pls i just included my sql query, pls check it and tell me why its giving 4 records instead of 3 records, thank you — Chinny Enechukwu, Sep 10 '16 at 12:15
you must also post how you're assigning a value to the registered_id variable. — user2940296, Sep 10 '16 at 12:41

score 0 · Answer 1 · answered Sep 10 '16 at 14:56

0

If you want distinct combinations of myid and his_id, you must not print id, date_replied, status, etc, but just two columns- his_id, myid.
Because if there are multiple rows with two columns with same values, it is yet another to decide which row must be selected in order to print id, status, etc.
So, just to print which two users are chatting, use myid and his_id. You can later use them for any other information.
Now, the code you wanted is-

$sql = mysqli_query($connectn, "select distinct 
least(myid, his_id), greatest(myid, his_id) 
from chats");

And yes, like @iblamefish mentioned, don't forget to protect your code against SQL injection.

answered Sep 10 '16 at 14:56

user2940296

143
15

I tried the above sql, quite alright user 1 saw 3 records but user 3 is also seeing 3 records instead of just 1 (user 1 chat) pls any help again? – Chinny Enechukwu Sep 12 '16 at 15:56
The above sql query finds the number of independent chats going on (in your case, 3). To find how many other individuals one individual is talking to, you'll need a where statement for that. E.g.- You want to check for user 3. just add 'where myid=3 or his_id=3' in the sql query after table name. To generalize it you could, instead of 3, use a variable which contains some user's id. – user2940296 Sep 12 '16 at 18:19
Yes i did that but when i checked user 1, it showed me 3 records, but when i checked user 3, it still showed me 3 records instead of 1. Just like in a chatting sysyem, im also trying so many sql statements but stil couldnt get what i wanted – Chinny Enechukwu Sep 12 '16 at 18:47
can you share the query you used for user 3? – user2940296 Sep 12 '16 at 18:56
Wow, finally it worked, i used exactly the query u gave to me and added "where myid='$registered_id' or his_id='$regestered_id' if i open the chat for user 1, it shows just 3 records as expected and when i open the chat for user 3, it showed just 1 record as expected, thank you very much im grateful, kuduos to u. But pls i hope my query is correct, hope its gonna hav bad behaviour or wrong output in future? – Chinny Enechukwu Sep 12 '16 at 19:08
it should not, as it selects 2 columns with unique values(order is not important). but if it ever does, you can leave a comment or ask another question :) – user2940296 Sep 13 '16 at 05:48
you can check the tick to the left of my answer if you're satisfied with it and think it solved your problem. thanks! – user2940296 Sep 13 '16 at 07:28

Combine 2 columns as 1 with other columns as well

1 Answers1