Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 105
  • Last Modified:

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

(this is a continuation of question http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_28552066.html#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.
This works fine, I (user 1 Neil) see all the people I'm chatting with and the latest message
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, when logged in as user 2 Clare I see her name and image, it should show my name and image (user 1 Neil)
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
0
Neil Thompson
Asked:
Neil Thompson
  • 12
  • 9
1 Solution
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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

0
 
Neil ThompsonSenior Systems DeveloperAuthor Commented:
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?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Neil ThompsonSenior Systems DeveloperAuthor Commented:
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);
0
 
Neil ThompsonSenior Systems DeveloperAuthor Commented:
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
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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

0
 
Neil ThompsonSenior Systems DeveloperAuthor Commented:
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,
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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?
0
 
Neil ThompsonSenior Systems DeveloperAuthor Commented:
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
0
 
Neil ThompsonSenior Systems DeveloperAuthor Commented:
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)
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
 
Neil ThompsonSenior Systems DeveloperAuthor Commented:
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
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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

0
 
Neil ThompsonSenior Systems DeveloperAuthor Commented:
That only seems to bring me back on all rows, no other user details?
just me returned
It should be above as well as the other user data we were getting before:before
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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

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

Is it because I'm using MySQL?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
I think it's a SQLFiddle problem/limitation.
I changed the order of columns and see what returns.:
SELECT c1.crConFK, c1.crTime, c1.crReply,
u2.uID, u2.uScreenName, u2.uProfileImage, u2.uSex,
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

Can't you run in your development environment?
0
 
Neil ThompsonSenior Systems DeveloperAuthor Commented:
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

0
 
Neil ThompsonSenior Systems DeveloperAuthor Commented:
| 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

0
 
Neil ThompsonSenior Systems DeveloperAuthor Commented:
Superb and continued support. I and EE are lucky to have your assistance. Many thanks Neil
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
So, you need an alias for fields with same name. Good catch ;)
Cheers
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 12
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now