Fetch rows from a table if their ids are in a serialized value in another table-CodePudding

I want to fetch values from one table (people) if their ids are within the serialized data of another table (groups).

In the table groups I have this row that contains this serialized data stored:

a:18 {
i:0;s:1:"7";
i:1;s:2:"13";
i:2;s:2:"14";
i:3;s:2:"16";
i:4;s:2:"28";
i:5;s:2:"42";
i:6;s:2:"46";
i:7;s:2:"79";
i:8;s:2:"81";
i:9;s:2:"94";
i:10;s:3:"149";
i:11;s:3:"219";
i:12;s:3:"234";
i:13;s:3:"264";
i:14;s:3:"266";
i:15;s:3:"270";
i:16;s:3:"273";
i:17;s:3:"285";
}

Here's converted to an array so it's easier to look at

array (
  0 => '7',
  1 => '13',
  2 => '14',
  3 => '16',
  4 => '28',
  5 => '42',
  6 => '46',
  7 => '79',
  8 => '81',
  9 => '94',
  10 => '149',
  11 => '219',
  12 => '234',
  13 => '264',
  14 => '266',
  15 => '270',
  16 => '273',
  17 => '285',
)

I have another table, people, that has thousands of rows with data on those people (name, surname, mail), as you can tell from the array I want to fetch specifically the rows whose ids are in that groups' row (7, 13, 14, 16, 28, 42, 46, 79, 81, 94, 149, 219, 234, 264, 266, 270, 273 and 285).

I'm able to work in PHP with individual values within the table groups easily using this regex code

SELECT * FROM groups WHERE people REGEXP CONCAT('i:[0-9]{1,3};s:[0-9]{1,3}:.', ?, '.;')
/* Where ? is the specific id I want. */

Let's say I query this

SELECT * FROM groups WHERE people REGEXP 'i:[0-9]{1,3};s:[0-9]{1,3}:"7";'

That will indeed fetch me all the rows in the table groups that contain the id 7.

Now, what I need is to basically get name and surname from my table people but only those whose ids are in that specific data in groups. I'll write some non functioning code just so you can see my approach

SELECT name, surname
FROM people AS table1
IF id
IN (people REGEXP CONCAT('i:[0-9]{1,3};s:[0-9]{1,3}:.', table1.id, '.;')
FROM groups
WHERE id = 1)

Could you give me a hand? I'm certain regex has to be used and perhaps CASES but I have no experience with those. I want to get everything in a single query if possible.

CodePudding user response：

You need to join the tables.

SELECT p.name, p.surname
FROM people AS p
JOIN groups AS g ON g.people REGEXP CONCAT('i:[0-9]{1,3};s:[0-9]{1,3}:"', p.id, '";')
WHERE g.id = 1