在PHP中删除逗号分隔字符串的值

Question

我在SQL Server中有如下表:

+--------------+----------------------------------------------+
| reportName   | ShareWith                                    |   
+--------------+----------------------------------------------+
| IBM SH  data | [e-mail],[e-mail],[e-mail]  |  
| Samsung Sr   | [e-mail],[e-mail]               |
| Xiaomi MFG   |                                              |
| Apple US st  | [e-mail],[e-mail]               |
| LG EU        | [e-mail]                               |
+--------------+----------------------------------------------+

在我的php文件中，我有一个输入文本和一个按钮。当用户键入报表名称并单击按钮时，tt将执行ajax调用以从所选报表中删除当前用户电子邮件。

在SQL中应该如下:

select shareWith, UpdatedshareWith = 
case
when shareWith like '[e-mail],%'   then REPLACE(shareWith, '[e-mail],', '')
when shareWith like '%,[e-mail],%' then REPLACE(shareWith, ',[e-mail],', ',')
when shareWith like '%,[e-mail]'   then REPLACE(shareWith, ',[e-mail]', '')
when shareWith = '[e-mail]' then ''
else shareWith
end
from  table
where reportName = 'xxxx';

我试着在PHP中动态应用它，但不能使它工作。

$('#button').on('click', function(){

var reportName = x;
var username = y;
$.ajax({
type: "POST",
url: "delete.php",
data: { reportName : reportName,
username : username },
success: function(data) { console.log(data); }
});
});

和delete.php如下:

$stmt = $conn->prepare("UPDATE table SET shareWith = CASE 
WHEN shareWith like '?,%' THEN REPLACE(shareWith, '?,', '')
WHEN shareWith like '%,?,%' THEN REPLACE(shareWith, ',?,', ',')
WHEN shareWith like '%,?' THEN REPLACE(shareWith, ',?', '')
ELSE shareWith
END
WHERE reportName = ?");
$stmt->execute([$_POST['username'], $_POST['username'], $_POST['username'],
$_POST['username'], $_POST['username'], $_POST['username'], $_POST['reportName']]);
echo json_encode('deleted');

我相信有一种更干净的方法。有什么建议，请我应该改变我的代码吗?非常感谢。

Answer 1

您迫切需要修复模式。在字符串中存储多个值是错误的。但有时我们会被别人非常非常非常糟糕的决定所困扰。

我建议这样的结构:

UPDATE table
SET shareWith = TRIM(',' FROM REPLACE(CONCAT(',', ShareWIth, ','), CONCAT(',', ?, ',')) )
WHERE reportName = ? AND
CONCAT(',', shareWith, ',') LIKE CONCAT('%,', ?, ',%');

也就是说，通过在搜索字符串和模式周围放置分隔符来简化逻辑。

但是，我要重复一遍:您应该有一个表，每个报告和共享一个值。

代替更灵活的TRIM()，您可以使用空格和TRIM()和REPLACE()来代替:

SET shareWith = REPLACE(TRIM(REPLACE(REPLACE(CONCAT(',', ShareWIth, ','), CONCAT(',', ?, ','))), ',', ' '), ' ', ',')

这里假设字符串没有空格，这对于字符串中的电子邮件是有意义的。

Answer 2

请尝试以下解决方案。

可以从SQL Server 2012开始使用

它使用XML和XQuery来标记电子邮件列表，并删除不需要的电子邮件。您可以将其打包为具有两个参数的存储过程，@reportName和toremove.

CTE完成了所有繁重的工作:

1. 将邮件列表转换为XML数据类型。
2. 使用XQuery消除不需要的电子邮件。
3. 转换回逗号分隔的邮件列表。

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, reportName VARCHAR(30), ShareWith VARCHAR(1024));
INSERT INTO @tbl (reportName, ShareWith) VALUES
('IBM SH  data', '[e-mail],[e-mail],[e-mail]'), 
('Samsung Sr', '[e-mail],[e-mail]'),
('Xiaomi MFG', ''),
('Apple US st', '[e-mail],[e-mail]'),
('LG EU', '[e-mail]');
-- DDL and sample data population, end
DECLARE @reportName VARCHAR(30) = 'IBM SH  data'
, @emailToRemove varchar(30) = '[e-mail]'
, @separator CHAR(1) = ',';
;WITH rs AS
(
SELECT *
, REPLACE(TRY_CAST('<root><r><![CDATA[' + 
REPLACE(ShareWith, @separator, ']]></r><r><![CDATA[') + ']]></r></root>' AS XML)
.query('
for $x in /root/r[lower-case((./text())[1]) ne lower-case(sql:variable("@emailToRemove"))]
return data($x)
').value('.', 'VARCHAR(MAX)'), SPACE(1), @separator) AS modifiedShareWith
FROM @tbl
WHERE reportName = @reportName
)
UPDATE t
SET ShareWith = rs.modifiedShareWith
FROM @tbl AS t
INNER JOIN rs ON t.id = rs.id;
-- test
SELECT * FROM @tbl;

+----+--------------+--------------------------------+
| ID |  reportName  |           ShareWith            |
+----+--------------+--------------------------------+
|  1 | IBM SH  data | [e-mail],[e-mail]   |
|  2 | Samsung Sr   | [e-mail],[e-mail] |
|  3 | Xiaomi MFG   |                                |
|  4 | Apple US st  | [e-mail],[e-mail] |
|  5 | LG EU        | [e-mail]                 |
+----+--------------+--------------------------------+