SQL column type issue

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

SQL column type issue

Post 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);								
			}			
		}				
	}	
?>
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: SQL column type issue

Post 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)?
Last edited by mikosiko on Thu Apr 12, 2012 5:48 am, edited 1 time in total.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: SQL column type issue

Post by Celauran »

Sounds like the numbers were bigger than INT can handle. You'd need to use BIGINT. See here.
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: SQL column type issue

Post 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?
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: SQL column type issue

Post 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 )
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: SQL column type issue

Post 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
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
Post Reply