使用PDO Sqlserver删除

Question

早上好，布迪。我有一个API调用来给我在我的网络系统中注册的profilesId。我想从我的sqlserver表中删除请求响应中不包含的内容。

我对属性进行了选择，以显示我与slqserver表和响应的共同点，并对剩余的进行了删除。

理论上，它应该是有效的，但没有。

你在信中有错吗？

我目前在sql server表中有391个寄存器，只有389个与我的sql server表和响应调用兼容。当我进行选择时，只给我一个共同的寄存器，idk不起作用。

选择查询(我只提到389个寄存器，没错(。

foreach ($result as $indUser => $userInfos){
$agentId = $userInfos['id'];
$dhInsert = date("Y-m-d H:i:s");
foreach ($userInfos['profileIds'] as $indProfile => $idProfile){
$sql = "SELECT agentId, profileId
FROM LIVEPERSON_USERPROFILES
WHERE agentId = :agentId
AND profileId = :idProfile";

$stmt = Conexao::getConnection()->prepare($sql);
$stmt->bindValue(":agentId",$agentId,PDO::PARAM_STR);
$stmt->bindValue(":idProfile",$idProfile,PDO::PARAM_STR);
$rows = $stmt->execute();

}
}

删除查询不起作用。

foreach ($result as $indUser => $userInfos){
$agentId = $userInfos['id'];
$dhInsert = date("Y-m-d H:i:s");
foreach ($userInfos['profileIds'] as $indProfile => $idProfile){
$sql = "DELETE FROM LIVEPERSON_USERPROFILES
WHERE NOT EXISTS (SELECT agentId, profileId
FROM LIVEPERSON_USERPROFILES
WHERE agentId = :agentId
AND profileId = :idProfile)";

$stmt = Conexao::getConnection()->prepare($sql);
$stmt->bindValue(":agentId",$agentId,PDO::PARAM_STR);
$stmt->bindValue(":idProfile",$idProfile,PDO::PARAM_STR);
$rows = $stmt->execute();

}
}

表格的数据：

insert into testTable (agentId, profileId) values ('1536989','12345')
insert into testTable (agentId, profileId) values ('1536989','56789')
insert into testTable (agentId, profileId) values ('1536989','99999')
insert into testTable (agentId, profileId) values ('1536874','56789')
insert into testTable (agentId, profileId) values ('1536874','56984')
insert into testTable (agentId, profileId) values ('7568594','99999')
insert into testTable (agentId, profileId) values ('8895874','56984')
insert into testTable (agentId, profileId) values ('8895874','56789')
insert into testTable (agentId, profileId) values ('8895874','77777')
insert into testTable (agentId, profileId) values ('8895874','99999')
insert into testTable (agentId, profileId) values ('1235689','56789')
insert into testTable (agentId, profileId) values ('1235689','77777')
insert into testTable (agentId, profileId) values ('1245365','56984')
insert into testTable (agentId, profileId) values ('1245365','56789')
insert into testTable (agentId, profileId) values ('1245365','77777')
insert into testTable (agentId, profileId) values ('1245365','99999')

Obs：一个agentId可以有多个profileId，因此，agentId是重复的。在表的数据中，我显示agentId 1536989有一个配置文件：12345、56789和99999。但在请求中，该代理只有1234556789的配置文件。所以我需要从表中删除技能9999(如果成功的话(。

Answer 1

您不需要子查询。

$sql = "DELETE FROM LIVEPERSON_USERPROFILES
WHERE agentId = :agentId AND profileId <> :idProfile)";

Answer 2

我是如何解决的：

首先，在我用来打开响应循环的foreach中，我制作了一个数组来保存这些值并在querystring中进行转换：

foreach ($result as $indUser => $userInfos){
$agentId = $userInfos['id'];
$dhInsert = date("Y-m-d H:i:s");
$forDelete[] = array(
'agentIdForDelete' => "'".$agentId."'",
'profileIdForDelete' => "'".implode("','",$userInfos['profileIds'])."'",
);

之后，我打印$forDelete并得到以下响应：响应json

然后我设置了一个循环来迭代索引并执行我的删除查询：

for ($i = 0; $i < count($forDelete); $i++){
$agentIdForDelete = $forDelete[$i]['agentIdForDelete'];
$profileIdForDelete = $forDelete[$i]['profileIdForDelete'];

$sql = "DELETE FROM LIVEPERSON_USERPROFILES
WHERE agentId = $agentIdForDelete AND profileId NOT IN ($profileIdForDelete)";
$stmt = Conexao::getConnection()->prepare($sql);
$stmt->execute();
}

这就是我找到的解决问题的方法。如果有人有其他建议，我可以：D