CODEWITHSUNDEEP
phpmysqlselect
Developer
Developer

Reputation: 1040

Select from table where value does not exist

I have a table (country_table) with a list of all countries and their respective ids.

|country_id|country_name|
|ES        |Spain       |
.
.
.

I have an array, fetched from another table using fetch_array that looks like this:

$array = Array(
    [0]=>Array([country_id]=>'ES')
    [1]=>Array([country_id]=>'DE')
    [2]=>Array([country_id]=>'GB'))

How can I select the records (country_id and country_name) from country_table that do not exist in the table but exist in the array?

Upvotes: 2

Views: 628

Answers (4)

Tronix
Tronix

Reputation: 3

For the question: "How can I select the records (country_id and country_name) from country_table that do not exist in the table but exist in the array?"

I guess you have to write some code (eg. in PHP) in order to achieve the result you want. The problem is that you can not display data that is not in the table (eg. country_name), that's not possible as you don't know this data. The only possibility would to show the country_id's which are in the array and not in the table as this is the only data you have...

Upvotes: 0

inhan
inhan

Reputation: 7470

// gather unwanted id's first
$notInIds = array();
foreach ($array as $arr) $notInIds[] = $arr['country_id'];

// prepare query
$query = 'SELECT `country_id`, `country_name` FROM `your_table`
WHERE `country_id` NOT IN (' . implode(',',$notInIds) . ')';

// then execute it

Upvotes: 0

Mudassir Hasan
Mudassir Hasan

Reputation: 28741

$sql ="SELECT country_id, country_name FROM country_table
       WHERE country_id NOT IN (". implode(",", $array) .")";

implode() function will generate comma-seprated string representation of array elements .

Upvotes: 2

Codeguy007
Codeguy007

Reputation: 891

This is the sql.

select country_id, country_name from table where country_id not in ('ES','DE','GB');

Upvotes: 0

Related Questions