Mysql join query help
- or -
Post a project like this3488
£6(approx. $8)
- Posted:
- Proposals: 2
- Remote
- #575785
- Awarded
Description
Experience Level: Entry
General information for the website: mysql
Description of every page/module: Right, i have a messaging system but im having trouble with one query!
So basically, im displaying the chats in a list, ordering by users online then users with messages (viewed)
I have got it so it displays the users online, but i cant get the users with messages
SHOULD TAKE SOMEONE 2 minutes! MYSQL EXPERT NEEDED
Here is mysql tables
USERS
- username
- key
- status
CHAT
- user1
- user2
- chat_key
CHAT_SEEN
- msgid
- userid
- viewed
This is the query i have so far (working but without the chat_seen)
$query = $this->pdo->prepare("SELECT u.key, u.status, c.chat_key, u.username
FROM chat c, users u
WHERE CASE
WHEN c.user1 = ?
THEN c.user2 = u.key
WHEN c.user2 = ?
THEN c.user1= u.key
END
AND (
c.user1 = ?
OR c.user2 = ?)
ORDER BY u.status DESC LIMIT 50");
Description of requirements/features: Right, i have a messaging system but im having trouble with one query!
So basically, im displaying the chats in a list, ordering by users online then users with messages (viewed)
I have got it so it displays the users online, but i cant get the users with messages
SHOULD TAKE SOMEONE 2 minutes! MYSQL EXPERT NEEDED
Here is mysql tables
USERS
- username
- key
- status
CHAT
- user1
- user2
- chat_key
CHAT_SEEN
- msgid
- userid
- viewed
This is the query i have so far (working but without the chat_seen)
$query = $this->pdo->prepare("SELECT u.key, u.status, c.chat_key, u.username
FROM chat c, users u
WHERE CASE
WHEN c.user1 = ?
THEN c.user2 = u.key
WHEN c.user2 = ?
THEN c.user1= u.key
END
AND (
c.user1 = ?
OR c.user2 = ?)
ORDER BY u.status DESC LIMIT 50");
Extra notes:
Description of every page/module: Right, i have a messaging system but im having trouble with one query!
So basically, im displaying the chats in a list, ordering by users online then users with messages (viewed)
I have got it so it displays the users online, but i cant get the users with messages
SHOULD TAKE SOMEONE 2 minutes! MYSQL EXPERT NEEDED
Here is mysql tables
USERS
- username
- key
- status
CHAT
- user1
- user2
- chat_key
CHAT_SEEN
- msgid
- userid
- viewed
This is the query i have so far (working but without the chat_seen)
$query = $this->pdo->prepare("SELECT u.key, u.status, c.chat_key, u.username
FROM chat c, users u
WHERE CASE
WHEN c.user1 = ?
THEN c.user2 = u.key
WHEN c.user2 = ?
THEN c.user1= u.key
END
AND (
c.user1 = ?
OR c.user2 = ?)
ORDER BY u.status DESC LIMIT 50");
Description of requirements/features: Right, i have a messaging system but im having trouble with one query!
So basically, im displaying the chats in a list, ordering by users online then users with messages (viewed)
I have got it so it displays the users online, but i cant get the users with messages
SHOULD TAKE SOMEONE 2 minutes! MYSQL EXPERT NEEDED
Here is mysql tables
USERS
- username
- key
- status
CHAT
- user1
- user2
- chat_key
CHAT_SEEN
- msgid
- userid
- viewed
This is the query i have so far (working but without the chat_seen)
$query = $this->pdo->prepare("SELECT u.key, u.status, c.chat_key, u.username
FROM chat c, users u
WHERE CASE
WHEN c.user1 = ?
THEN c.user2 = u.key
WHEN c.user2 = ?
THEN c.user1= u.key
END
AND (
c.user1 = ?
OR c.user2 = ?)
ORDER BY u.status DESC LIMIT 50");
Extra notes:
Daniel L.
100% (14)Projects Completed
21
Freelancers worked with
19
Projects awarded
18%
Last project
19 Sep 2018
United Kingdom
New Proposal
Login to your account and send a proposal now to get this project.
Log inClarification Board Ask a Question
-
There are no clarification messages.
We collect cookies to enable the proper functioning and security of our website, and to enhance your experience. By clicking on 'Accept All Cookies', you consent to the use of these cookies. You can change your 'Cookies Settings' at any time. For more information, please read ourCookie Policy
Cookie Settings
Accept All Cookies