Related article search (using tags)
Moderator: General Moderators
Related article search (using tags)
I am working on an article manager. For every article i have a tag defined (similar to flickr).
For example when i access mysite.com/pages?page=1 then suppose it's tags are life,confidence,success,honesty
Now below the actual article content i want to show all the articles avaiable in the database WHICH HAVE ANY ONE OF THE TAGS OR MORE TAGS ASSOCIATED WITH IT. That means any other article (in same table) should be displayed which have the same tag associated witrh them
Table name: articles
Database name: mysqldb
So how do i write the search query?
For example when i access mysite.com/pages?page=1 then suppose it's tags are life,confidence,success,honesty
Now below the actual article content i want to show all the articles avaiable in the database WHICH HAVE ANY ONE OF THE TAGS OR MORE TAGS ASSOCIATED WITH IT. That means any other article (in same table) should be displayed which have the same tag associated witrh them
Table name: articles
Database name: mysqldb
So how do i write the search query?
feyd | Please use
For displaying i am using something like[/syntax]
feyd | Please use
Code: Select all
,Code: Select all
and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
[quote="xpgeek"]Give us tables structure.[/quote]
For the table structure below. Suppose i am viewing a article (using a display page given below)
then the current article's tags must be searched in wow table and correspondingly if any tags match with any other article that article should be displayed (it's link and title)
The tags are read from db as $row['tags'] they are stored as a string separated by ,
example. tags: love,life,story,clever
[syntax="sql"]
CREATE TABLE `wow` (
`id` int(11) unsigned NOT NULL auto_increment,
`catid` int(11) NOT NULL default '1',
`contributed_by` text,
`title` tinytext,
`content` text,
`date` bigint(20) NOT NULL default '0',
`mood` int(2) NOT NULL default '0',
`tags` text,
`views` int(11) NOT NULL default '0',
`rating` float NOT NULL default '0',
`votes` int(11) NOT NULL default '0',
`trusted` tinyint(1) NOT NULL default '0',
`modified` tinyint(1) NOT NULL default '0',
`ip` varchar(80) NOT NULL default '',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;
--
-- Dumping data for table `wow`
--
INSERT INTO `wow` VALUES (1, 1, 'preet', 'some content', 'more edit23', 1188193696, 5, 'kgjhgjhkgjhgkhj', 269, 3.61, 106, 0, 0, '127.0.0.1');
INSERT INTO `wow` VALUES (2, 1, 'dfghdfsh', 'rkpdshsfdhfsd', 'rkp -vithfsdhsfh', 1188193640, 5, 'fdhggfsh', 100, 2.89, 10, 0, 0, '127.0.0.1');
INSERT INTO `wow` VALUES (3, 1, 'rahul khanna', 'Old lady', 'the story is here', 1188219512, 3, 'old,lady,love', 138, 2.5, 14, 0, 0, '127.0.0.1');
INSERT INTO `wow` VALUES (4, 1, 'Test User', 'The Clever Dog', 'Some content is displayed', 1114324233, 1, 'dog,clever', 336, 3.45, 125, 0, 0, '127.0.0.1');
Code: Select all
if(!isset($_GET['page'])){
//20
$page = 1;
}else{
if(ctype_digit($_GET['page'])){
$page=trim(mysql_real_escape_string($_GET['page']));
}else{
//30
echo "invalid query";
exit;
}
}
if(!isset($_GET['catid'])){
$catid = 1;
}else{
if(ctype_digit($_GET['catid'])){
$catid=trim(mysql_real_escape_string($_GET['catid']));
}else{
echo "invalid query";
exit;
}
}
// Define the number of results per page
//40
$max_results = 1;
// Figure out the limit for the query based
// on the current page number.
$from = (($page * $max_results) - $max_results);
// Perform MySQL query on only the current page number's results
//50
$countviews = mysql_query("UPDATE wow SET views=views+1 WHERE id=$page");
$sql = mysql_query("SELECT * FROM wow WHERE `trusted` = 1 ORDER BY `id` ASC LIMIT $from, $max_results");
while($row = mysql_fetch_array($sql)){
echo "<!--start-->";
// Build your formatted results here.
//60
echo "<font size=3><b>";
echo $row['title']."</b><br /></font>";
echo "<font size=2>";
echo "Contributed By ";
echo $row['contributed_by'];
//70
echo " on ";
echo date("d-m-Y H:i:s", $row['date'])."<p>";
echo $row['content']."</p><br />";
echo "<!--end-->";feyd | Please use
Code: Select all
,Code: Select all
and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]Database Search Problem
I am writing something like
But the tags which are stored in db are separated by commas example life,success,destiny (can be any number of tags separated by comma)
and if i write $row['tags'] it will take it as a complete string and try to match which will give the article itself as a result of search query.
I want to search wow table if any of the articles (other than the current) have tags keyword "life" , "success" , "destiny" all three or any of the one then only those results should be displayed search related articles with tags
That means it should search all the keywords in $row['tags'] separately(separated by comma)
Code: Select all
SELECT DISTINCT id FROM wow WHERE 0 OR CONCAT(title,content,views,'') LIKE '%$row['tags']%' ORDER BY id DESC;and if i write $row['tags'] it will take it as a complete string and try to match which will give the article itself as a result of search query.
I want to search wow table if any of the articles (other than the current) have tags keyword "life" , "success" , "destiny" all three or any of the one then only those results should be displayed search related articles with tags
That means it should search all the keywords in $row['tags'] separately(separated by comma)
While it's possible to make it work with your table structure, the proper way to store a series of values is not as a delimited string in one field. That violates relational database normalization rules, which say that every field should be "atomic", which means that the value cannot be broken down into subordinate values.
The proper design is to have tags in a separate table, with a record for every instance of an image/tag combination.
The proper design is to have tags in a separate table, with a record for every instance of an image/tag combination.
Each tag in one row (separate table)
That means for the article section i should have a separate tags table called article_tags (or something) and then store ALL THE TAGS in the tags table with the corresponding id or the article.califdon wrote: The proper design is to have tags in a separate table, with a record for every instance of an image/tag combination.
One thing the tags should be stored as one tag per row?
something like
id , tags , articleid should have values like
1 , life , 2 in 1st row then 2,success , 2 in 2nd row. I mean each tag of a particular particle in each row
As above articleid 2 has 2 tags associated then they should come in two row.
Am i correct now?
- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
That's not how I do it. I use 3 tables: article, tag, and article_tag_xref. Obviously articles go in the article table with a unique id for each one. I then have a tag table in which I store each unique tag with an id. The article_tag_xref table is a simple many-to-many cross reference table storing an article id and a tag id. Eg:
Both articles have tag 3 (Shoes) assigned to them. All I do to find articles with the same tag is a join. It's pretty straightforward stuff. The only complicated bit is editting the tags ... I have to check each one is in the database and either fetch the existing ID or insert it when I edit an article. That's not too much of a challenge though, and while it's a bit hard on the database it's only something that happens when things are updated or created, not when articles are selected from the database.
Code: Select all
Article table:
Article id Title
1 Article Number One
2 Article Number Two
Tag table
Tag id Title
1 Coat
2 Hat
3 Shoes
4 Jacket
Article_tag_xref table
article_id tag_id
1 1
1 3
1 4
2 2
2 3Thanks onion2k,onion2k wrote: Both articles have tag 3 (Shoes) assigned to them. All I do to find articles with the same tag is a join. It's pretty straightforward stuff. The only complicated bit is editting the tags ... I have to check each one is in the database and either fetch the existing ID or insert it when I edit an article. That's not too much of a challenge though, and while it's a bit hard on the database it's only something that happens when things are updated or created, not when articles are selected from the database.
Actually i have bit complication here, I have different sections like articles,quotes,abc and xyz (around 5 different sections currently).
In Each section the content is identified by unique id.
And EACH content in a PARTICULAR SECTION has tags associated to it.
Now how to relate different section, content id in a section and tags in the content ?
Ok, But how do i keep the tags unique and check if the tags are not repeated bufore entering them into the tag table.onion2k wrote:I'd still keep all the unique tags in one table, but I'd have several xref tables ... one for quotes_tags_xref, another for abc_tags_xref, and so on.
The user will enter something like life,success,dream then i have to use explode and then add dream, success and life in the tags table IF THEY DON"T EXIST. How do i check this after i explode and reference each tag as $piece[$i]; where is is total count of array
In short from what user enters i separate the tags and enter into db after checking for duplicate tags. How to do this?
You check to see if the tag exists with a select. Basically:kkonline wrote:Ok, But how do i keep the tags unique and check if the tags are not repeated bufore entering them into the tag table.
The user will enter something like life,success,dream then i have to use explode and then add dream, success and life in the tags table IF THEY DON"T EXIST. How do i check this after i explode and reference each tag as $piece[$i]; where is is total count of array
In short from what user enters i separate the tags and enter into db after checking for duplicate tags. How to do this?
Code: Select all
$article_id = 1;
$tags = explode(",","Coat,Hat,Shoes,Socks");
foreach ($tags as $tag)
{
$sql = "select tag_id from tag where tagname = '$tag'";
$result = mysql_query($sql,$databaseLink);
if (mysql_num_rows($result) == 1)
{
$record = mysql_fetch_object($result);
$tag_id = $record->tag_id;
} else {
$sql = "insert into tag (tagname) values ('$tag')";
mysql_query($sql,$databaseLink);
$tag_id = mysql_insert_id($databaseLink);
}
$sql = "insert into article_tag_xref (article_id,tag_id) values ($article_id,$tag_id)";
mysql_query($sql,$databaseLink);
}