Forum Moderators: open
I want to be able to apply certain keywords or tags to each article and am wondering what is my best approach.
I was looking at creating a keyword cell in the row, where I can specify a tag for the article.
While this approach has several benefits, including allowing me to dynamically output a list of unique tags (which is very important), it does have one major problem -- it would limit me to one tag per article.
I thought about creating different cells, keyword1, keyword2, keyword3, etc... While a pain to setup, and not very flexible if demand increases, this would allow me to specify tags for upto however many cells I have precreated, but being able to efficiently and dynamically output a list of unique tags would be challenging.
I also thought about creating just one cell for my keyword, but in this cell I would concatenate my keywords together: keyword1;keyword2;keyword3 etc... This would free me from any limits to keywords I would want to use, but I think this would be my least efficient approach, and still being able to dynamically output a list of unique tags would still be a challenge.
Lastly I thought maybe creating a seperate table where a row would just contain an id number (to link it to the corrisponding article) and a keyword. I would be able to create an unlimited number of keywords, (just create another row with a matching id number), and I would easily be able to efficintly and dynamically output a list of unique tags used. However, I'm personally not a fan of this approach as it would store seperatly my keywords from the articles (seems messy and sloppy), and would double my table requests.
Would would you recommend? One of my ideas above, or maybe theres a better idea out there I didn't even think about. Please let me know your feedback, thanks.
It may seem messy to you as a person but to a DBMS it is just fine and your DB will thank you (with speed and less cpu and disk usage) for making things relational!
JAG
I am however facing one major obsticle before I can implament a change like this.
Right now I'm outputing some data from my first table like this:
$result=mysql_query("SELECT * FROM table1 ORDER BY published DESC LIMIT 5");
while($data=mysql_fetch_array($result)){
echo "<p>".$data["title"]."; filed under: ".$data["keyword"]."</p>";
}
How might I efficiently lookup all applicable keywords for each article?
Would my approach be something like this:
$result=mysql_query("SELECT * FROM table1 ORDER BY published DESC LIMIT 5");
while($data=mysql_fetch_array($result)){
echo "<p>".$data["title"]."; filed under: ";
$result2=mysql_query("SELECT * FROM table2 WHERE published='$published'";);
while($data2=mysql_fetch_array($result2)){
echo $data2["keyword"].", ";
}
echo "</p>";
}
To me this seems really sloppy and inefficient. I originally had only one lookup, now I'm at six!
Any advice? Thanks.
I originally had only one lookup, now I'm at six!
Ahh. Yes. You'll want to do a join where you'll just join the 2 tables together on your 'published' value and get all the records back in one query. Joins can be a bit confusing sometimes so just read up on the join syntax at the mysql site and you'll be running in no time.
Yours will look something like:
SELECT * FROM table1, table2 INNER JOIN table2 ON table1.published=table2.published;
JAG
Thank you so much, the join function looks extremely beneficial! I will definitely need to research that.
I'll have to do some research, but I'm wondering though if this might be a problem for me given that table2 will have several rows that correspond with table1 when an article has multiple keywords associated with it. Would this still be a possible solution?
Thanks
Remember also that you will still have all the normal controls with your where clause, group by, order by etc.
Have fun!
JAG
