CODEWITHSUNDEEP
phpmysqlsql
user3032282
user3032282

Reputation: 93

WHERE NOT IN command does not work in PHP but works in SQL

So I'm trying to select some values from one table that do not exist in a selection of another table.

This is the SQL command I run:

SELECT `u882219588_data`.`user`.`user_name`, `u882219588_data`.`user`.`email`,
       `u882219588_data`.`user`.`name`FROM `u882219588_data`.`user`
WHERE `u882219588_data`.`user`.`user_name` 
      NOT IN (SELECT 
                  `u882219588_data`.`user_groups`.`user_name` 
              FROM `u882219588_data`.`user_groups` 
              WHERE 
                  `u882219588_data`.`user_groups`.`group_id` = '$group_id');

And it works in PHPMyAdmin perfectly! But when I use it in PHP:

//Login information on top
if (isset($_GET['group_id'])) {
    $array = array();
    $response = array();
    $groupid = $_GET['group_id'];

    $result = mysqli_query($con,
            "SELECT `u882219588_data`.`user`.`user_name`, 
                   `u882219588_data`.`user`.`email`, `u882219588_data`.`user`.`name`
             FROM `u882219588_data`.`user`
             WHERE `u882219588_data`.`user`.`user_name` 
                NOT IN (SELECT 
                            `u882219588_data`.`user_groups`.`user_name` 
                        FROM `u882219588_data`.`user_groups` 
                        WHERE
                          `u882219588_data`.`user_groups`.`group_id` = '$group_id');");

    while($obj = mysqli_fetch_object($result)) {
        $array[] = $obj;
    }
    $response["success"]=1;
    $response["result"]=$array;
    $response["group_id"] = $groupid;

    print(json_encode($response));
}

mysqli_close($con);

It actually selects every row from the user table! I don't get what is happening here. I even tried to change the PHP a bit, use another approach, but the same result happens.

Later, I found out that 

SELECT 
     `u882219588_data`.`user_groups`.`user_name` 
FROM 
     `u882219588_data`.`user_groups` 
WHERE 
`u882219588_data`.`user_groups`.`group_id` = '$group_id'

was actually returning an empty set, but I have no idea why because it works in PHPMyAdmin.

Does anyone see the mistake?

Upvotes: 0

Views: 54

Answers (1)

bidifx
bidifx

Reputation: 1650

Typo: your variable is called

$groupid = $_GET['group_id'];

But you're using $group_id in your Query.

Also read @tadman's comment or you'll run into security issues by sql-injection.

Upvotes: 1

Related Questions