select data from mysql array

Question

In my MySQL database I have an array column (text type) called friend_array where I have the variables usernames separated by comma (,)

for example: user1 friend_array value in cell is:

prevert,
borges,
eliot,
wislawa,
derek,
hebert,
pushkin,
plath,
donnelly,
ahlberg,
ginsberg,
amiri,
rilke,
mckay,
aam,
wowen,
larkin,
michaux,
moliver,
dickinson,
rumi,
reverdy,
brecht,
tate,
cad,
cummings,
dianeb,
sexton,
redson,
pessoa,
transtromer,
shakespeare,
mistral,
sodergran,
bukowski,
auden,
tedhu,
ashton,
tomandrews,
gwallas,
updike,
ashbery,
jenkins,
rhass,
whitehead,
montale,
ritvo,
snyder,
kooser,
simic,
koertge,
nemerov,
aporter,
reichhold,
rduncan,
meretop,
kkoch,
strand,
kahf,
asha,
keats,
pinter,
santoka,
opaz,
breton,
schreiner,
bataille,
cedo,
galeano

now how to select each one of the friends separately in php in order to view friend list without repeating

thanks in advance

saving comma separated data is against NORMALIZATION rules and it will be very hard to do search, CRUD operation on those records. Try to save each friend_array data separately in db. — Death-is-the-real-truth
– Death-is-the-real-truth, Commented May 7, 2019 at 7:01
It seems you should read about the basics of relational databases. The question is not how to select, but how to store IMHO. — U. Windl
– U. Windl, Commented May 7, 2019 at 7:02
if data is in single key array you can try $usernames[0] = "prevert,borges,eliot,wislawa,derek,hebert,pushkin,plath,donnelly,ahlberg,ginsberg,amiri,rilke,mckay"; $pieces = explode(",", $usernames[0]); echo $pieces[0]; — Rasa Mohamed
– Rasa Mohamed, Commented May 7, 2019 at 7:18
Related: is-storing-a-delimited-list-in-a-database-column-really-that-bad — Paul Spiegel
– Paul Spiegel, Commented May 7, 2019 at 7:26

Mickaël Leger · Accepted Answer · 2019-05-07 07:40:05Z

1

You should create a new table friend that looks like this for example:

friend
===============
id_user | username

with a primary key on id_user1 + username, this way to get all friend of your user1 you can just do SELECT username FROM friend WHERE id_user = :id_user1;. Having this kind of field with a string that contains multiple value you need to select is really bad practice as said in comment.

Now to answer the question, if you manage to get the friend_array value (a string with multiple username separate by comma as you said), you can do :

$data = explode(',', $your_friend_array_string);

If you want to remove duplicate :

$data_unique = array_unique($data);

Hope it helps, but start by changing the way you store your data it if possible !

answered May 7, 2019 at 7:40

Mickaël Leger

3,4482 gold badges21 silver badges37 bronze badges

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

Comments

Daan · Accepted Answer · 2019-05-07 07:42:17Z

0

Whilst I absolutely agree you should absolutely work towards setting op relations and use normalization (ie: create a friends table). The question is if this is possible without making a repeating query.

It is like this:

SELECT
    a.name, 
    b.name 
FROM user a 
LEFT JOIN user b ON FIND_IN_SET(b.name, a.friends)
WHERE 
   a.name = 'name';

It's not pretty and if you have ANY way to edit the database structure you should do that and abandon this 'solution'.

answered May 7, 2019 at 7:42

Daan

19110 bronze badges

Collectives™ on Stack Overflow

select data from mysql array

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related