sql string field contain an id

Question

I have in my database a table projects. In this table there is a row categories where are all the categories_id where a a project is in. For example a project is in category 1 and 2, so the row value is 1,2. What I want is to count all the projects in a category. So I try this:

select * from `projects` WHERE '{$id_cat}' IN (`categories`)

but it does not count the projects that have more than one category. I dont want to use LIKE

select * from `projects` WHERE `categories` LIKE '%{$id_cat}%'

because for example if I have a project that is in category 11 and I try to find all project in category 1 this query will return this value too.

It would be better to have a project / category lookup table. Storing multiple int ID's in a string field like this will only continue to cause you problems. — GrandMasterFlush, Feb 20 '14 at 16:08
@Kuzgun because if I want projects in category `1` if I use `like` it will return the projects from multiple categories. For exampla category `11` or `21` and so on — Andreea, Feb 20 '14 at 16:10

score 1 · Accepted Answer · answered Feb 20 '14 at 16:13

1

In MySQL, you can do this with find_in_set(). First, though, you can make the like work by adding delimiters on both sides:

select *
from `projects`
WHERE concat(',', `categories`, ',') LIKE '%,{$id_cat},%';

The equivalent find_in_set() is:

select *
from `projects`
WHERE find_in_set($id_cat, categories) > 0;

answered Feb 20 '14 at 16:13

Gordon Linoff

1,242,037
58
646
786

Finally I use `like` . I avoided to use it at first because the lack of comma at the beginning and end, but your idea is great. Thanks! – Andreea Feb 21 '14 at 07:21

score 0 · Answer 2 · edited May 23 '17 at 10:26

I would suggest that you re-structure your database as storing int ID's for foreign keys in a concatenated string will continue to cause you problems.

For example, assuming your database structure is like this (I've not done MySQL for a while so feel free to correct my syntax here):

Table: Project
--------------
ID   - INT (PK)
Name -  VARCHAR

Table: Category
---------------
ID   - INT (PK)
Name -  VARCHAR

You would have a join table structured like this:

Table: ProjectCategoryLookup
----------------------------
CategoryID - INT (FK to Category.ID)
ProjectID - INT (FK to Project.ID)

You could then return projects on a category ID by

SELECT * FROM Projects P
INNER JOIN ProjectCategoryLookUp LKP ON P.ID = LKP.ProjectID
WHERE LKP.CategoryID IN (11, 12)

Ideally, you'd make CategoryID and ProjectID the compound primary key for the ProjectCategoryLookup, as seen in this question.

I totally agree with you, but unfortunately I can not modify the datebase, I just have to do some statistics without changing anything in the structure. Thank for the answer, anyway. — Andreea, Feb 21 '14 at 07:04

sql string field contain an id

2 Answers2