Forum Moderators: open
I am trying to create a search solution for a database of Articles. I need the query string to return ArticleID's on LIKE matches in two tables (Articles and Authors) so that a user can search for text within the article, or the Article Authors name.
However Article and Authors and linked by a 3rd table, ArticleAuthorsLinks because any given article may have more than 1 contributing Author.
Therefore the search string must be queried in Articles (easy) and authors. Then for Author matches lookup the Article that author has written via the ArticleAuthorsLink table.
I have managed to create 2 seperate SELECTs, 1 for Author search, one for Articles search below (where $trimm = search term):
$ArticlesSearch = "SELECT ID FROM Articles WHERE Title LIKE \"%$trimm%\" OR Abstract LIKE \"%$trimm%\"";
$AuthorsSearch = "SELECT ArticleAuthorLinks.ArticleID FROM ArticleAuthorLinks, Authors WHERE ArticleAuthorLinks.AuthorID = Authors.ID AND (Authors.Surname LIKE \"%$trimm%\" OR Authors.Forename LIKE \"%$trimm%\")";
Any help much appreciated.
Rob
SELECT DISTINCT
Articles.ID
FROM
ArticleAuthorLinks,
Authors,
Articles
WHERE
Authors.ID = ArticleAuthorLinks.AuthorID
AND Articles.ID = ArticleAuthorLinks.ArticleID
AND (
Authors.Surname LIKE \"%$trimm%\"
OR Authors.Forename LIKE \"%$trimm%\"
OR Articles.Title LIKE \"%$trimm%\"
OR Articles.Abstract LIKE \"%$trimm%\"
)The DISTINCT is necessary to prevent an article being returned multiple times when the WHERE condition is matched more than once for a single article (eg a search for "john" and an article has two authors "John Smith" and "John Bloggs").
You should also implement code to prevent SQL injection if you're haven't already, eg by using mysql_real_escape_string.
