Power By 专否社区

Skip if value exist on SQL table

Go To StackoverFlow.com

0

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

2012-04-04 07:00
by Ricardo Capistran
A few pointers regarding your code. Use pdo instead of mysql_* functions. 2nd is, if you see there is too much elseif, use switch-case - itachi 2012-04-04 07:05

2

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...

2012-04-04 07:04
by web-nomad
Note that both are MySQL-specific - ThiefMaster 2012-04-04 07:06

0

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

2012-04-04 07:08
by starl1ng
Ads