asked on

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.

?>

Andyc75

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

birwin

birwin

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

birwin

phparmy

ASKER

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.

phparmy

ASKER

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

birwin

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?

Andyc75

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

phparmy

ASKER

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.

Andyc75

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.

phparmy

ASKER

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.

Andyc75

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

ASKER CERTIFIED SOLUTION

Andyc75

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

phparmy

ASKER

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

Andyc75

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.