OK, doing the selects is pretty straight forward once you get yourself organised.
// 1. Find article ids for a given subject id
"SELECT article_id FROM article_subjects
WHERE subject_id=$id"
// 2. same for a given subject
"SELECT article_id FROM article_subjects INNER JOIN subjects USING(subject_id)
WHERE subject='$subject'"
Now you just join to the articles table if you want the article data as well.
Problems start to arise when you want to find articles using multiple subjects.
// find articles with either of 2 subjects
"SELECT DISTINCT article_id FROM article_subjects
WHERE subject_id=$id1 OR subject_id=$id2"
// note the use of DISTINCT to eliminate duplication
// you can also use a comma separated string of ids
$idstring = implode(',' , $subject_array);
$query = "SELECT DISTINCT article_id FROM article_subjects
WHERE subject_id IN ($idstring)";
// better when you don't know how many subjects a user will choose
The real problems arise when you want to select articles that match all subjects chosen, especially when you don't know how many subjects there will be.
The best way to do this is by grouping and counting so that you exploit the duplication that DISTINCT was used to elliminate
$subject_count = count($subject_array);
$idstring = implode(',' , $subject_array);
$query = "SELECT article_id FROM article_subjects
WHERE subject_id IN ($idstring)
GROUP BY article_id
HAVING COUNT(subject_id)=$subject_count";
That should get you started 🆒
