CODEWITHSUNDEEP
phpmysqljson
Michael TGCM
Michael TGCM

Reputation: 45

Exclude results from SQL if Y in other Table

I have a list of users in my database, i also have another table if a user is banned...

i need help to adjust my sql query so that if a user is in ban table they will be excluded from the sql results

tbl_ban_user    
banID user_id nickname isBan date_ban   ban_cause
98    32      Michael  Y     2016-03-11 whatever reason
99    84      Joe      Y     2016-03-11 whatever reason



tbl_users
user_id nickname is_private verified etc..
32      Michael  N          Y    
84      Joe      N          Y

here is my sql query's which then output the JSON, 

public function fetchUsers($keyword, $user_id, $who = 0, $who_nickname = '') {
        $sql = "SELECT
                A.*,
                SUM(IF(D.grader_id='$user_id', 0, 1)) AS ungraded_count,
                ROUND(AVG(D.grader_value),0) as avg_grade,
                COUNT(DISTINCT B.grader_id) as grading,
                COUNT(DISTINCT E.grade_id) as graders,
                COUNT(DISTINCT C.photo_id) as photos,
                SUM(IF(B.grader_id='$user_id', 1, 0)) AS is_graded,
                SUM(IF(B.grader_id='$user_id', B.notification, 0)) AS notification
                FROM
                tbl_users A
                LEFT JOIN
                tbl_grade B ON (A.user_id = B.grade_id AND B.grade_type=2)
                LEFT JOIN
                tbl_photo C ON (A.user_id = C.user_id)
                LEFT JOIN
                tbl_grade D ON (C.photo_id=D.grade_id AND D.grade_type=1)
                LEFT JOIN
                tbl_grade E ON (A.user_id = E.grader_id AND E.grade_type=2)
                WHERE
                A.verified = 'Y' AND 
                A.user_name LIKE '%$keyword%'
                 AND A.user_id <> '$user_id' " . ($who == 0 ? '' : " AND A.user_id = '$who'") . ($who_nickname == '' ? '' : " AND A.nickname = '$who_nickname'") . "

                GROUP BY A.user_id
                ORDER BY A.user_name";

        return $this->db->query($sql)->result();
    }

So basically I'm looking to exclude any users listed in the tbl_ban_user from the results how would i code that?

Thank you for your help

Upvotes: 1

Views: 42

Answers (2)

Ruslan Khyurri
Ruslan Khyurri

Reputation: 157

  1. Never run this function in production, because this lines are vulnerable:

    A.nickname = '$who_nickname'

Try to use PDO to avoid sql injections

  1. Use Josh's second example for request (JOIN variant). Subquery "SELECT DISTINCT(user_id) FROM tbl_ban_user" will work very slow in large tables.

Upvotes: 1

Josh Rumbut
Josh Rumbut

Reputation: 2710

Probably the quickest way to do it would be this:

public function fetchUsers($keyword, $user_id, $who = 0, $who_nickname = '') {
        $sql = "SELECT
                A.*,
                SUM(IF(D.grader_id='$user_id', 0, 1)) AS ungraded_count,
                ROUND(AVG(D.grader_value),0) as avg_grade,
                COUNT(DISTINCT B.grader_id) as grading,
                COUNT(DISTINCT E.grade_id) as graders,
                COUNT(DISTINCT C.photo_id) as photos,
                SUM(IF(B.grader_id='$user_id', 1, 0)) AS is_graded,
                SUM(IF(B.grader_id='$user_id', B.notification, 0)) AS notification
                FROM
                tbl_users A
                LEFT JOIN
                tbl_grade B ON (A.user_id = B.grade_id AND B.grade_type=2)
                LEFT JOIN
                tbl_photo C ON (A.user_id = C.user_id)
                LEFT JOIN
                tbl_grade D ON (C.photo_id=D.grade_id AND D.grade_type=1)
                LEFT JOIN
                tbl_grade E ON (A.user_id = E.grader_id AND E.grade_type=2)
                WHERE
                A.verified = 'Y' AND 
                A.user_name LIKE '%$keyword%'
                 AND A.user_id <> '$user_id' " . ($who == 0 ? '' : " AND A.user_id = '$who'") . ($who_nickname == '' ? '' : " AND A.nickname = '$who_nickname'") . "
                AND A.user_id NOT IN(SELECT DISTINCT(user_id) FROM tbl_ban_user)

                GROUP BY A.user_id
                ORDER BY A.user_name";

        return $this->db->query($sql)->result();
    }

Another way would be:

public function fetchUsers($keyword, $user_id, $who = 0, $who_nickname = '') {
        $sql = "SELECT
                A.*,
                SUM(IF(D.grader_id='$user_id', 0, 1)) AS ungraded_count,
                ROUND(AVG(D.grader_value),0) as avg_grade,
                COUNT(DISTINCT B.grader_id) as grading,
                COUNT(DISTINCT E.grade_id) as graders,
                COUNT(DISTINCT C.photo_id) as photos,
                SUM(IF(B.grader_id='$user_id', 1, 0)) AS is_graded,
                SUM(IF(B.grader_id='$user_id', B.notification, 0)) AS notification
                FROM
                tbl_users A
                LEFT JOIN
                tbl_grade B ON (A.user_id = B.grade_id AND B.grade_type=2)
                LEFT JOIN
                tbl_photo C ON (A.user_id = C.user_id)
                LEFT JOIN
                tbl_grade D ON (C.photo_id=D.grade_id AND D.grade_type=1)
                LEFT JOIN
                tbl_grade E ON (A.user_id = E.grader_id AND E.grade_type=2)
                LEFT JOIN
                tbl_bar_user F ON F.user_id = A.user_id
                WHERE
                A.verified = 'Y' AND 
                A.user_name LIKE '%$keyword%'
                 AND A.user_id <> '$user_id' " . ($who == 0 ? '' : " AND A.user_id = '$who'") . ($who_nickname == '' ? '' : " AND A.nickname = '$who_nickname'") . "
                AND F.isBan <> 'Y'

                GROUP BY A.user_id
                ORDER BY A.user_name";

        return $this->db->query($sql)->result();
    }

You can test both and see which suits your data set/coding preferences better.

Upvotes: 1

Related Questions