How to push a value into an existing array of mysql table using php-CodePudding

I have a table of column name "access" which has array values [2,3,4] Now I want to push a single value in the last part of the array. if my value is '7' so after pushing the array should be [2,3,4,7]

Here's the access column.

This is my access column

I used this but i know it won't happen because it removes all the existing values

$id = '7'
UPDATE user SET access = '$id' WHERE name = '$user'

CodePudding user response：

Putting comma-separated values into a SQL column is a misuse of SQL, and that's why you can't find any clean way to do this.

If you must do things this way, try this.

UPDATE user SET access = CASE 
         WHEN access IS NULL OR access = '' THEN $id
         WHEN FIND_IN_SET($id,access) IS NOT NULL THEN access
         ELSE CONCAT_WS(',',access,$id) END
 WHERE whatever;

The first WHEN handles the situation where access doesn't contain anything. The second handles the case where your $id value is already in access. And the ELSE appends your $id value.

But if you can, do this the SQL way instead. Try creating a separate table called user_access. Give it a user and an access column. Then INSERT a row to the table to add an $id, and DELETE one to remove. Why? You have a many::one relationship between access $id values and users, and that's done with a table.

CodePudding user response：

you can use the array_push() function in a MySQL UPDATE statement.

$id = '7';

$sql = "UPDATE user SET access = array_push(access, '$id') WHERE name = '$user'";

CodePudding user response：

It should be inserting not updating

Insert into table(access)values(7)