CODEWITHSUNDEEP
phpmysql
hasan hasan
hasan hasan

Reputation: 65

select data from mysql array

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

Upvotes: 0

Views: 1064

Answers (2)

Daan
Daan

Reputation: 191

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'.

Upvotes: 0

Mickaël Leger
Mickaël Leger

Reputation: 3440

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 !

Upvotes: 1

Related Questions