relating articles across website

[mattennant]

Hi There

 

I'm trying for the first time to relate articles across a site i am building

 

the idea is that everytime say for example a news item is added in the cms, the admin will check  'check boxes'  of the categories that  story falls into

 

[] backpain

[] fitness training

[] alternative therapy etc etc

 

at the moment i am storing the array as a comma seperated array in a column called keywords (i realise there could be issues here)

 

so far all well and good i retrieve the data as fllows

do {  
$nkw = explode(',',$row_news['keywords']);
if (($row_news['article_id']) ==(in_array($row_article['keywords'], $nkw))){
echo '<li><a href="mat_news.php?article_id=' . $row_news['article_id'] . '" target="_self" title="'.$row_news['headline'] .'">' . $row_news['headline'] . '</a></li>';
}
} while ($row_news = mysql_fetch_assoc($news2));

 

this works fine if there is an exact match from the checked checkboxes, but i want to relate the articles even if there is just a single match from one of the checkboxes, i'm sure this must be simple, but i've been toying with this for ages

 

any help much appreciated

 

mat

 

 

 

 

[DarkWater]

You need to normalize your tables to get functionality like that in the proper manner, so you aren't able to use a comma separated list of values in one column.  I'd have 3 tables:

 

articles:

id INT NOT NULL AUTO_INCREMENTING,

message TEXT NOT NULL

#maybe more columns depending on what you want, idk

 

categories:

id INT NOT NULL AUTO_INCREMENTING,

name VARCHAR(100) NOT NULL

 

article_cats:

id INT NOT NULL AUTO_INCREMENTING,

article_id INT NOT NULL,

category_id INT NOT NULL

 

Then it's a simple join.

[mattennant]

Thanks darkwater, thought that might well be the case.

 

Is there though a solution to solve this problem, in a kinda unproper manner,before i head down the normalised route, or am i just making more work for myself??

 

thaks for your time

 

Mat

[DarkWater]

You're totally making more work for yourself by not normalizing.

[mattennant]

hello

 

have followed darkwaters advice and now have three tables

 

• articles - contains article_id  - headline- description etc
  
• keywords - keyword_id - backpain - fitness training etc
  
• keyword_rank keywordrank_id  - keyword_id - article_id - rank
  

 

everytime a new article is added the user s/he checks a checkbox if that keyword is relevent eg

 

[]backpain

[]fitness training

etc etc

these checked items are stored as rank in the keyword_rank  (at the moment as a 1)

 

by query is how best to get the related article to run alongside the article

 

at the moment i have the following (keyword_id 4 is fitness training)

SELECT * FROM keyword_rank  LEFT JOIN articles ON articles.article_id = keyword_rank. article_id WHERE keyword_id = 4   ORDER by keyword_rank.rank DESC

 

this is fine to relate one keyword, but when two or more are checked i run into problems, i've thought about putting the statement in a loop corresponding to the number of keywords selected, but this would result in the same row getting selected more than once,  thinking that there must be a much slicker solution,