MySQL, PHP: Select * from table where id is not in array

Question

so I currently have a database table where I am trying to select all of the records EXCEPT for those that are included in an array that I have made. As some background context:

The structure of the database table in question is:

server_status:
id int(11)
server_id int(11)
time_checked datetime
status char(1)

My PHP script to get the data into the hash looks like this:

$sql2 = "SELECT server_id, time_checked,id from server_status where   time_checked<'$date' order by server_id;";
$result2=$conn->query($sql2);
while($row2 = $result2->fetch_assoc()){

 $server_id = $row2['server_id'];
 $id = $row2['id'];
 $dt = $row2['time_checked'];

 $year = substr($dt,0,4);
 $month = substr($dt,5,2);
 $day = substr($dt,8,2);

 $day = "$year-$month-$day";

 $all[$server_id][$day] = $id;  // ARRAY

}

So what I'm trying to do is create a MySQL query that reads in the ids ($id) from the array, and selects * APART from that. From looking it up, it seems like I will have to use a 'where not' clause, but I do not know how to reference the hash in this.

Further clarification: I now have an array which extracts data which looks like this:

1{
2016-05-05 : 252
2016-05-10 : 406
2016-04-27 : 141
2016-05-04 : 164
2016-05-09 : 263
2016-05-03 : 153
2016-04-26 : 131
2016-04-14 : 1
2016-04-18 : 31
2016-04-21 : 111
2016-04-20 : 61
2016-04-19 : 51
2016-04-15 : 21
2016-04-25 : 121
}
2{
2016-05-10 : 452
2016-05-05 : 198
2016-05-09 : 264
2016-05-04 : 165
2016-04-26 : 132
2016-04-27 : 143
2016-04-25 : 122
2016-04-21 : 112
2016-05-03 : 154
}

I want to take the IDs from this array (e.g. 154) and select everything in the table which doesn't have any of the above IDs. I hope this helps to clarify?!

Any help will be greatly appreciated!

$all[$server_id][$day] = $id; sorry I think I might be using terminology used by my boss! :P in PHP it may be known as a multi-dimensional array? But I thought it was recognised as a hash by it's square brackets. — dplatt
– dplatt, Commented May 13, 2016 at 14:30
?? there is no hash. and with this explanation your goal became even more unclear — Alex
– Alex, Commented May 13, 2016 at 14:45
try to provide some example of records set where you select from. and expected result you want to get — Alex
– Alex, Commented May 13, 2016 at 14:47
I think you are mixing concepts, in php hash is a message digest, so what's a hash for you? xD — Asur
– Asur, Commented May 13, 2016 at 14:50

Paulo Amaral · Accepted Answer · 2016-05-13 16:16:16Z

2

In case $all is the array you want to extract the unwanted ids from, this might be what you need after the code you provided:

$ids_to_exclude = array();

// iterate through servers
foreach ($all as $server_id => $dates) {
    // iterate through dates of each server
    foreach ($dates as $date => $id) {
        // If a value is not in the array, add it.
        // In case ids don't repeat, you won't need this if
        if (!in_array($id, $ids_to_exclude)) {
             // add $id to the array
             $ids_to_exclude[] = $id;
        }
    }
}

$sql_condition = "where `id` not in (".implode(",",$ids_to_exclude).")";

Just be careful when writing queries with string concatenation. Read about SQL Injection and how to prevent it. Use Prepared Statements instead of pure concatenation.

answered May 13, 2016 at 16:16

Paulo Amaral

8731 gold badge6 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

dplatt · Accepted Answer · 2016-05-17 09:27:26Z

SOLVE:

$sql2 = "SELECT server_id, time_checked,id from server_status where time_checked<'$date' order by server_id;";
$result2=$conn->query($sql2);
while($row2 = $result2->fetch_assoc()){


 while($row2 = $result2->fetch_assoc()){
    $server_id = $row2['server_id'];
    $id = $row2['id'];
    $dt = date('Y-m-d', strtotime($row2['time_checked']));
    $all[$server_id][$dt] = $id;  // ARRAY
 }
}

 $stack = array();
  $keys = array_keys($all);
  for($i = 0; $i < count($all); $i++) {
      foreach($all[$keys[$i]] as $key => $value) {
        array_push($stack, $value);
      }
  }


$ids = join(',',$stack);
$sql = "SELECT * FROM server_status WHERE time_checked<'$date' AND id NOT IN ($ids)";
$result=$conn->query($sql);
echo "Server status data has been deleted.<br>";

Created another array from the multi-dimensional array to store the ids only and use NOT IN like John Green suggested.

Thanks!

John Green · Accepted Answer · 2016-05-13 16:05:25Z

1

I think you just want a NOT IN, right?

$sql2 = "SELECT id, server_id, time_checked,id 
         FROM server_status 
         WHERE 
            id NOT IN (".implode(',', $id_array)."
            AND time_checked<'$date' 
         ORDER BY server_id;";

$result2=$conn->query($sql2);

while($row2 = $result2->fetch_assoc()){   
   $server_id = $row2['server_id'];
   $id = $row2['id'];
   $dt = date('Y-m-d', strtotime($row2['time_checked']));
   $all[$server_id][$dt] = $id;  // ARRAY 

}

answered May 13, 2016 at 16:05

John Green

13.5k3 gold badges31 silver badges52 bronze badges

2 Comments

dplatt Over a year ago

How would I define $id_array please? :)

John Green Over a year ago

a PHP array of IDs. If your IDs are numeric, something like $id_array = array(1,5,12,24,33);

Collectives™ on Stack Overflow

MySQL, PHP: Select * from table where id is not in array

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related