PHP/MySQL Script: Array in a Field

[mooeypoo]

Hi guys,

 

I'm programming a new web application with PHP/mySQL and I need to connect the table ID to a specific field and later on fetch that field with a list of ID numbers.

 

The tblUsers (containing all the user data) has a field called "uFriends" where all the user's chosen friends IDs are listed.

 

At first, I just put them in the field as "text" with commas between multiple ids, but that cause a problem; When I want to show how many people chose a user to be in their 'friends' list, and use the SQL query:

 

Query="SELECT * FROM tblUsers where uFriends LIKE '".$UserID."';";

 

I =wanted= to pick all the people that have the user id in their friends list, but that causes my script to also show other IDs. For instance, if a user ID is 1, then the script above also shows people who has the numbers 10,11,12.., 21,31,41... in that field.

 

I then thought I'd add a comma at the end of everything, always (even if the user has only one ID number in his list) and use "explode(",",$row["uFriends"])" on it, but that caused an empty field to appear, and got the code a bit messy with adding an unnecessary comma at the end and taking the empty field off before showing the list of users.

 

Also, if I want to remove only ONE number from the list in the uFriends field, it gets really annoying if it is in simple text with commas.

 

So I thought I'd create a binary array and just fetch it and use it with "explode()" command. But I have no idea how to do that. I know it's possible, I am just not sure how.

 

Anyone? Ideas?

 

 

Thanks!

 

~moo

[Aeternus]

Why not move things out into another table called user_friends or something? In that table you could have a - user_id | friend_id - and then you can simply query that table for all friends of a particular user id. That makes it easy to avoid any possible length of field problems (ie if you are using something with a fixed or at least a maximum string length, you will reach a point where you won't be able to add more friends) and searching for friends will be a hell of a lot faster as you won't have to use LIKE on every field and you can index on either or both of the fields in this new table depending on what you are usually searching for.

 

This would also make it easier to remove things as with your current method you have to move around that string and remove things from the middle etc whereas with this it is a simple delete query.

[bascule]

Aeternus has it

 

For a has and belongs to many (HABTM) relationship, you want to use a join table

[mooeypoo]

Still, that doesn't help me with searching the field for a single number.. even if I split the tables, I still have a field with multiple numbers that I need to make sure is in the table in a way that is easy to search in... Or did I miss anything?

 

~moo

[Aeternus]

The idea is that you don't have your friends field but instead have another table with the fields user_id and friend_id. When a person has a friend, you add a row to the friends table (user_id = their_id, friend_id = their_friends_id). You can then get a list of a users friends by just getting all rows in the friends table for a given user_id, or if you want to see who has X as a friend, you just search for all the rows with the friend_id=X etc. You can even get all the data about these friends (ie their name etc from the users table) by doing JOIN with the user table.

 

This also allows you to add additional information later on about a particular friendship (for instance you might want to store the time the friendship started so you can say "you have been friends for X"), as this would simply be an additional column in the friends table.

[mooeypoo]

aaaaaaaaaaaaahhhhhhhhh!!!

 

Awsome Idea!! I didn't think about it... Okay, so I will create another database called tblUserFriends, that has the fields userID, friendID and just link them. That would also give me the option to search better and add/delete better.

 

Wooh! awsome, hehe, thanks Aeternus.

 

Just a tiny question still: Is this "wasting" space in the tables? instead of having 1 field full of a list of - say - 10 friends, I have 10 fields... should I be worried about space?

 

I don't quite know mySQL core handling (in terms of space and such) so if this question is rediculous, i do appologize.

 

THANKS FOR THE SOLUTION!! I'm bouncing up and down in joy! hehe

 

~moo

[Dave]

It's probably best to explain with some examples. Say user 1 has friends 2, 3, 7, 18 and 30. Then you'd have a user_friends table that contained the following entries:

 

userid | friendid
-------+---------
1      | 2
1      | 3
1      | 7
1      | 18
1      | 30

 

Then, to get a list of the friends that that user has, we use the SQL query:

 

SELECT u.name FROM users AS u, user_friends AS uf WHERE uf.userid = u.user

 

Or similar. To get a list of the users and how many friends they have, try:

 

SELECT u.name, COUNT(*) AS count FROM users AS u, user_friends AS uf WHERE uf.userid = u.userid GROUP BY u.userid

 

There's lots of variations on the theme.

[Dave]

Just a tiny question still: Is this "wasting" space in the tables? instead of having 1 field full of a list of - say - 10 friends' date=' I have 10 fields... should I be worried about space?

 

I don't quite know mySQL core handling (in terms of space and such) so if this question is rediculous, i do appologize.[/quote']

 

Not at all. Even with a link table with hundreds of thousands of entries, you're not talking about immense amount of space. The SFN database has over 200,000 posts (which incidentally has two copies of each post, one cached, the other raw) and it only takes up about 100mb or so. So for a table with two integer fields, you're not talking about immense amounts of space.

[mooeypoo]

Awsome, good to know.

 

IT WORKS!! yay, I didn't think about it, and it solved the entire thing. I actually started reading about serialize() routine in PHP to add an array into my DB, which just complicated the entire bit and didn't solve anything. But with such a simple solution, my entire problem is solved!

 

Thanks guys

 

~moo