I have a question regarding SQL, i gave a small script which gets from twitter what people are saying about somebody and inserting it on a sql table from my server, the code works well, but my question is, how can the script skip a entry when it already exist on the table, so it dosent insert several times the same entry, here is the code:
<?php
include '../config/config.php';
mysql_connect($dbhost, $dbuser, $dbpwrd) or die(mysql_error());
mysql_select_db($database) or die(mysql_error());
$rss = simplexml_load_file("http://search.twitter.com/search.rss?q=YaninGarciaD");
if($rss){
echo '<ul>';
foreach($rss->channel->item as $item){
$author = (string) $item->author; // Title
$link = (string) $item->link; // Url Link
$description = (string) $item->description;
$pubDate = (string) $item->pubDate; //Description
list($usuario, $nombre3) = explode("@", $author);
list($basura, $nombre2) = explode(" (", $nombre3);
$nombre = substr($nombre2, 0, -1);
$piezasurl = explode("/", $link);
$status = $piezasurl[5];
$piezasfecha = explode(" ", $pubDate);
if ($piezasfecha[2] == "Jan") {
$mes = "01";
} elseif ($piezasfecha[2] == "Feb") {
$mes = "02";
} elseif ($piezasfecha[2] == "Mar") {
$mes = "03";
} elseif ($piezasfecha[2] == "Apr") {
$mes = "04";
} elseif ($piezasfecha[2] == "May") {
$mes = "05";
} elseif ($piezasfecha[2] == "Jun") {
$mes = "06";
} elseif ($piezasfecha[2] == "Jul") {
$mes = "07";
} elseif ($piezasfecha[2] == "Ago") {
$mes = "08";
} elseif ($piezasfecha[2] == "Sep") {
$mes = "09";
} elseif ($piezasfecha[2] == "Oct") {
$mes = "10";
} elseif ($piezasfecha[2] == "Nov") {
$mes = "11";
} elseif ($piezasfecha[2] == "Dic") {
$mes = "12";
}
$fecha = $piezasfecha[3].'-'.$mes.'-'.$piezasfecha[1].' '.$piezasfecha[4];
mysql_query("INSERT INTO yg_mensajes
(nombre, mensaje, twitter, url, postid, tipo, fecha, aprovado)
VALUES ('$nombre', '$description', '$usuario', '$link', '$status', 2, '$fecha', 0 ) ")
or die(mysql_error());
echo '<li>';
echo $description.'<br />';
echo $usuario.'<br />';
echo $nombre.'<br />';
echo $link.'<br />';
echo $status.'<br />';
echo $fecha.'<br />';
echo '<br /></li>';
}
echo '</ul>';
}
?>
A good thing about twitter is that the post number (in this code, is the var $status) is unique, that is the var that shouldn't be in the table before
You can see the script in action here: http://www.yaningarcia.com/unete/test.php
My SQL Table structure is this one:
CREATE TABLE `yg_mensajes` (
`id` int(11) NOT NULL auto_increment,
`nombre` text,
`mensaje` text,
`twitter` text,
`url` text,
`postid` text,
`tipo` int(11) default NULL,
`fecha` datetime default NULL,
`aprovado` int(11) default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
Thanks!! :D
you can use INSERT IGNORE...
or INSERT...ON DUPLICATE UPDATE..
commands.
http://www.electrictoolbox.com/mysql-insert-ignore/
http://www.mysqlperformanceblog.com/2006/05/29/insert-on-duplicate-key-update-and-summary-counters/
Hope it helps...
I think, you need to create index on postid field and use INSERT IGNORE command. Or use INSERT...ON DUPLICATE KY UPDATE, but in this case you need to update one or more field(s) http://dev.mysql.com/doc/refman/5.6/en/insert.html