I think I'm stuck at this problem now and I'm not sure how to proceed.
Here's the task: a php site with different registered users.
Every user can create a directory.
Every directory can be searched by categories, the users create those categories.
Unlimited number of categories, with up to 2 sub-categories each.
I'm explaining this a little complicated, but what it is is - for example - the user needs the following directory:
-USA (LA/ NY/ San Francisco, ...)
- Europe(France/Germany, ...)
- Asia (China/Russia, ..)
-Health (Doctor, nurse, .. )
-Education (Teacher, Student, Lecturer)
Scale: - so on
For every category of those they have letï¿½s say an article. Every article can be placed under different categories, of level2 or 3. For example USA->LA && Health && Education->Lecturer.
We need to search by categories and get the articles as result.
So far I have:
1. Table Categories:
Category_id, user_id, category_label, category_parent
2. Table articles
Article_id, user_id, article_title, article_text
- Table relations:
Relation_id, article_id, level1_rel, level2_rel, level3_rel
So I know this table structure is far from perfect and I would love to know the better way to do that, but at this point the most important thing is to get this done as soon as possible, as Iï¿½m very late with the project.
So if thereï¿½s a way just to come up with a query for the search ï¿½ will be great. Otherwise Iï¿½m curious to other ways as well.
Someone needs to be able to search for an article, letï¿½s say USA, San Francisco AND Health/Doctors or Just Education, or just Asia, or not select a category to see all of that user.
(They cannot select just teacher, but can select only Education)
Thank you in advance