Topic: exclude values in an array from mysql select statement????

[Heylance]

Beginner here!!!

I am trying to exclude the values in an array from being searched for in a mysql select statement

WHERE P.id != '$new_array' AND .....ect;

Doesn't work, searches for all P.id's normally and doesn't exclude the id's in the array.

But

If I use foreach where the array comes from, only the last item in the array is acted upon.

print_r shows the array normally before doing the foreach statement

but after creating a foreach statement:

Code: [Select]

if(count($array) > 0){
 foreach($array as $item);
  $new_array = $item;
}

Now
list_r  $new_array  displays the last value only and not as an array but just the string by itself... 428

The id 428 is excluded from the select process but not the other values in the array.

I know I can change it to $new_array = array($item)
print_r shows new_array normally but I'm back to the select statement not excluding anything.

What's up!!!!

[jonsjava]

not sure if this is what you are wanting, but....

Code: [Select]

<?php
$new_array = array();
if(count($array) > 0){
 foreach($array as $item);
  if ($item != "undesired item"){
       $new_array = $item;
  }
}


[Heylance]

Thanks,
What I need is for the values in the array to be excluded at the mysql select statement, just like in my example if possible.
SELECT ... FROM.... WHERE ..... AND P.id != '$new_array'

BTW, I tried implode to make a comma separated list and a space separated list, didn't work

Code: [Select]

if(count($array) > 0){
 foreach($array as $item){
  $new_array = implode(",",array);
 }
}


[sasa]

Code: [Select]

$new_array = implode("','", $array);and
WHERE P.id NOT IN ('$new_array') AND .....ect;

[jonsjava]

the only problem is the last ","
solution:

Code: [Select]

<?php
if(count($array) > 0){
$last = array_pop($array);
 foreach($array as $item){
   if ($item != $last){
      $exclude_list .= "'".$item."', ";
  }
 }
 $exclude_list .= "'".$last."'";
}
$sql = "SELECT * FROM `table_name WHERE P.id NOT IN ({$exclude_list})";


[Heylance]

Thanks everyone...

sasa

Code: [Select]

$new_array = implode("','", $array);

it works
echo $new_array
427','428

I tried it with P.id != '$new_array' and that works as well as with an empty array.

I also tried it with  implode("''", $array);
it worked also
echo $new_array
427'428

jonsjava

Code: [Select]

$sql = "SELECT * FROM `table_name WHERE P.id NOT IN ({$exclude_list})";

If the array is empty it doesn't work. " NOT IN ({$exclude_list}) "
It kills the select statement and nothing gets selected if the array (or list, whichever it is) is empty.

I tried it with P.id NOT IN ('$exclude_list') and it works
I tried it with P.id != '$exclude_list' and it works

both of the above yielded:

echo $exclude_list
'427', '428'

-----------------------
It seem that we have 3 or 4 ways to yield the same result. I will leave this as unsolved for a while if anyone would care to comment on which method is or isn't correct.

Thank again, Lance