asked on

Messaging system needs to pull back other persons details, not message initiator

(this is a continuation of question https://www.experts-exchange.com/questions/28552066/issue-returning-all-information-from-chat-sytem-that-has-no-replies-just-its-some-sort-of-JOIN-problem.html?anchorAnswerId=40428316#a40428316 after noticing I need additional syntax)

Hi, I have a messaging system that lets users contact other users and keeps a FaceBook style presentation of them.

If I log in as myself (user 1) I get everything working fine, I get the list of all the people I'm in conversation with (Leigh, Jayne and Claire) with the latest conversation message with each person.

Now... If I log in as one of those users I would then expect to see "my" image and name as to them that's who they have a conversation with (or started by). They don't want to see their name and face next to each conversation as they wouldn't know which conversation was which until they clicked to expand them in the right column.

Here is the SQL to build and add content to the tables:

CREATE TABLE conversation (
  coID int auto_increment primary key,
  coUserOne int(11),
  coUserTwo int(11),
  coIP varchar(30),
  coTime int(11)
); 


CREATE TABLE users (
  uID int auto_increment primary key,
  uScreenName varchar(100),
  uProfileImage varchar(100),
  uSex varchar(10)
); 


CREATE TABLE conversation_reply (
  crID int auto_increment primary key,
  crReply text,
  crUserFK int(11),
  crIP varchar(30),
  crTime int(11),
  crConFK int(11)
); 


INSERT INTO users VALUES (1,'Neil','20140302__1__b00c1338a7feeae110af30868d871549.png','Male');
INSERT INTO users VALUES (2,'Clare','20140303__2__150fb4811b2794f6b8a39d49ead2edc5.jpg','Female');
INSERT INTO users VALUES (3,'Leigh','20140403__3__fda33411ff84b3f69484d862838dd417.jpg','Male');
INSERT INTO users VALUES (7,'Jayne','20140403__7__87bbf84f78bd08b71a55a2002e35230a.jpg','Female');


INSERT INTO conversation VALUES (1,1,2,'127.0.0.1',1400945511);
INSERT INTO conversation VALUES (2,1,3,'127.0.0.1',1400945613);
INSERT INTO conversation VALUES (3,1,7,'127.0.0.1',1400946146);


INSERT INTO conversation_reply VALUES (1,'This is my first conversation to Clare',1,'127.0.0.1',1400945511,1);
INSERT INTO conversation_reply VALUES (2,'this is a reply from Clare',2,'127.0.0.1',1400945514,1);
INSERT INTO conversation_reply VALUES (3,'Hi Leigh, hows it looking on Android?',1,'127.0.0.1',1400945613,2);
INSERT INTO conversation_reply VALUES (5,'Have you seen the amount of people who have registered on the site in the last day, it\'s gone mad.',1,'127.0.0.1',1400946146,3);
INSERT INTO conversation_reply VALUES (6,'test',7,'127.0.0.1',1400946149,3);
INSERT INTO conversation_reply VALUES (7,'good',1,'92.40.104.96',1401642550,3);
INSERT INTO conversation_reply VALUES (9,'Very good',1,'188.31.214.125',1401703530,3);
INSERT INTO conversation_reply VALUES (10,'latest test from upload to live server :)',1,'82.47.69.204',1401991923,1);
INSERT INTO conversation_reply VALUES (11,'Cooooooooooooooo eeeeeeeeee',2,'77.96.207.54',1401993721,1);
INSERT INTO conversation_reply VALUES (12,'Ipad test',1,'188.29.102.172',1402780028,1);

Open in new window

SELECT c1.crConFK, c1.crTime, c1.crReply, u.uScreenName, u.uProfileImage, u.uID, u.uSex
FROM conversation_reply AS c1
	INNER JOIN (SELECT crConFK, MAX(crID) MAXcrID
			FROM conversation_reply
			GROUP BY crConFK) AS c2
    ON c1.crID=c2.MAXcrID
	INNER JOIN conversation c ON c.coID=c2.crConFK
    INNER JOIN users u ON c.coUserTwo=u.uID
WHERE (c.coUserOne = 1 OR c.coUserTwo = 1)
ORDER BY c1.crID, c1.crconfk

Open in new window

These are on a SQLFiddle at http://sqlfiddle.com/#!2/854e3/17/0

I'm using MySQL 5.x

Regards
Neil

Vitor Montalvão

Neil, if you are the User #1 then you need to find a way to substitute the userID in the WHERE clause:

DECLARE @UserdID INT
SET @UserdID=1 --> Change here for other users

SELECT c1.crConFK, c1.crTime, c1.crReply, u.uScreenName, u.uProfileImage, u.uID, u.uSex
FROM conversation_reply AS c1
	INNER JOIN (SELECT crConFK, MAX(crID) MAXcrID
			FROM conversation_reply
			GROUP BY crConFK) AS c2
    ON c1.crID=c2.MAXcrID
	INNER JOIN conversation c ON c.coID=c2.crConFK
    INNER JOIN users u ON c.coUserTwo=u.uID
WHERE (c.coUserOne = @UserID OR c.coUserTwo = @UserdID)
ORDER BY c1.crID, c1.crconfk

Open in new window

Neil Thompson

ASKER

Lol, that's way over my head :) That why my code was so long winded (and failed to work) last time :)

I still need to get all the messages back that the user (e.g. user 2 clare) has started, or is in but somehow need to swap her details for the other party, that's what I'm unsure of.

I cant work out why it works perfectly though for me as user 1, is it just because there are replies?

Vitor Montalvão

Depends on what you want but I will guess if there's no replies then no conversation exists, right?
Like you are using the phone to call someone but the partner didn't answer.
It's up to you to decide if you want these kind of not replied conversation to be shown or not.

Neil Thompson

ASKER

Yes, I want them to be shown, but I also want them to show that for example as user 2 (clare) has begun a conversation (or message) to user 1 (Neil) but is waiting for a reply.

The message/conversation (all be in only 1 way at present) still exists.

INSERT INTO conversation VALUES (1,1,2,'127.0.0.1',1400945511);

Neil Thompson

ASKER

The code you sorted Friday is perfect but If for example the user had started 20 conversations with 20 people that all had yet to respond the would have 20 pictures of herself up with no idea who they conversations had been started with

Vitor Montalvão

Using the data provided by you, when I change the UserID to 2, this is the returned row:

crConFK	crTime	crReply	uScreenName	uProfileImage	uID	uSex
1	1402780028	Ipad test	Clare	20140303__2__150fb4811b2794f6b8a39d49ead2edc5.jpg	2	Female

Open in new window

DECLARE @UserID INT
SET @UserID=2 --> Change here for other users

SELECT c1.crConFK, c1.crTime, c1.crReply, u.uScreenName, u.uProfileImage, u.uID, u.uSex
FROM conversation_reply AS c1
	INNER JOIN (SELECT crConFK, MAX(crID) MAXcrID
			FROM conversation_reply
			GROUP BY crConFK) AS c2
    ON c1.crID=c2.MAXcrID
	INNER JOIN conversation c ON c.coID=c2.crConFK
    INNER JOIN users u ON c.coUserTwo=u.uID
WHERE (c.coUserOne = @UserID OR c.coUserTwo = @UserID)
ORDER BY c1.crID, c1.crconfk

Open in new window

Neil Thompson

ASKER

yes, that's right.

logged in as clare (user 2) that's the right response, although as the user and profilepicture returned is that of user 2 I need it to show the other party to the conversation,

so in this case as it would be user 1 Neil and the profile image associated with that,

Vitor Montalvão

When the user is you (UserID=1) the returned rows are:

crConFK	crTime	crReply	uScreenName	uProfileImage	uID	uSex
2	1400945613	Hi Leigh, hows it looking on Android?	Leigh	20140403__3__fda33411ff84b3f69484d862838dd417.jpg	3	Male
3	1401703530	Very good	Jayne	20140403__7__87bbf84f78bd08b71a55a2002e35230a.jpg	7	Female
1	1402780028	Ipad test	Clare	20140303__2__150fb4811b2794f6b8a39d49ead2edc5.jpg	2	Female

Open in new window

How are you showing the other party actually?

Neil Thompson

ASKER

That's the weird thing, when logged in as me its perfect

There are 3 chats going on, each one comes back with the person I'm having a chat with Leigh, Jayne and Clare

For some reason though, for any other user, 2,3 or 7 it comes back with "their info" rather than the person they are chatting / have started to chat to

Neil Thompson

ASKER

User 1 - Me , Chats going on with Leigh, Jayne, Clare = OK

User 2 - Clare, chat going on with me (neil user 1) but her details show (rather than mine as the other party)
User 3 - Leigh, chat going on with me (neil user 1) but his details show (rather than mine as the other party)
User 4- Jayne, chat going on with me (neil user 1) but her details show (rather than mine as the other party)

Vitor Montalvão

I think somewhere in your code there's hardcoded your user ID since it's only working with you. Try to find it.
You can also try to have a reply to any of the other conversations to see how it behaves.

Neil Thompson

ASKER

Its definitely not hard codes anywhere as the SQL above works right for me, as you can see from your results. It's just when viewing as anyone else.

I thing unfortunately I may have to check if the user returned is the current logged in user, and if so do a second lookup to that conversation and find the other parties name and picture.

One thing that would help is if it is possible to return both parties details from each conversation in the current list? That way I already have the data and can simply programmatically look at each bit rather than do a series of db calls?

So, from your image aboive something like

crConFK crTime crReply uScreenName1 uProfileImage1 uID1 uSex1 uScreenName2 uProfileImage2 uID2 uSex2

Vitor Montalvão

Yes, it makes more sense if you return both users data:

DECLARE @UserID INT
SET @UserID=1 --> Change here for other users

SELECT c1.crConFK, c1.crTime, c1.crReply, u.uID, u.uScreenName, u.uProfileImage, u.uSex, u2.uID, u2.uScreenName, u2.uProfileImage, u2.uSex
FROM conversation_reply AS c1
	INNER JOIN (SELECT crConFK, MAX(crID) MAXcrID
			FROM conversation_reply
			GROUP BY crConFK) AS c2
    ON c1.crID=c2.MAXcrID
	INNER JOIN conversation c ON c.coID=c2.crConFK
    INNER JOIN users u ON c.coUserOne=u.uID
    INNER JOIN users u2 ON c.coUserTwo=u2.uID
WHERE (c.coUserOne = @UserID OR c.coUserTwo = @UserID)
ORDER BY c1.crID, c1.crconfk

Open in new window

Neil Thompson

ASKER

That only seems to bring me back on all rows, no other user details?

It should be above as well as the other user data we were getting before:

Vitor Montalvão

Verify is the rest of columns aren't hidden. Roll the scroll back to right also :)
Here's what returned for me:

crConFK	crTime	crReply	uID	uScreenName	uProfileImage	uSex	uID	uScreenName	uProfileImage	uSex
2	1400945613	Hi Leigh, hows it looking on Android?	1	Neil	20140302__1__b00c1338a7feeae110af30868d871549.png	Male	3	Leigh	20140403__3__fda33411ff84b3f69484d862838dd417.jpg	Male
3	1401703530	Very good	1	Neil	20140302__1__b00c1338a7feeae110af30868d871549.png	Male	7	Jayne	20140403__7__87bbf84f78bd08b71a55a2002e35230a.jpg	Female
1	1402780028	Ipad test	1	Neil	20140302__1__b00c1338a7feeae110af30868d871549.png	Male	2	Clare	20140303__2__150fb4811b2794f6b8a39d49ead2edc5.jpg	Female

Open in new window

Neil Thompson

ASKER

weird, still not coming up here : http://sqlfiddle.com/#!2/854e3/32/0

Is it because I'm using MySQL?

ASKER CERTIFIED SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Neil Thompson

ASKER

I tried it in my own MySQL and it was the same until I set the column names for the duplicates using AS and it now works :)

SELECT c1.crConFK, c1.crTime, c1.crReply,
u2.uID as uID2, u2.uScreenName as uScreenName2, u2.uProfileImage as uProfileImage2, u2.uSex as uSex2,
u.uID, u.uScreenName, u.uProfileImage, u.uSex
FROM conversation_reply AS c1
	INNER JOIN (SELECT crConFK, MAX(crID) MAXcrID
			FROM conversation_reply
			GROUP BY crConFK) AS c2
    ON c1.crID=c2.MAXcrID
	INNER JOIN conversation c ON c.coID=c2.crConFK
    INNER JOIN users u ON c.coUserOne=u.uID
    INNER JOIN users u2 ON c.coUserTwo=u2.uID
WHERE (c.coUserOne = 1 OR c.coUserTwo = 1)
ORDER BY c1.crID, c1.crconfk

Open in new window

Neil Thompson

ASKER

| CRCONFK |     CRTIME |                               CRREPLY | UID2 | USCREENNAME2 |                                    UPROFILEIMAGE2 |  USEX2 | UID | USCREENNAME |                                     UPROFILEIMAGE | USEX |
|---------|------------|---------------------------------------|------|--------------|---------------------------------------------------|--------|-----|-------------|---------------------------------------------------|------|
|       2 | 1400945613 | Hi Leigh, hows it looking on Android? |    3 |        Leigh | 20140403__3__fda33411ff84b3f69484d862838dd417.jpg |   Male |   1 |        Neil | 20140302__1__b00c1338a7feeae110af30868d871549.png | Male |
|       3 | 1401703530 |                             Very good |    7 |        Jayne | 20140403__7__87bbf84f78bd08b71a55a2002e35230a.jpg | Female |   1 |        Neil | 20140302__1__b00c1338a7feeae110af30868d871549.png | Male |
|       1 | 1402780028 |                             Ipad test |    2 |        Clare | 20140303__2__150fb4811b2794f6b8a39d49ead2edc5.jpg | Female |   1 |        Neil | 20140302__1__b00c1338a7feeae110af30868d871549.png | Male |

Open in new window

Neil Thompson

ASKER

Superb and continued support. I and EE are lucky to have your assistance. Many thanks Neil

Vitor Montalvão

So, you need an alias for fields with same name. Good catch ;)
Cheers