PHP MySQL SELECT WHERE NOT IN

Question

Having trouble implementing a bit of code which works in phpMyAdmin, but fails in PHP. (This is my first PHP project, and can't find answers that seem to work)

Basically I have 3 tables:

memberdetails = mem_id, lastname, firstname .....

classdetails = class_id, classname ...

classmember = class_id, mem_id

I can display a class list using following code

<?php
    $c_query = $conn->query("SELECT * FROM `classdetails` WHERE `class_id`= '$_REQUEST[class_id]'") or die(mysqli_error());
    c_fetch = $c_query->fetch_array();
    $class = $c_fetch['class_id'];
?>   
.......
<?php
  $query = $conn->query("SELECT * FROM `memberdetails` WHERE `mem_id` in (select mem_id from classmember where class_id= '$class')") or die(mysqli_error());
  while($f_query = $query->fetch_array()){
?>
<tr>
   <td><?php echo $f_query['firstname']?></td>
   <td><?php echo $f_query['lastname']?></td>
</tr>
<?php
  }
?>

That all works ok but later on I need to display a list of all the other "members" who are not in the class previously selected.

In myPHPAdmin the following works ok

SELECT * FROM memberdetails WHERE mem_id NOT IN (SELECT mem_id FROM classmember WHERE class_id = 3) ORDER BY lastname, firstname ASC

but I am losing the plot trying to get this to work in PHP

<?php
  $g_query = $conn->query('SELECT * FROM memberdetails WHERE mem_id NOT IN (SELECT * FROM memberdetails WHERE mem_id in (select mem_id from classmember where class_id= '$class') ORDER BY lastname, firstname ASC') or die(mysqli_error());
  while($g_fetch = $g_query->fetch_array()){
    echo "<option value = ".$g_fetch['mem_id'].">".$g_fetch['firstname'].' '.$g_fetch['lastname']."          </option>";
    }
   ?>

However I can't get this to work in the page. I have tried various ways to format the query.

I daresay this is quite simple, but, at the moment, if I had any hair I would be pulling it out!

There are two mem_id not in present in your query. Fix that. — Fahad Anjum, Nov 02 '17 at 10:50
Possible duplicate of [What is the difference between single-quoted and double-quoted strings in PHP?](https://stackoverflow.com/questions/3446216/what-is-the-difference-between-single-quoted-and-double-quoted-strings-in-php) — Nigel Ren, Nov 02 '17 at 10:59

score 1 · Answer 1 · edited Nov 02 '17 at 11:05

You have syntax error on your query.

$g_query = $conn->query('SELECT * FROM memberdetails WHERE mem_id NOT IN (SELECT * FROM memberdetails WHERE mem_id in (select mem_id from classmember where class_id= '$class') ORDER BY lastname, firstname ASC') or die(mysqli_error());

To this

$g_query = $conn->query("SELECT * FROM memberdetails WHERE mem_id NOT IN (SELECT * FROM memberdetails WHERE mem_id in (select mem_id from classmember where class_id= $class) ORDER BY lastname, firstname ASC") or die(mysqli_error());

OR

 $g_query = $conn->query('SELECT * FROM memberdetails WHERE mem_id NOT IN (SELECT * FROM memberdetails WHERE mem_id in (select mem_id from classmember where class_id= '.$class.') ORDER BY lastname, firstname ASC') or die(mysqli_error());

Also note that your tried phpMyadmin query and below one is different.

I think what you are looking for is:

$g_query = $conn->query('SELECT * FROM memberdetails WHERE mem_id NOT IN (SELECT mem_id FROM classmember WHERE class_id= '.$class.') ORDER BY lastname, firstname ASC') or die(mysqli_error());

Fahad Anjum · Accepted Answer · 2017-11-04T05:13:35.473

-1

There are two mistake I am able to find in your query

use column name instead of *

When you are using sub query and wants to match with a column then you have to write exact column name to which you want to compare otherwise it will give you an error.

you have not properly ended brackets.

Close brackets properly.

As per comment, You have also one more issue.

change $class to .$class

Try Below one.

<?php
  $g_query = $conn->query('SELECT * FROM memberdetails WHERE mem_id NOT IN (SELECT mem_id FROM memberdetails WHERE mem_id in (select mem_id from classmember where class_id= '.$class.')) ORDER BY lastname, firstname ASC') or die(mysqli_error());
  while($g_fetch = $g_query->fetch_array()){
    echo "<option value = ".$g_fetch['mem_id'].">".$g_fetch['firstname'].' '.$g_fetch['lastname']."          </option>";
    }
   ?>

edited Nov 04 '17 at 05:13

answered Nov 02 '17 at 11:05

Fahad Anjum

1,246
1
10
19

@OldGuy Have you tested this ? – Fahad Anjum Nov 02 '17 at 11:33
Thanks - basically I used Fahad's code, but needed to change '$class' to '.$class.' and it works :) – OldGuy Nov 02 '17 at 11:46
@OldGuy Could you please mark it as correct answer ? – Fahad Anjum Nov 02 '17 at 11:51

PHP MySQL SELECT WHERE NOT IN

2 Answers2