Saving arrays in database – PDO

PHP

I want to save an array in my database friends column. Actually I want to create friend request system. My method is a little bit different. I want to have two columns for this system.

______________________________________________________________________
| Name   | Friends                   |  Requests                     |
| Name1  | array(person1,person2..)  |  array(request1, request2..)  |
| Name2  | array(person1,person2..)  |  array(request1, request2..)  |
|________|___________________________|_______________________________|

When name1 adds name2 the name1 name must be put in requests of name2 when name2 accepts the requests the name1 will be removed from name2 requests and added to both name1 and name2 friends.

Now the problem is that I have no idea how to put an array into a column cell and how to get the name when needed from it by a select query. I have just worked with simple queries a lot but not this kind of thing.

Best Answer

You should re-evaluate your database design. A relational database is not designed to store arrays in columns. Actually, elements in arrays are "columns" itself. So, what you're trying to do is store multiple columns in a single column...

I'd recommend two tables: Persons (PersonId, Name,...) Friends (FriendsId, PersonId, FriendId, Accepted), where both, PersonId and FriendId have a foreign key to Persons.PersonId. The column Accepted will be Zero as long as it is not accepted. When accepted, just change it to 1. Therewith, you don't have to move rows arond and take care that there are no inconsistent data (or even missing data).

Related Question