MySQL Forums
Forum List  »  PHP

Can anyone help me with this Query?
Posted by: DR Rom
Date: November 10, 2022 11:12AM

Basically my goal is to have a multiple filter in my small project that I'm working.
I have a page for Rooms or Lobby.

Filters are:

Privacy : ( 0 / 1 / 2 ) 0 = All / 1 = Public / 2 = Private

Status : ( 0 / 1 / 2 / 3 ) 0 = ALl / 1 = Open / 2 = In Progress / 3 = Completed

Created By Me: ( True or False )

Joined Room: ( True or False )

I also have 2 tables.. One for Rooms and other for Room_users


| User ID | Name |
| -------- | -------- |
| 1 | Me |
| 2 | Raven |
| 3 | Gabby |


| Room ID | Title | Privacy | Status | User ID |
| -------- | ----------- | -------- | -------- | ---------- |
| 1 | My Room | 1 | 1 | 1 | <<< Me/I'm the Creator/Host
| 2 | Raven Room | 2 | 1 | 2 | <<< Raven is the Creator/Host
| 3 | Gabs Room | 1 | 1 | 2 | <<< Raven is the Creator/Host


| Room Users | Room ID | User ID | Status |
| ---------- | ---------- | -------- | -------- |
| 1 | 2 | 1 | 1 |
| 2 | 2 | 3 | 2 |
| 3 | 3 | 3 | 1 |

Here, Me and UserID#3 joined to Room ID#2 (Raven Room)

**Status**: 1 = Pending / 2 = Joined

The result should display, Rooms based on the filter..
Here's my current query:

$userID = My ID / UserID# 1



$status = ($status != "0") ? "AND `a`.`status` = '".$status."'" : "AND `a`.`status` != '0'";
$privacy = ($privacy != "0") ? "AND `a`.`privacy` = '". $privacy ."'" : "AND `a`.`privacy` != '0'";



"SELECT a.room_id, a.title, a.status, a.privacy, a.user_id, b.room_id, b.user_id, b.status, c.user_id, c.name
FROM `rooms` as `a`
LEFT JOIN `room_users` as `b` ON `a`.`room_id` = `b`.`room_id`
LEFT JOIN `user` as `c` ON `b`.`user_id` = `c`.`user_id`
WHERE (`a`.`user_id` = '". $userID ."' OR (`b`.`status` != '1' OR `b`.`user_id` = '". $userID ."')) ". $status ." ". $privacy ." ORDER BY `a`.`room_id` DESC"

When i filter Privacy: All, it should return this ff:
1. all rooms that is public
2. all rooms that I'm member of (Private). Else, it should be exluded.

Filter: Privacy: Public should only display all rooms with status 1 (Public)

Filter: Privacy: Private should only display all rooms that **I'm member of**.

when I filter Created By Me, it should display only rooms that I created.

when I filter Joined Room, it should display only the rooms that I'm member of.

Right now, according to my query it only returns room that I joined in and the room I created.

And it displays multiple duplicated rooms, but I used array_unique to return only the unique rooms. I CAN'T use **GROUP BY** since it has a problem with MySQL regarding sql_mode=only_full_group_by and for some reason I dont have access on doing that.

Can anyone help me with how to construct the query for the database, so it will return the accurate data that i needed..


Any help is very much appreaciated.

Options: ReplyQuote


Subject
Written By
Posted
Can anyone help me with this Query?
November 10, 2022 11:12AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.