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

Question

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'

So, those aren't array values but simple text. Use concat() as Ken Lee suggested or normalize your data, one row per access level — user8034901
– user8034901, Commented Dec 31, 2022 at 12:16

O. Jones · Accepted Answer · 2022-12-31 12:29:02Z

2

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.

answered Dec 31, 2022 at 12:29

O. Jones

110k17 gold badges134 silver badges187 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Tanvir Over a year ago

It didn't work. when access is empty it is putting value but when there is a value already it did nothing.

Tanvir Over a year ago

Thanks for your second suggestion. but I have almost 1000+ access id for a single user. so if I create a separate table than there will be 1000+ rows for every user. is it a good idea? I have very little knowledge about it.

O. Jones Over a year ago

Yes, it is a good idea. SQL databases are far better at handling your use case with a separate table than they are at handling very long strings of comma-separated numbers.

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related