CODEWITHSUNDEEP
phpmysql
Matarishvan
Matarishvan

Reputation: 2432

MySql NOT IN many values from the mysql fetched result

I have 2 MySql queries which are interdependent.
My 'table1'

----------
  id
----------
   1
   2
   3
   4
   5
   6
   7

My First query

$sql1 = "SELECT * FROM table1 WHERE";// some condition which gives me id's 1,2,3
$res1=$obj->_executeQuery($sql1);
$res1=$obj->getAll($res1);

The result of this is giving me array

Array
(
    [0] => Array
        (
            [id] => 1
            ..
            ..
        )

    [1] => Array
        (           
            [id] => 2
            ..
            ..
        )
    [2] => Array
        (           
           [id] => 3
            ..
            ..
        )
)

I want to run another query on same 'table1', where not equal to list of ID's which i am getting from the first query.

My Second Query

$sql2 = "SELECT * FROM table1 WHERE id NOT IN (" . implode(',', $res1) . ")";

This is not showing me only one id i,e first. In above case i should get id's 4,5,6,7

Upvotes: 0

Views: 55

Answers (3)

Vamshi .goli
Vamshi .goli

Reputation: 520

Here you are getting two dimensional array so that's reason it is not working

while($each=mysql_fetch_array($res1))
{
$array[]=$each[id];
}
$imp=implode(",",$array);
$sql2 = "SELECT * FROM table1 WHERE id NOT IN (".$imp.")";

Try this it will works

Upvotes: 0

Suchit kumar
Suchit kumar

Reputation: 11859

Since implode will not give the desired value on multidimensional array so first you need to get the array of all id's to form one-dimensional array then use implode on the array of id's:

$id=array();
foreach ($res1 as $key=>$inner_array){
    $id[]= $inner_array['id'];
}

you can use array_walk also here like this:

array_walk($res1,function($c) use (&$id) {$id[] = $c['id'];});

but i think the best one is array_map :

$id = array_map(function($i) {
    return $i['id'];
}, $res1);

$sql2 = "SELECT * FROM table1 WHERE id NOT IN (" . implode(',', $id) . ")";

Note: when ever you are doing select please specify your column if you need few to select,unnecessarily selecting all will slow your sql processing.

suppose here:SELECT * FROM table1 WHERE, if you need only id then please select id only.

Upvotes: 1

Manmaru
Manmaru

Reputation: 578

You have to change $res1, which is a two-dimensional array, into a one-dimensional array of IDs to be able to use implode:

$ids_from_first_query = array();
foreach($res1 as $result_row) {
    $ids_from_first_query[] = $result_row['id'];
}

$ids_as_string = implode(',', $ids_from_first_query);
$sql2 = 'SELECT * FROM table1 WHERE id NOT IN(' . $ids_as_string . ')';

In the above code, $ids_as_string will look like this:

1,2,3

Thus it can be used in your MySQL query

Upvotes: 0

Related Questions