Related article search (using tags)

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
kkonline
Forum Contributor
Posts: 251
Joined: Thu Aug 16, 2007 12:54 am

Related article search (using tags)

Post by kkonline »

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?
User avatar
xpgeek
Forum Contributor
Posts: 146
Joined: Mon May 22, 2006 1:45 am
Location: Kyiv, Ukraine
Contact:

Post by xpgeek »

Give us tables structure.
kkonline
Forum Contributor
Posts: 251
Joined: Thu Aug 16, 2007 12:54 am

Post by kkonline »

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');
For displaying i am using something like[/syntax]

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]
kkonline
Forum Contributor
Posts: 251
Joined: Thu Aug 16, 2007 12:54 am

Database Search Problem

Post by kkonline »

I am writing something like

Code: Select all

SELECT DISTINCT id FROM wow WHERE 0 OR CONCAT(title,content,views,'') LIKE '%$row['tags']%' ORDER BY id DESC;
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)
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Post by califdon »

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.
kkonline
Forum Contributor
Posts: 251
Joined: Thu Aug 16, 2007 12:54 am

Each tag in one row (separate table)

Post by kkonline »

califdon wrote: The proper design is to have tags in a separate table, with a record for every instance of an image/tag combination.
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.

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?
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

Yes.
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post by onion2k »

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:

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              3
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.
kkonline
Forum Contributor
Posts: 251
Joined: Thu Aug 16, 2007 12:54 am

Post by kkonline »

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.
Thanks onion2k,

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 ?
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post by onion2k »

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.
kkonline
Forum Contributor
Posts: 251
Joined: Thu Aug 16, 2007 12:54 am

Post by kkonline »

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.
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?
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post by onion2k »

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?
You check to see if the tag exists with a select. Basically:

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);

}
Post Reply