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

Question

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?

When using mysqli you should be using parameterized queries and bind_param to add user data to your query. DO NOT use string interpolation to accomplish this because you will create severe SQL injection bugs. I hope for your sake this code is nowhere near the public internet. — tadman
– tadman, Commented Jun 24, 2014 at 8:23
There is a typo in your code @ $groupid is your declared variable and you are using $group_id. — Harry
– Harry, Commented Jun 24, 2014 at 8:26

bidifx · Accepted Answer · 2014-06-24 08:23:58Z

1

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.

answered Jun 24, 2014 at 8:23

bidifx

1,67013 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related