delete a value in comma delimited string in PHP

Question

I have table in SQL Server as below :

+--------------+----------------------------------------------+
| reportName   | ShareWith                                    |   
+--------------+----------------------------------------------+
| IBM SH  data | jack@gmail.com,alex@gmail.com,jan@gmail.com  |  
| Samsung Sr   | alex@gmail.com,peter@gmail.com               |
| Xiaomi MFG   |                                              |
| Apple US st  | maria@gmail.com,alex@gmail.com               |
| LG EU        | fred@gmail.com                               |
+--------------+----------------------------------------------+

In my php file, I have an input text and a button. When a user type a report name and click on the button tt will perform an ajax call to remove the current user email from the selected report.

In SQL it should be as below :

select shareWith, UpdatedshareWith = 
  case
    when shareWith like 'alex@gmail.com,%'   then REPLACE(shareWith, 'alex@gmail.com,', '')
    when shareWith like '%,alex@gmail.com,%' then REPLACE(shareWith, ',alex@gmail.com,', ',')
    when shareWith like '%,alex@gmail.com'   then REPLACE(shareWith, ',alex@gmail.com', '')
    when shareWith = 'alex@gmail.com' then ''
    else shareWith
end
from  table
where reportName = 'xxxx';

I'm trying to apply it dynamically in PHP but couldn't make it work.

$('#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); }
  });

});

and delete.php as below :

$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');

I believe there is a cleaner way to do it. Any suggestions please what should i change in my code ? Thank you very much.

Normalize the schema. See ["Is storing a delimited list in a database column really that bad?"](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad). — sticky bit, Feb 10 '21 at 11:59
thanks for commenting. I know it's a bad practice but the table is created by another team and they use it that way, I can't change that. — DevTN, Feb 10 '21 at 12:04
@DevTN, if you are using SQL Server 2017+, a possible option is to split the `ShareWith` column with `STRING_SPLIT()` and aggregate the results again with `STRING_AGG()`. Note, that `TRIM()` is also introduced in SQL Server 2017, so you need to change the code in the @Gordon Linoff's answer. — Zhorov, Feb 10 '21 at 13:19

Gordon Linoff · Answer 1 · 2021-02-10T15:24:44.503

0

You desperately need to fix the schema. Storing multiple values in a string is just wrong. But sometimes we are stuck with other people's really, really, really bad decisions.

I would suggest this structure:

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

That is, simplify the logic by putting delimiters around the searched string and the pattern.

However, I will repeat: you should have a table with one value per report and share.

Instead of the more flexible TRIM(), you can use spaces and TRIM() and REPLACE() instead:

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

This assumes that the string doesn't have spaces, which makes sense for emails in a string.

edited Feb 10 '21 at 15:24

answered Feb 10 '21 at 12:03

Gordon Linoff

1,242,037
58
646
786

thanks for your answer. unfortunately i can't change the table structure as it was created by another team and they are using that one. One question please, I didn't understand your query what you mean by " ',' FROM REPLACE" because I am getting incorrect syntax error. is there sth missing ? – DevTN Feb 10 '21 at 13:47
@DevTN . . . That is `TRIM()` functionality introduced in SQL Server 2017: https://learn.microsoft.com/en-us/sql/t-sql/functions/trim-transact-sql?view=sql-server-ver15. – Gordon Linoff Feb 10 '21 at 13:53
thanks. I was reading about it but I meant how you use keywords FROM REPLACE together.. also I would need an alternative solution because at work we are all using SQL Server 2012 – DevTN Feb 10 '21 at 14:11

Yitzhak Khabinsky · Answer 2 · 2021-02-10T15:36:31.673

Please try the following solution.

It will work starting from SQL Server 2012 onwards.

It is using XML and XQuery to tokenize email list, and remove not needed email. You can package it as a stored procedure with two parameters, @reportName and @emailToRemove.

The CTE does all the heavy lifting:

Converts email list into XML data type.
Using XQuery to eliminate not needed email.
Converts back to comma separated email list.

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', 'jack@gmail.com,alex@gmail.com,jan@gmail.com'), 
('Samsung Sr', 'alex@gmail.com,peter@gmail.com'),
('Xiaomi MFG', ''),
('Apple US st', 'maria@gmail.com,alex@gmail.com'),
('LG EU', 'fred@gmail.com');
-- DDL and sample data population, end

DECLARE @reportName VARCHAR(30) = 'IBM SH  data'
    , @emailToRemove varchar(30) = 'alex@gmail.com'
    , @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;

Output

+----+--------------+--------------------------------+
| ID |  reportName  |           ShareWith            |
+----+--------------+--------------------------------+
|  1 | IBM SH  data | jack@gmail.com,jan@gmail.com   |
|  2 | Samsung Sr   | alex@gmail.com,peter@gmail.com |
|  3 | Xiaomi MFG   |                                |
|  4 | Apple US st  | maria@gmail.com,alex@gmail.com |
|  5 | LG EU        | fred@gmail.com                 |
+----+--------------+--------------------------------+

delete a value in comma delimited string in PHP

2 Answers2