Need advice on mysql table structure

[amrys]

Hello,

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:

Location:
-USA (LA/ NY/ San Francisco, ...)
- Europe(France/Germany, ...)
- Asia (China/Russia, ..)
Type:
-Health (Doctor, nurse, .. )
-Education (Teacher, Student, Lecturer)
Organization:
...
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

3. 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

[NogDog]

I would just have each entry in the relationship table define only one article-to-category relationship. That article_to_category relationship table would then only need 2 columns: article_id and category_id, with a unique index on the combination of those 2 columns. Then your article-to-category relationships become totally scalable, allowing any number of unique pairings.

[amrys]

The problem is that they are related - that's why I used 3 levels for relation, and I'm searching by 2 of them.
I have a problem constructing the query:

For example I have:
Table relations:
Relation_id, article_id, level1_rel, level2_rel, level3_rel
1, 1, 17, 19, 24
2, 1, 18, 25, 28
3, 2, 17, 19, 22
4, 2, 18, 25, 26

Those are 2 articles
- 1st -> USA/LA and Education/Lecturer
- 2nd: ->USA/LA and Education/Student

Now somebody searches for USA/LA and Education/Lecturer
I have:

SELECT articles.*, relations.* FROM articles LEFT JOIN relations ON articles.article_id = relations.article_id WHERE articles.user_id = '2' AND articles.article_id IS NOT NULL AND ( category_3 = 22 OR category_3 = 26 ) GROUP BY articles.article_id

22:LA
26:Lecturer

As a result I need only article 1, that has both LA and Lecturer, however I'm getting as result both articles, as they both have category_3 values 22 OR 26. I need the query to say only records that have both 22 and 26, not or. Looking in Google I think I need an INNER JOINT with nested SELECT, but I'm having trouble building that query.

Thank you

[amrys]

Ok, just for reference, I think I figured it out:

$search_query="";
if ($cat1>0){
$search_query.=" AND r.category_2 = ".$cat1;
}
if ($subcat1>0){
$search_query.=" AND r.category_3 = ".$subcat1;
}
if ($cat2>0){
$search_query.=" AND r1.category_2 = ".$cat2;
}
if ($subcat2>0){
$search_query.=" AND r1.category_3 = ".$subcat2;
}


$sql="SELECT article.article_id, article.article_title, r.category_1, r.category_2, r.category_3, r1.category_2, r1.category_3
FROM (article INNER JOIN relation as r ON article.article_id = r.article_id) INNER JOIN relation as r1 ON r1.article_id=r.article_id
WHERE article.user_id='{$this->user_id}' ".$search_query."
GROUP BY article.article_id
ORDER BY article_order_number ASC, article_title DESC
";