I want to store an array in a record.
Table1:
ID, Name, Friends (friends should be an array) 1, Bill, 2&3 2, Charles, 1&3 3, Clare, 1
I want to be able to do a search like this:
SELECT * FROM Table1 WHERE Friends='3'
to find everyone who has Clare listed as a friend
Unless you have a really good reason for doing this, you should keep your data normalized and store the relationships in a different table. I think perhaps what you are looking for is this:
CREATE TABLE people (
id int not null auto_increment,
name varchar(250) not null,
primary key(id)
);
CREATE TABLE friendships (
id int not null auto_increment,
user_id int not null,
friend_id int not null,
primary key(id)
);
INSERT INTO people (name) VALUES ('Bill'),('Charles'),('Clare');
INSERT INTO friendships (user_id, friend_id) VALUES (1,3), (2,3);
SELECT *
FROM people p
INNER JOIN friendships f
ON f.user_id = p.id
WHERE f.friend_id = 3;
+----+---------+----+---------+-----------+ | id | name | id | user_id | friend_id | +----+---------+----+---------+-----------+ | 1 | Bill | 1 | 1 | 3 | | 2 | Charles | 2 | 2 | 3 | +----+---------+----+---------+-----------+ 2 rows in set (0.00 sec)
This looks like the perfect place for a relation table instead: Table 1:
ID, Name
1, Bill
2, Charles
3, Clare
Table 2 (the relation table)
ID, FriendID
1, 2
1, 3
2, 1
2, 3
3, 1
The second table keeps track of the friend relations by connecting ID's from Table1.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With