MySQL，MySQL：从ID不位于数组中的表中选择* [英] MySQL, PHP: Select * from table where id is not in array

问题描述

因此，我目前有一个数据库表，在这里我试图选择除我创建的数组中所包含的记录以外的所有记录。作为某些背景上下文：

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)

我的将数据放入哈希表的PHP脚本如下：

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

}

所以我想做的是创建一个MySQL查询，该查询从数组中读取ID（$ id），然后从中选择* APART。通过查找，似乎我将不得不使用 where not子句，但我不知道如何在此引用哈希。

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
}

我想从此数组中获取ID（例如154 ），然后选择表中没有上述任何ID的所有内容。我希望这有助于澄清？！

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?!

任何帮助将不胜感激！

推荐答案

解决：

$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>";

从多维数组创建另一个数组以仅存储ID，并像John Green一样使用NOT IN

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

谢谢！

这篇关于MySQL，MySQL：从ID不位于数组中的表中选择*的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！