PHP/MySQL checking for duplicate content in database

Question

Im loading an xml file with tweets and then adding it a to a Mysql database. Up to this point it works fine but id like to do a basic check on the url field to see if its already added in the database. If its in the database already id like to update the mention count else insert it as a new entry. Its a real newbie question but im struggling to get it working. Heres the code as it stands now:

<?php 

require_once('functions/functions.php');

$dbhost = '****';
$dbuser = '****';
$dbpass = '****';

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');

if($conn)
 {
    echo "Database Connection Successfull...<br /><br />";
 }

$dbname = '****';
mysql_select_db($dbname) or die('Couldnt connect to database table');

if($dbname)
{
    echo "Database ".$dbname." Selected..<br /><br />";
}

  $tweetmeme = "http://api.tweetmeme.com/stories/popular.xml?category=sports-soccer&count=30" ; 

  $xml = @simplexml_load_file($tweetmeme) or die ("no file loaded") ; 
  if($xml)
  {
    echo "Tweetmeme XML loaded with ".count($xml->stories->story)." stories in the file..<br /><br />";
  }
 if(get_magic_quotes_gpc())
 {
    echo "Magic Quotes is ON<br /><br />";
 }
 $count_insert=0;
 $count_update=0;

 //Select url and id and compare to the url being added
 $url_compare = mysql_query("SELECT id,url FROM ft_tweets");


 if($url_compare)
 {
    //echo_result($url_compare);
    echo mysql_num_rows($url_compare)." rows loaded.<br /><br />";
 }

 $url_compare = mysql_fetch_assoc($url_compare);

foreach($xml->stories->story as $story)
{
    $title=check_input($story->title);


    $url=check_input($story->url);

    $media_type=check_input($story->media_type);

    $created=check_input($story->created_at);

    $url_count=check_input($story->url_count);

    $comment_count=check_input($story->comment_count);

    $excerpt=check_input($story->excerpt);



    $sql = "INSERT INTO ft_tweets (title,url,media_type,created_at,mention_count,comment_count,excerpt) VALUES ($title,$url,$media_type,$created,$url_count         ,$comment_count,$excerpt)";


    $result = mysql_query($sql) or die(mysql_error());
    if($result)
    {
        //echo "added to database<br />";
        $count_insert++;
    }
}
echo $count_insert." Records added to the database<br /><br />";
$search=mysql_query("SELECT * FROM ft_tweets WHERE title LIKE '%liverpool%' OR excerpt LIKE '%lfc%'");
if($search)
{
    echo "<br /><br />Found tweets mentioning Liverpool";
}
//fetch 10 tweets from ft_tweets where liverpool or lfc is in the excerpt or the title and store it in a variable
//echo_result($search);
echo "<br />";
var_dump($search);
echo "<br />";
?>

BBonifield · Accepted Answer · 2010-09-16 23:39:16Z

1

Seems like you just need to check for a duplicate tweet first.

$sql = "SELECT id FROM ft_tweets WHERE url = '" . mysql_real_escape_string( $url ) . "'";
$duplicate = mysql_query( $sql );
if ( mysql_num_rows( $duplicate ) > 0 ) {
  // some notice about duplicate tweet
  $count_duplicate++;
} else {
  // insert the new
  $sql = "INSERT INTO...";
}

answered Sep 16, 2010 at 23:39

BBonifield

5,01321 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Baadier Sydow Over a year ago

not working for me, it runs but doesnt pick up any of the duplicate url's in the database. The logic looks fine so maybe its the format of url before it goes into the database

jcubic · Accepted Answer · 2010-09-16 23:45:41Z

1

If you set url as Key you can use INSERT ... on DUPLACATE KEY UPDATE sytax.

answered Sep 16, 2010 at 23:45

jcubic

67.1k58 gold badges252 silver badges466 bronze badges

2 Comments

Chris Ghenea Over a year ago

this solution is more... elegant :) but you'll need MySQL 5.0+

Baadier Sydow Over a year ago

are you suggesting as opposed to using an arbitary id for the primary key you use the url or just making the url have a unique property in the database

Collectives™ on Stack Overflow

PHP/MySQL checking for duplicate content in database

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related