0

I have this array in a variable

$regions = |north|south|west|

and a database field "Region" that contains a single value e.g. north

I need a select statement that will select all the fields where the value of "Region" is not any of the values listed in the array. e.g. north, south, west.

gen_Eric
  • 223,194
  • 41
  • 299
  • 337
sah
  • 137
  • 2
  • 7
  • 1
    What have you tried? Where is `$regions` coming from? Because that's not an array. – gen_Eric Mar 01 '12 at 20:11
  • 1
    http://stackoverflow.com/questions/1519272/mysql-not-in-query – smp7d Mar 01 '12 at 20:13
  • I have multiple checkboxes in the form. If the user selects north and south and west, I need to display all records that do not have north south or west in the region field – sah Mar 01 '12 at 20:27
  • Rocket, your comment helped me solve the issue. I was passing the data as a string and not as an array. – sah Mar 27 '12 at 14:15

3 Answers3

3

You can do this in SQL with WHERE field NOT IN('value1', 'value2', ...). Basic string functions are probably sufficient to get your array in this format.

Another Code
  • 3,083
  • 21
  • 23
2
$regions = "north|west|south";
$regionsArray = explode('|', $regions);

for ($i = 0; $i < count($regionsArray); $i++) {
    $regionsArray[$i] = "'" . $regionsArray[$i] . "'";
}

$query = "SELECT *
FROM ...
WHERE Region NOT IN (" . implode(",", $regionsArray) . ")";

This assumes that your $regions variable is a string like described, and not an array.

If it was an array before then there is quite likely no need to convert it to a string though (the above code converts it back into an array).

Chris
  • 3,113
  • 26
  • 33
  • I tried this but this is what I get: Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in – sah Mar 01 '12 at 20:20
  • @sheredia: Is `$regions` an array or a string that contains e.g. "north|west|south"? – Chris Mar 01 '12 at 20:24
  • @sheredia: I edited my answer to show you a more detailed code example. – Chris Mar 01 '12 at 20:30
  • The code produces an SQL statement that will select all entries from your table except the ones where `Region` contains any of the values. Where is the problem? – Chris Mar 01 '12 at 20:44
  • this is the error that I get **Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in...** – sah Mar 01 '12 at 20:55
  • That most likely means that you are not getting any results from the database. This can be due to an invalid query or if there are just no results in the table that match. Print out the generated query and then issue it 'manually', like e.g. through phpmyadmin. – Chris Mar 01 '12 at 20:57
  • I tried this sql statement applied to the WordPress wp_users table and user_email field, with a comma delimited string of five email addresses as the list. It doesn't work, returning all email addresses. I just found out it doesn't work because the list (or if you use `implode()`) doesn't wrap each list item with a ' (opening and closing apostrophe). So, implode(",",$str) produces "user1@gmail.com,user2@mydomain.com,user3@mydomain.com". That fails the sql. The list has to be constructed like this: `WHERE user_email NOT IN ('user1@gmail.com','user2@mydomain.com','user3@mydomain.com')";` – TARKUS Nov 12 '15 at 16:25
1

maybe you forget about mysql_real_escape_string() ?

function db_escape($value)
{
    return '"'.mysql_real_escape_string($value).'"';
}

$regions = array('north','west','south');

$query = "SELECT *
FROM `table`
WHERE `Region` NOT IN (" . implode(",", array_map('db_escape',$regions)) . ")";
Mikhail
  • 2,542
  • 4
  • 29
  • 40