0

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

Alive to die - Anant
  • 70,531
  • 10
  • 51
  • 98
hasan hasan
  • 65
  • 7
  • 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. – Alive to die - Anant May 07 '19 at 07:01
  • 1
    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 May 07 '19 at 07: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 May 07 '19 at 07:18
  • Related: [is-storing-a-delimited-list-in-a-database-column-really-that-bad](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) – Paul Spiegel May 07 '19 at 07:26

2 Answers2

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 !

Mickaël Leger
  • 3,426
  • 2
  • 17
  • 36
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'.

Daan
  • 191
  • 10