How to check whether a value exists in an array that is stored in the database

47
January 13, 2019, at 09:30 AM

I have a database where moments contain a user id and friend id's. The friend_ids are saved as an array. I have a page where all the moments should be displayed. I want to check for moments which contains a certain id. For example I made a moment my own so my id is in the field user_id but also a friend of mine created a moment and so I am in the friend_ids. Now I need to check on this page if my id occurs in the user_id or friend_ids table. How can I check if a value exist in an array in a database? Do I have to fetch all records and loop through them to check the arrays or is there a better method?

The array looks like this in the database:

[2,3]   

And this is what my database schema looks like right now, i haven't set any relations between the tables yet.

Answer 1

Like Mureinik suggested, although he provided a solution to your question, you better represent your data better. With time, moments friend_ids field will get really big and will affect the performance.

In the following diagram you could see how I took *_ids column and set a dedicated pivot table for each one, that way you can get your friends easily by adding a JOIN clause.

Try to read more about models or entities relationship (you may find good example in Laravel documentation)

If you have any question, let me know.

Answer 2

As a general piece of advice, this isn't a great way to store things in a database. You'd probably be better off creating a junction table that holds the friendship status per pair of users.

If you can't do that, you could chop off the first and last characters ([ and ]), and use find_in_set on the remaining comma-delimited string:

SELECT *
FROM   mytable
WHERE  SELECT FIND_IN_SET(123, TRIM(TRAILING ']' FROM TRIM(LEADING '[' FROM friend_ids))) > 0
-- Just an example -------^ 
READ ALSO
How to count number of rows while using UNION in codeigniter?

How to count number of rows while using UNION in codeigniter?

In this question, I have two tables table1 and table2 where the tables structure of both are sameNow, I want to count number of rows

63
Errors installing IPSearch from GitHub

Errors installing IPSearch from GitHub

I got some few errors while installing IPSearch package from GitHub, that I couldn't solve with the answers of other people, in other forums too, so I was hoping that some of you could suggest solutions to the problems below

27
Is it possible to do a group_concat inside another group_concat?

Is it possible to do a group_concat inside another group_concat?

I'm building a webshop that sells shoesI'm trying to write a command that will show the quantity of each size for 1 specific sneaker

61
MySql incremental backup of only added lines

MySql incremental backup of only added lines

How can I create incremental backups of a MySql database that only includes data that is added and ignores data that has been removed?

26