Filtering Out Duplicate Content

[Elven6]

I'm looking into ways of filtering out duplicate content on my CMS, currently the way it does things is if you post a news piece across three different platforms it will appear three times on the front page. I want to change it so it will only show up once on the front page if multiple content is detected.

An example,

http://thatgamingsite.com

The content MySQL is structured like this:

id (database id)
name (article name)
cat (the category it's in, ex: news, review, etc)
username (the poster username)
date (date timestamp)
ver (whether the content is on hold for submission, 0 is no and 1 is yes)
postpone (if you want to post at a later date)
stats (views and such)
lev (if it can be viewed by those logged out)
games (any games it's attached to)
system (any systems it's attached to)

The only way I can think of doing it is by matching both the date and name so if both the name and date of an entry match it will only display 1 of them since the two will never match at different intervals. (so if you post "Hello World" today the filter system won't recognize it as a duplicate next week since the timestamp will be different) The code I was trying to use hasn't been doing anything, infact it doesn't show anything at all when inserted!

mysql_query("SELECT * FROM ".$pre."content WHERE name = '".$name."' AND date = '".$date."'");

The functions.php area that controls the content fetch is,

function content($cat, $num, $template) {
global $siteurl;
global $dformat;
global $part1;
global $part2;
global $pre;

$sql = mysql_query("SELECT * FROM ".$pre."templates WHERE name = '".$template."'");
while($user = mysql_fetch_array($sql)) {
	$temp = stripslashes($user[template]);
}

    if ($cat == "") {
    $queryrra = mysql_query("SELECT * FROM ".$pre."content WHERE ver = '0' AND postpone = '' ORDER BY `id` DESC LIMIT ".$num);
	} else {
	$queryrra = mysql_query("SELECT * FROM ".$pre."content WHERE cat = '".$cat."' AND ver = '0' AND postpone = '' ORDER BY `id` DESC LIMIT ".$num);
	}
    while ($row = mysql_fetch_array($queryrra)) {
	$cat = $row[cat];
	$systems = $row[systems];

	$short = mysql_fetch_row(mysql_query("SELECT data FROM ".$pre."fielddata WHERE id2 = '".$row[id]."' AND name = 'Short' AND cat = 'content'"));
	
	$image = mysql_fetch_row(mysql_query("SELECT data FROM ".$pre."fielddata WHERE id2 = '".$row[id]."' AND name = 'image' AND cat = 'content'"));
    
	$nicon = mysql_fetch_row(mysql_query("SELECT data FROM ".$pre."fielddata WHERE id2 = '".$row[id]."' AND name = 'newsicon1' AND cat = 'content'"));

	$systemsa = mysql_fetch_row(mysql_query("SELECT icon,name FROM ".$pre."systems WHERE id = '".$systems."'"));

        $pryr[0] = "/{name}/";
		$pryr[1] = "/{link}/";
		$pryr[2] = "/{date}/";
		$pryr[3] = "/{cat}/";
		$pryr[4] = "/{icon}/";
		$pryr[5] = "/{username}/";
		$pryr[6] = "/{Short}/";
		$pryr[7] = "/{newsicon1}/";
		$pryr[8] = "/{image}/";
		$aryr[0] = stripslashes($row[name]);
		$aryr[1] = $part1.$row[id].fthrough($row[name], $row[cat]).$part2;
		$aryr[2] = date($dformat, $row[date]);
		$aryr[3] = $row[cat];
		if ($systemsa[0]) {
		$aryr[4] = "<img src='".stripslashes($systemsa[0])."'>";
		} else {
		$aryr[4] = "";
		}
		$aryr[5] = "<a href='index.php?load=elite&user=".$row[username]."'>".$row[username]."</a>";
		$aryr[6] = stripslashes($short[0]);
		$aryr[7] = $nicon[0];
		$aryr[8] = "<img src='".stripslashes($image[0])."' width='153' height='86'>";

eval (" ?>" . preg_replace($pryr, $aryr, $temp) . " <?php ");
}
}

Any ideas as to what I'm doing wrong?

 

[sourcez]

Why don't you put a new field in for something like unique_id - have it so each article only has one id across all 3 copies?

 

[Elven6]

How would that work? Wouldn't that essentially be similar to the name/date stuff in terms of how it would be filtered out?

 

[sourcez]

Well it would allow you to display things on your frontpage by unique_id rather than having to search through and double check there weren't multiple postings.

Thing is though, don't you want it to come up for each platform? Which platform image would be shown?

 

[Elven6]

It depends, a news post could be linked to Xbox 360, PS3, and PC, or any combination of a variety of platforms, there could also be news that isn't attached to a platform.

So if "Test Post" was attached to the DS, Xbox 360, and PS2 it should only show up for 1 of the platforms on the main page.

 

[beaver6813]

It depends, a news post could be linked to Xbox 360, PS3, and PC, or any combination of a variety of platforms, there could also be news that isn't attached to a platform.

So if "Test Post" was attached to the DS, Xbox 360, and PS2 it should only show up for 1 of the platforms on the main page.

The core issue is that there shouldn't be duplicate content in the database at all. When you store the post dont store one copy for DS, one for Xbox, one for PS3. Instead if you want it to appear in multiple categories, in the category field use: DS,Xbox,PS3 and then just have one copy of the post.

 

[hawkaw]

Have you tried experimenting with the DISTINCT-statement in SQL? If not, Google "SQL DISTINCT" or check out this tutorial at w3schools.com

 

[Elven6]

The core issue is that there shouldn't be duplicate content in the database at all. When you store the post dont store one copy for DS, one for Xbox, one for PS3. Instead if you want it to appear in multiple categories, in the category field use: DS,Xbox,PS3 and then just have one copy of the post.

I've really never thought of why it was like that, I guess in case you wanted to make them somewhat distinct later on? I'll look into other methods.

Have you tried experimenting with the DISTINCT-statement in SQL? If not, Google "SQL DISTINCT" or check out this tutorial at w3schools.com

I think that might be what I need, thanks for the link!