0

I'm working on a mysql database select and cannot find a solution for this tricky problem.

There's one table "words" with id and names of objects (in this case possible objects in a picture).

words

ID object

  1. house
  2. tree
  3. car
  4. …

In the other table "pictures" all the information to a picture is saved. Besides to information to resolution, etc. there are especially informations on the objects in the picture. They are saved in the column objects by the ids from the table words like 1,5,122,345, etc.

Also the table pictures has a column "location", where the id of the place is written, where I took the picture.

pictures

location objectsinpicture ...

1 - 1,2,3,4

2 - 1,5,122,34

1 - 50,122,345

1 - 91,35,122,345

2 - 1,14,32

1 - 1,5,122,345

To tag new pictures of a particular place I want to become suggestions of already saved information. So I can create buttons in php to update the database instead of using a dropdown with multiple select.

What I have tried so far is the following:

SELECT words.id, words.object 
FROM   words, pictures   
WHERE  location = 2 AND FIND_IN_SET(words.id, pictures.objectsinpicture)
GROUP BY words.id
ORDER BY words.id

This nearly shows the expected values. But some information is missing. It doesn't show all the possible objects and I cannot find any reason for this.

What I want is for example all ids fo location 2 joined to the table words and to group double entries of objectsinpicture:

1,5,122,34
1,14,32

1,5,14,32,34,122

  1. house
  2. ...
  3. ...
  4. ...
  5. ...
  6. ...

Maybe I need to use group_concat with comma separator. But this doesn't work, either. The problem seems to be where condition with the location.

I hope that anyone has an idea of solving this request.

Thanks in advance for any support!!!

Björn
  • 1
  • You might want to look at https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad – Progman Jun 16 '21 at 17:04

1 Answers1

1

This is a classic problem of denormalization causing problems.

What you need to do is store each object/picture association separately, in another table:

create table objectsinpicture (
  picture_id int,
  object_id int,
  primary key (picture_id, object_id)
);

Instead of storing a comma-separated list, you would store one association per row in this table. It will grow to a large number of rows of course, but each row is just a pair of id's so the total size won't be too great.

Then you can query:

SELECT w.id, w.object 
FROM   pictures AS p
JOIN   objectsinpicture AS o ON o.picture_id = p.id
JOIN   words AS w ON o.object_id = w.id
WHERE  p.location = 2;
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Hey Bill, thanks a lot for your quick response. In other cases I would do that but in this case I have decided to use this solution because the pictures table is joined to many other tables, too. Is there a chance to first of all select all objectsinpicture where the location is 2, then order the comma-separated values ASC and then group them in ONE SELECT in SQL? As an alternative in PHP: Maybe I could write the group_concated objectsinpicture to an array and then reduce the array in php and then match each entry against the table words with in_array. What do you think? – Björn Jun 16 '21 at 17:18
  • MySQL doesn't have an array type. You don't have a list of discrete values, you have a string. Look, this is a solved problem in relational database design, and you're choosing to disregard that. The solution is on you now. Good luck! – Bill Karwin Jun 16 '21 at 18:36
  • Ok, I'm learning… :-) And I'm already beginning to implement your suggestion. – Björn Jun 16 '21 at 18:42
  • 1
    Hey Bill, I already got it working! :-) It was easier than I thought. Thanks a lot for your support. – Björn Jun 16 '21 at 19:08