While loop to output a list from SQL on a per users group permission basis returns duplicate results

Question

DISCLAIMER: Before I continue I would like to state that I am not a PHP pro or anything like that, I haven't even touched PHP for 3-4 years (where then I knew way more) which makes it even harder for me to understand the issue, I've been looking online but didn't get far sadly so I decided to post a question here

P.S: About my code, I know that better SQL methods can be used but as it stands for now I just want to figure out what is wrong with my code and then I can take care of "improving" it

I know that I am probably approaching this in the wrong way (at first I thought its only my sql query)

Code:

<?php
header('Access-Control-Allow-Origin: localhost');
header('Content-type: application/json');
$conn = mysqli_connect("localhost", "root", "local_database", "example_db");
// Pull Member data
function getMember($data) {
    try {
        $select = mysqli_query($conn, "SELECT * FROM store_members WHERE name = '{$data}'");
        $result = mysqli_fetch_assoc($select);
        mysqli_free_result($select);
    } catch (RuntimeException $e) {
        $result["error"] = "Error: ".$e->getMessage();
    }
    
    return $result;
}
// Function that takes 2 strings and tries to find a match against a list, if a match is found it will be returned.
// Example Usage: (1) echo InGroupID(1, "2, 8", "10, 3, 8"); (2) echo InGroupID(10, "2, 8", "10, 3, 6"); (3) echo InGroupID(10, "2, 8", "10, 3, 8"); || (1) Result: 8, (2) Result: 10 (3) Result: 10
function InGroupID($maingroup, $secondary_groups = FALSE, $customgrouplist = FALSE) {
    $member->split_sgroups = preg_split("/[\s,]+/", $secondary_groups);
    $groups->permitted = array(4, 2, 6); // 4 = Admins, 2 = Moderators, 6 = Private Users (default list if none provided)
    if($customgrouplist !== FALSE)
        $customgrouplist = preg_split("/[\s,]+/", $customgrouplist);
    else
        $customgrouplist = $groups->permitted;
    
    $sgroup_match = array_intersect($member->split_sgroups, $customgrouplist);
    $key = array_search($maingroup, $customgrouplist);
    if($key !== false)
        return $customgrouplist[$key];
    elseif(($secondary_groups !== FALSE) && ($sgroup_match !== FALSE)) {
        sort($sgroup_match);
        return $sgroup_match["0"];
    }
    else
        return false;
    
    return false;
}
$query = mysqli_query($conn, "SELECT * FROM `store_products`");
$res = array();
if(mysqli_num_rows($query) >= 1) {
    $mmbr = getMember("Admin");
    while($row = mysqli_fetch_assoc($query)) { // duplicates results for me, if I disable the first while it will display products based only on permissions from 1 row and not accordingly to each product
        $getGroup = InGroupID($mmbr["main_group_id"], $mmbr["secondary_group_ids"], $row["product_permissions"]); // tries to find a match for members permissions in store products, if found, the match will be returned so we can use it to filter products in the next while.
        $cQuery = mysqli_query($conn, "SELECT `product_name`,`price`,`quantity`,`product_permissions` FROM `store_products` WHERE CONCAT(',', product_permissions, ',') like '%{$getGroup}%'"); // product_permissions example "5, 9, 10"
        // SELECT `product_name`,`price`,`quantity` FROM `store_products` WHERE FIND_IN_SET('{$getGroup}',product_permissions) -> this won't work for values after the first comma because it is seperated by comma and space and not just comma
        if(!empty($getGroup)) { 
            while($row2 = mysqli_fetch_assoc($cQuery)) { // if 1st while is off it will display rows based on matching permissions from first row only and not each accordingly
                $row2["price"] = intval($row2["price"]);
                $row2["quantity"] = intval($row2["quantity"]);
                // Debug, can be ignored.
                $row2["main_group_id"] = $mmbr["main_group_id"];
                $row2["secondary_group_ids"] = $mmbr["secondary_group_ids"]; 
                $row2["($)getGroup"] = $getGroup;
                $res[] = $row2;
            }
        }
    }
    echo json_encode($res);
}
else {
    $res[0] = array(
        "product_name" => "no_products_available_foruser",
        "price" => 0,
        "quantity" => 0
    );
    echo json_encode($res);
}

SQL Tables Used:

TABLE store_members:

+-----------------------------------------------------------------+
|                          store_members                          |
+----+-----------+----------+---------------+---------------------+
| id | name      | password | main_group_id | secondary_group_ids |
+----+-----------+----------+---------------+---------------------+
|  1 | Admin     |    ---   | 4             | 8                   |
+----+-----------+----------+---------------+---------------------+
|  2 | Moderator |    ---   | 2             | 8, 3                |
+----+-----------+----------+---------------+---------------------+
|  3 | VIP_User  |    ---   | 8             | 3                   |
+----+-----------+----------+---------------+---------------------+
|  4 | Shopper12 |    ---   | 3             | NULL                |
+----+-----------+----------+---------------+---------------------+

TABLE store_products:

+--------------------------------------------------------------------------+
|                              store_products                              |
+----+----------------------------+-------+----------+---------------------+
| id | product_name               | price | quantity | product_permissions |
+----+----------------------------+-------+----------+---------------------+
|  1 | iPhone X - VIP Edition     |   30  | 5        | 4, 2, 8             | 
+----+----------------------------+-------+----------+---------------------+
|  2 | iPhone X - Public          |   45  | 200      | 2, 8, 3             | 
+----+----------------------------+-------+----------+---------------------+
|  3 | iPhone X - Private Edition |   15  | 3        | 4, 2                | 
+----+----------------------------+-------+----------+---------------------+
|  4 | Pixel 4 - VIP Edition      |   28  | 10       | 4, 2, 8             | 
+----+----------------------------+-------+----------+---------------------+
|  5 | Pixel 4 - Private Edition  |   12  | 8        | 4, 2                | 
+----+----------------------------+-------+----------+---------------------+
|  6 | Pixel 4 - Public           |   42  | 164      | 2, 8, 3             | 
+----+----------------------------+-------+----------+---------------------+
|  7 | Pixel 4 - Subscribers      |   40  | 100      | 6                   | 
+----+----------------------------+-------+----------+---------------------+

Here's what the code should do basically,

Short Version:

Pulls product list from an sql table store_products by a users group id/sub group ids from store_members table and displays the list in json.

Long Version:

PHP Script to get main_group_id, secondary_group_ids values from store_members table by a given members name and then takes main_group_id, secondary_group_ids and searches for any match at another table (store_products) at column product_permissions finally, if there's a match the certain product will be displayed, it should search for a match for every product separately from store_products and then display it in json.

The Problem: Duplicated results, here's how it happens to me -

Let's get the product list for user "Admin" in a json file, Member Properties - Name: Admin , Main Group ID: 4 (Admins) , Secondary Group IDS: 8 (VIP)

A while loop should calculate how many rows to display based on permission match for each row individually, For example My Main Group ID & Secondary Group IDS are 4 and 8 which means I have permissions to view products list for groups 4 and 8, I should get 6 results then because 4 and 8 exist in these rows product_permissions, Instead I get duplicate results and I sometimes don't get products that match permission for my subgroup, especially for products that have two of my groups in their product_permissions

It's all calculated like that:

InGroupID(4, 8, "4, 2, 8"); // 4 (my main group), 8 (secondary), iPhone X - VIP Edition's `product_permissions` || returns 4 because there is a match for 4 in 4,2,8 (first match gets returned first)

InGroupID(4, 8, "2, 8, 3"); // 4 (my main group), 8 (secondary), iPhone X - Public's `product_permissions` || returns 8 because there is a match for 8 in 2,8,3 (first match gets returned first)

The match from InGroupID will be passed to $getGroup through an SQL query (SELECT `product_name`,`price`,`quantity`,`product_permissions` FROM `store_products` WHERE CONCAT(',', product_permissions, ',') like '%{$getGroup}%') and will loop with while for each product in the list to display accordingly

Notice: The Enteries below appear at the json file and are just for "debugging" purposes to better understand the problem, ignore them if you'd like as they are not a part of the actual code.

"product_permissions": "X, X, X",
"main_group_id": "X",
"secondary_group_ids": "X, X",
"($)getGroup": "X"

Default Output (original code): https://codebeautify.org/jsonviewer/cb675fd3 - shows all results but has too many duplicates

Without the 1st while-loop (// while($row = mysqli_fetch_assoc($query)) {): https://codebeautify.org/jsonviewer/cbebc3eb

Expected Output (what I wanted): https://codebeautify.org/jsonviewer/cb656906

Can you tell me what's wrong with my code?

Answer 1

I would recommend using [PDO][1]

$query = mysqli_query($conn, "SELECT * FROM `store_products`");
$res = [];

if(mysqli_num_rows($query) >= 1) {
    $mmbr = getMember("Admin");
    $sql  = 'SELECT `product_name`,`price`,`quantity`,`product_permissions` FROM `store_products` WHERE';

    while($row = mysqli_fetch_assoc($query)) {
        // bool / int
        $getGroup = InGroupID($mmbr["main_group_id"], $mmbr["secondary_group_ids"], $row["product_permissions"]);

        // Concat all the existing group into a query string.
        if($getGroup) { 
            $sql .=  " CONCAT(',', product_permissions, ',') like '%{$getGroup}%' OR";
        }
    }

    // Remove last OR
    $sql       = rtrim($sql, 'OR');

    $prepare   = mysqli_query($conn, $sql);
    // If finally using PDO, you can do this instead
    // $res = $sth->fetch(PDO::FETCH_ASSOC);
    // Saves you from another loop.
    while($row = mysqli_fetch_assoc($prepare)) {
        $res[] = $row;
    }

    // Tested and Working, Thanks!!
    echo json_encode($res);
}
else {
    $res = [
        "product_name" => "no_products_available_foruser",
        "price"        => 0,
        "quantity"     => 0    
    ];

    echo json_encode($res);
}


  [1]: https://www.php.net/manual/en/book.pdo.php