[SOLVED] Storing a variable formed from two database query arrays

[Goldeneye]

MySQL Server Version: 5.0.22

Returned Error: Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in

SELECT `noteid`, `time`, `userid`, `notifier`, `reason`, `getid1`, `getid2`, `getid3` FROM `notifications` WHERE `type`=0 AND `status`=0"

 

<?php
CREATE TABLE `notifications` (
  `noteid` mediumint(9) NOT NULL auto_increment,
  `type` int(2) NOT NULL,
  `time` int(11) NOT NULL,
  `userid` mediumint(9) NOT NULL,
  `notifier` varchar(100) NOT NULL,
  `reason` blob NOT NULL,
  `status` int(1) NOT NULL,
  `getid1` int(14) NOT NULL,
  `getid2` int(14) NOT NULL,
  `getid3` int(14) NOT NULL,
  PRIMARY KEY  (`noteid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=13 ;
?>

Here's my problem

I've got a $_GET['message'] variable which is composed of two database variables: `time` and `userid`. When I try inserting this combined variable into another table named `notifications` (used for reporting rule violating posts), it changes: ex) If I inserted the $_GET['message'] variable 12108923001 into the integer field `getid3`, the value gets changed to 2147483647

 

Am I using the wrong field type? Or is it even possible to retain the initial value?

 

Here's what I've tried

Copy and pasting field names from PHPMyAdmin to ensure there are no errors

Changing the mysql_fetch_array to mysql_fetch_assoc and mysql_fetch_row

[Xurion]

Can you split your gets into two different elements?

[fenway]

Am I using the wrong field type? Or is it even possible to retain the initial value?

You're inserting a value larger than is allowed for this column type, so it's being truncated... SHOW WARNINGS will tell you this.

[Goldeneye]

Well the time value should only 11 digits and the userid should 1 digit; atleast for now it should it be able to fit in one of the `getid#` fields but I'll try increasing it, though. But it doesn't seem like it's being truncated; it seems more like it's being changed completely. I'm also not quite sure how use SHOW WARNINGS in SELECT statements.

 

Yeah, I could split the $_GET variable because it's set up like this:

<?php
$time = $dbarray['time'];
$userid = $dbarray['userid'];
$message = "$time$userid";
if($_GET['message'] == $message){ /* foobar */ }
?>

[fenway]

That number after your INT column declaration is NOT related to the size of the data type, rather it's display width (which is meaningless).  INT can only hold numbers up to ~2 billion (~4 billion if unsigned), which in too small for "12108923001", hence it's being truncated to it's max. value (~2 billion -> 2147483647 ).

 

SHOW WARNINGS is a separate statement, not part of SELECT.  Issue it after your query:

 

$w_q = mysql_query( "SHOW WARNINGS" );
if( mysql_num_rows( $w_q) > 0 ) {
   while( $warning = mysql_fetch_assoc( $w_q ) ) {
      echo $warning['Level']."(".$warning['Code'].") - ".$warning['Message']."<BR>";
   }
}

[Goldeneye]

Ahhh of course, now I see, it has to be a BIGINT field to able to keep the entire value intact. Thanks fenway, for the explanation.