CODEWITHSUNDEEP
phpmysql
Random Guy
Random Guy

Reputation: 2900

Remove particular id from database field

Am storing a string separated using |, which lists the groups allowed, now my issue is if I delete a group than I am not able to remove the ID from that particular field, for example

allowed_group_id
+---------------+
1332|4545|5646|7986

So for example am deleting the group say no 5646, so how do I alter the scripts and remove that particular group from the allowed_group_id in script table?

Upvotes: 0

Views: 282

Answers (6)

George D
George D

Reputation: 2365

Using Suresh response and improving it:

UPDATE TABLE tableName SET allowed_group_id = REPLACE(
    REPLACE(allowed_group_id, '5646', ''),
    '||',
    '|');

First you search for '5646' string in allowed_group_id and replace it with empty string ''. Secondly you search and replace two bars from your result '||' with only one bar '|'. This will avoid having '1332|4545||7986' in your allowed_group_id.

Upvotes: 1

giorgio
giorgio

Reputation: 10202

Use explode:

$allowed_ids = explode('|', $current_allowed_group_ids);
if($remove_key = array_search($remove_id, $allowed_ids) !== false) {
    unset($allowed_ids[$remove_key]);
}
$update_query = 'UPDATE table_name SET allowed_group_id = "'. implode('|', $allowed_ids) .'" WHERE id= ...';

But you might want to alter your database design slightly, creating a pivot table to check for allowed ids. Example:

+------------+
|   GROUPS   |
+----+-------+
| id | name  |
+----+-------+
| 1  | grp_1 |
| 2  | grp_2 |
  ...

+--------------------+
|   ALLOWED_GROUPS   |
+--------------------+
| user_id | group_id |
+---------+----------+
|   2     |    1     |
|   2     |    2     |
|   5     |    2     |
   ...

Upvotes: 1

cmbuckley
cmbuckley

Reputation: 42468

Whilst the other answers will solve your problem as-is: you might want to consider normalising your database.

For example, if you currently have a table table_name containing id and allowed_group_id, then you would create a new table allowed_group containing multiple rows for each allowed group:

foreign_id | group_id
-----------+---------
1          | 1332
1          | 4545
1          | 5646
1          | 7986

...and so on. Here, foreign_id is the ID of the row in your existing table_name table. Now, instead of your above problem, you can simply DELETE FROM allowed_group WHERE foreign_id = 1 AND groupId = 5646.

This is called putting your data in first normal form.

Upvotes: 0

JEY
JEY

Reputation: 7123

Or you should have a table with 2 columns 1 with the id of the action you whant to allow and one with the id of a group.

Upvotes: 0

Vlad Preda
Vlad Preda

Reputation: 9920

I recommend taking the entry, exploding it by "|", removing the appropriate entry, imploding it back and updating.

$allowedGroupId = '1332|4545|5646|7986';
$parts = explode('|', $allowedGroupId);
if(($key = array_search($deleteGroup, $allowedGroupId)) !== false) {
    unset($allowedGroupId[$key]);
}

$update = " ... "; //update with the new imploded values

Hope it helps

Upvotes: 1

Suresh Kamrushi
Suresh Kamrushi

Reputation: 16086

You can try this- 

update table tableName set allowed_group_id = REPLACE(allowed_group_id, '5646', '');

Upvotes: 1

Related Questions