Solved

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

Posted on 2014-11-10
21
83 Views
Last Modified: 2014-11-10
(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
Comment
Question by:NeilT
  • 12
  • 9
21 Comments
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40432569
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
 
LVL 3

Author Comment

by:NeilT
ID: 40432594
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40432600
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
 
LVL 3

Author Comment

by:NeilT
ID: 40432607
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
 
LVL 3

Author Comment

by:NeilT
ID: 40432612
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40432656
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
 
LVL 3

Author Comment

by:NeilT
ID: 40432775
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40432781
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
 
LVL 3

Author Comment

by:NeilT
ID: 40432790
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
 
LVL 3

Author Comment

by:NeilT
ID: 40432793
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40432801
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
 
LVL 3

Author Comment

by:NeilT
ID: 40432826
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40432867
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
 
LVL 3

Author Comment

by:NeilT
ID: 40432916
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40432921
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
 
LVL 3

Author Comment

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

Is it because I'm using MySQL?
0
 
LVL 45

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 40432934
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
 
LVL 3

Author Comment

by:NeilT
ID: 40432960
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
 
LVL 3

Author Comment

by:NeilT
ID: 40432962
| 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
 
LVL 3

Author Closing Comment

by:NeilT
ID: 40432975
Superb and continued support. I and EE are lucky to have your assistance. Many thanks Neil
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40432999
So, you need an alias for fields with same name. Good catch ;)
Cheers
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now