We help IT Professionals succeed at work.

select from array which not in table

hello,

how can i select from array that these values not in table.

for example
<?php
$array = (1,2,3,4,5);

$array_str = my_implode("'","'",",", $array); //now  array_str is '1','2','3','4','5'

$s1 = mysql_query("select * from users where user_id not in ($array_str)"); // this returns me users not in this array

but i need

$s1 = mysql_query("select * from $array_str where $array_str not in users.user_id");

// how can i do with MySQL.

//Please dont answer php coding only SQL needed.
Thanks.

?>
Comment
Watch Question

Andyc75Software Architect
Top Expert 2010

Commented:
Give this a try,

you can build a fake table of values using unions, then left join that result to your table.  To get the values that do not exist in your table find the records that don't match by looking for nulls in the 2nd table.


select A.id, B.name from  
(select 1 as id
union select 2
union select 3
union select 4
union select 5) A
   left outer join users B on A.id = B.user_id
Where b.user_id is null

Open in new window

Commented:

$array_string="|".implode("|",$array)."|";
"select * from users where  instr($array_string, concat(\"|\",user_id,\"|\"))=''; // that is two single quotes ''

Commented:
Did you try my code? I am using it for the same purpose. Did it throw an error? What result did it give you?

Commented:
Actually I do see an error. I missed the ending double quote. It should read:
$array_string="|".implode("|",$array)."|";  
"select * from users where  instr($array_string, concat(\"|\",user_id,\"|\"))='' ";

Author

Commented:
birwinD, i already write this in my question. i dont want to find array in table, i want to find table in array. Please read carefully again. my_implode function is doing same with your implode function.

Author

Commented:
key point is : how can i select from array that these values not in table.

Commented:
You can't. mySQL will only interact with tables.
My code above will pull any users that are not in the array, or by changing ="" to >'' will pull users that are in the array.
Why don't you just dump the array into a temporary table and let mySQL do its magic?
 
Andyc75Software Architect
Top Expert 2010

Commented:
Hi phparmy,

You can do this with mySQL in a single statement,  Did you try my example that I posted ?

I will post this again, please try it and verify that it works for you.

Here we have your list of values 1-5.
There is a table of users with 2 users (1 and 3)

The result is 2, 4 and 5 which are the values that are not in your user table. Which is exactly what you asked for.




Create table users(
user_id int null,
name varchar(25) null
);


insert into users values (1,'John');
insert into users values (3,'Bob');


select A.id, B.name from  
(select 1 as id
union select 2
union select 3
union select 4
union select 5) A
   left outer join users B on A.id = B.user_id
Where b.user_id is null;

Open in new window

Author

Commented:
because i want to insert only values that are not in table and delete values not in array.

i give you only sample, as in normal data is so big.
Andyc75Software Architect
Top Expert 2010

Commented:
I am providing you with a list of values from the array that are not it the user table (which is what you asked for)

Your original question did not say anything about inserting or deleting values.  

I provided the correct Answer to your original question, based on the information you provided.

If you have new requirements please post a new question with all the important information and any relevant sample data.



Author

Commented:
i tested your code again, it gives user_id which are not in array, but i need values in array but not in table.

if you look your code you will see, you are using select * from users, your source is users table.

think my array is 1,2,100

1,2 in users table but 100 not

as a result it has to give me 100 result.
Andyc75Software Architect
Top Expert 2010

Commented:
Please re-check this:

The Array has these values ( 1 2 3 4 5 )

The User table has these values 1 3

The result is 2 4 5 (which are the values from the array that are not in the table)

My Source is the array not the table.

I used the same values you provided in this new example:
Array has 1,2, 100
Users table has 1,2

Result is 100
Create table users(
user_id int null,
name varchar(25) null
);


insert into users values (1,'John');
insert into users values (2,'Bob');


select A.id, B.name from  
(select 1 as id
union select 2
union select 100) A
   left outer join users B on A.id = B.user_id
Where b.user_id is null;

Open in new window

Software Architect
Top Expert 2010
Commented:
"if you look your code you will see, you are using select * from users, your source is users table."

"Select * from users" is the code the birwin posted,  Can your browser see the code blocks that I am posting ?

Here is the text I posted in the Code Block:

Create table users(
user_id int null,
name varchar(25) null
);


insert into users values (1,'John');
insert into users values (2,'Bob');

select A.id, B.name from  
(select 1 as id
union select 2
union select 100) A
   left outer join users B on A.id = B.user_id
Where b.user_id is null;

Author

Commented:
thanks Andyc75 your solution is true i tried this before only concern about performance issues. i have tried a lot.

Please try this code.


<?php
$con1 = mysqli_connect("localhost","root","password");
mysqli_select_db($con1,"database");

// Try bigger than 5331 then it will give you error.
//error is Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given in line 26

for ($i=0;$i<5331;$i++){
	$array[$i] = $i;
}

$sayac = 0;
$str = "";
foreach ($array as $key => $value){
	$sayac++;
	if($sayac == "1"){
		$str = " select $value as id ";
	}else {
		$str.=" union select $value ";
	}
	
}

$sql = "select A.id,B.username from ($str) A left outer join users B on A.id=B.user_id where B.user_id is null"; 

echo $sql;

$r1 = mysqli_query($con1,$sql);
while ($r2 = mysqli_fetch_assoc($r1)){
	echo $r2[id]."<br/>";
}
?>

Open in new window

Andyc75Software Architect
Top Expert 2010

Commented:
It does not make sense to build a query this large.  It would be just as bad to build an in clause with 5000 values.

If you need to compare against an array this large you should put these values into a database table and join on the table.

Your array is hard coded so I can't see any reason why you would need to build an array of values instead of using a tables.