Page 1 of 1

SQL column type issue

Posted: Thu Apr 12, 2012 5:35 am
by social_experiment
I'm retrieving tweets from a rss feed page; the code i use works but when i inserted it into the database i found this - The guid valued retrieve from the document is an 18 character integer value so i set the type of the column to int(28). As soon as i started running the code the value in that column wasn't the 18 character integer but this value 2147483647. After some changes i decided to change the type of the column to varchar(18) and it solved the problem. Is this an sql issue?

I tested the data received from the feed and when i printed it the 18 character integers were displayed; the code i use

Code: Select all

<?php
$handle = @file_get_contents('http://twitter.com/statuses/user_timeline/user_.rss');
	
	if ($handle != false) {		
		// 
		$xml = new SimpleXMLElement($handle);
		
		// connection to database		
		
		$total = count($xml->channel->item) - 1;			
		$updated = 0;
		$notUpdated = 0;
			
		for ($i=0; $i<= $total; $i++) {
			//
			preg_match('/\d{18,}/', $xml->channel->item[$i]->guid, $match);			
			//			
			$guid = mysqli_real_escape_string($sqli, $match[0]);
			$description = mysqli_real_escape_string($sqli, $xml->channel->item[$i]->description);
			$pubDate = mysqli_real_escape_string($sqli, $xml->channel->item[$i]->pubDate);			
			//
			$qry = "SELECT COUNT(`id`) FROM `twitter_tbl` WHERE `guid` = '" . $guid . "' ";
			$sql = mysqli_query($sqli, $qry);
			$ary = mysqli_fetch_array($sql);
			$rows = $ary[0];			
			//
			if ($rows == 0) {
				$qry = "INSERT INTO `twitter_tbl` (`description`, `pubdate`, `guid`) 
				VALUES ('" . $description . "', '" . $pubDate . "', '" . $guid . "')";
				$sql = mysqli_query($sqli, $qry);								
			}			
		}				
	}	
?>

Re: SQL column type issue

Posted: Thu Apr 12, 2012 5:45 am
by mikosiko
INT(28) doesnt means anything considering that an INTEGER datatype could only store values 10 digits maximum.

two links that will explain that to you
http://dev.mysql.com/doc/refman/5.5/en/ ... types.html
and
http://dev.mysql.com/doc/refman/5.5/en/ ... rflow.html

did you tried using a BIGINT (19 digits maximum)?

Re: SQL column type issue

Posted: Thu Apr 12, 2012 5:47 am
by Celauran
Sounds like the numbers were bigger than INT can handle. You'd need to use BIGINT. See here.

Re: SQL column type issue

Posted: Thu Apr 12, 2012 5:55 am
by social_experiment
mikosiko wrote:did you tried using a BIGINT (19 digits maximum)?
Not yet;

thanks for the answers, something that occurs to me: if the amount of digits in the guid was to increase to 20 any BIGINT column would probably produce a similar effect as the INT type did? Should i stick with BIGINT or switch to using varchar?

Re: SQL column type issue

Posted: Thu Apr 12, 2012 11:48 am
by mikosiko
If the guid is intended to represent a number, and moreover be used for some math calculations then I will stick with that... Integers are not the only exact numeric data types that you can use... p.e http://dev.mysql.com/doc/refman/5.5/en/ ... anges.html

and also you must consider the storage difference between a numeric datatype and a string in your analysis (in case that the data volume is significant )

Re: SQL column type issue

Posted: Thu Apr 12, 2012 4:50 pm
by social_experiment
The consensus among most of the opinions i read seems to be that if the value stored will be used in any sort of mathematical operation it is best to store it as an integer; if no calculations or math operations will be done best is to store it as a string.

guid in this case will serve no mathematical function, it's merely a way to identify tweets