Problems in consulting with NOT IN

Question

I have 3 users in the database, and I want to search for all users, except the user 2, then have to return user 1 and 3. In the database returns perfectly, but on the web always returns user 1, and it has to be 1 and 3. The SQL code I used in PHP to return all users except with id = 2 is:

$checkEmail = "SELECT email ";
$checkEmail .= "FROM usuariospf ";
$checkEmail .= "WHERE usuarioID NOT IN(2) ";
$queryEmail = mysqli_query($connection, $checkEmail);
if(!$queryEmail){
    die('Erro ao fazer a consulta!');
}
$users = mysqli_fetch_assoc($queryEmail);
print_r($users);

Answer 1

As commented by Mr Anderson, mysqli_fetch_assoc does not do what you are expecting, and the result you have obtained is absolutely correct (comes one at a time, and obviously is the first).

Mr Sam’s reply has an example of how to use the loop.

If you want to get all users at once, you should prefer the função mysqli_fetch_all:

$checkEmail = "SELECT email ";
$checkEmail .= "FROM usuariospf ";
$checkEmail .= "WHERE usuarioID <> 2";
$resultEmail = mysqli_query($connection, $checkEmail);
if(!$resultEmail){
    die('Erro ao fazer a consulta!');
}
$users = mysqli_fetch_all($resultEmail,MYSQLI_ASSOC);
print_r($users);

Handbook:
https://www.php.net/manual/en/mysqli-result.fetch-all.php

Note the use of the option MYSQLI_ASSOC for associative result instead of numerical.

Compare with the fetch-assoc
https://www.php.net/manual/en/mysqli-result.fetch-assoc.php

If there is no function error, make sure your PHP is using the extension mysqlnd. If not, try to use it this way:

$resultEmail->fetch_all(MYSQLI_ASSOC);

I took the opportunity to change your NOT IN for WHERE usuarioID <> 2. The IN makes sense when you want to use a list of values, like WHERE usuarioID NOT IN (2, 3, 7)

Answer 2

The function mysqli_fetch_assoc() pulls each line found in the query each time it is called, i.e.:

-> mysqli_fetch_assoc($queryEmail) // retorna o primeiro registro
-> mysqli_fetch_assoc($queryEmail) // retorna o segundo registro
-> mysqli_fetch_assoc($queryEmail) // retorna o terceiro registro
etc...

As you only called once in:

$users = mysqli_fetch_assoc($queryEmail);

The value of $users will be the first record found.

To create an array of all records, you can create a while feeding the array by calling the function up to the number of results found. At the end you will have an associative array $users[] with all records found:

$checkEmail = "SELECT email ";
$checkEmail .= "FROM usuariospf ";
$checkEmail .= "WHERE usuarioID NOT IN(2) ";
$queryEmail = mysqli_query($connection, $checkEmail);
if(!$queryEmail){
  die('Erro ao fazer a consulta!');
}
while($row = mysqli_fetch_assoc($queryEmail)){
   $users[] = $row;
}
print_r($users);