Member Avatar for reco21

Hello,

I'm trying to get results from a mysql database based on values in an array.

My tables look like this


  1. id | articles | thumbs | etc

  2. tag_id | tag_name | article_id

I'd like to get related articles based on the tags for that particular article. So if an article has books, drawings, cartoons for tags, then I would like to get articles from the database that have those exact tags, only.

I have this query below which gets related articles if an article has ONE or MORE of the tags associated with it.

$tagcom=array("books","drawings","cartoons");
$tagcoms = implode("', '", $tagcom);
        $query = mysql_query("SELECT DISTINCT id,articles,thumb FROM articles,tags WHERE tags.tag_name IN ('$tagcoms') AND id != '$articleid'") or die(mysql_error());

How would I construct this to get related articles that only have the $tagcom array values. Not articles with more tags or less tags?

Thanks a lot :)

Edited by reco21
  • 2 Contributors
  • 1 Reply
  • 1K Views
  • 5 Hours Discussion Span
  • Latest Post Latest Post by diafol
Member Avatar for diafol

Your 2nd table looks a little wonky. I'd leave off the article_id and set up a link table called art_tag:

article_id | tag_id

That way you can count(tag_id) for each article_id when you search (if = 3, Bingo!)

Assume $search is an array containing all plain text and cleaned search terms:
So your tables:
articles, tags, art_tag

$countstrings = count($search);
$searchstring = "'" . implode("','",$search) . "'";

$sql = "SELECT COUNT(art.tag_id) AS hits, a.* FROM articles AS a INNER JOIN art_tag AS arttag ON a.article_id = arttag.article_id INNER JOIN tags AS t ON arttag.tag_id = t.tag_id WHERE t.tag IN ($searchstring) GROUP BY a.article_id HAVING hits = $countstrings"
Edited by diafol
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.