php - Update and delete data in database mysql

Question

I use codeigniter. i have following values as json_encode in database row, i want delete value 3 in row 1 and values 14 & 5 in database table.

DELETE -> value 3 on row 1 & values 14, 5 on rows 2
UPDATE -> value 2 to 8 on row 1 & value 3 to 17 on rows 2.

How is query it for values json?

Row 1:

id=1

[{
    "hotel_id": ["3"]
}, {
    "hotel_id": ["2"]
}, {
    "hotel_id": ["1"]
}]

Row 2:

id=2

[{
    "hotel_id": ["14"]
}, {
    "hotel_id": ["5"]
}, {
    "hotel_id": ["4"]
}, {
    "hotel_id": ["3"]
}, {
    "hotel_id": ["2"]
}, {
    "hotel_id": ["1"]
}]

Answer 1

What most of the commenters are trying to say is that your problem is a very common one when storing serialized data in the DB (like JSON strings) - That is exactly why this is considered bad practice. All the wonderful tools of relational databases, like indexing, sorting, grouping and querying according to any column are gone once the data is stored as a big pile of characters the DB can't parse.

The solution is to save the actual DATA in the DB, and format it as JSON only on the application side, for use when needed. In your case, you can create a table like hotels_to_users (I am doing some guesswork here about the meaning of your data, hoping you get it even if it's not exactly what you need). This table will look like:

user_id (?) |  hotel_id
1           |  1
1           |  2
2           |  14

This will make it very easy to query, update, insert and delete any specific hotel to any user.

Hope this makes sense and that you are able to make that change, otherwise your problem can be solved with either some MySQL string functions like REPLACE and SUBSTR, or by doing all the work in the application code and saving a new JSON when done. Either way, maintaining it will be hard and require more effort in the long term.