Question posted in
You can find the official documentation here.

delete a value in comma delimited string in PHP – Jquery ajax

I have table in SQL Server as below :

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

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 '[email protected],%'   then REPLACE(shareWith, '[email protected],', '')
    when shareWith like '%,[email protected],%' then REPLACE(shareWith, ',[email protected],', ',')
    when shareWith like '%,[email protected]'   then REPLACE(shareWith, ',[email protected]', '')
    when shareWith = '[email protected]' 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.

Tags:

2

Answers


  1. 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.

    Login or Signup to reply.
  2. 0

    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:

    1. Converts email list into XML data type.
    2. Using XQuery to eliminate not needed email.
    3. 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', '[email protected],[email protected],[email protected]'), 
('Samsung Sr', '[email protected],[email protected]'),
('Xiaomi MFG', ''),
('Apple US st', '[email protected],[email protected]'),
('LG EU', '[email protected]');
-- DDL and sample data population, end

DECLARE @reportName VARCHAR(30) = 'IBM SH  data'
    , @emailToRemove varchar(30) = '[email protected]'
    , @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 | [email protected],[email protected]   |
|  2 | Samsung Sr   | [email protected],[email protected] |
|  3 | Xiaomi MFG   |                                |
|  4 | Apple US st  | [email protected],[email protected] |
|  5 | LG EU        | [email protected]                 |
+----+--------------+--------------------------------+
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search