Strange happenings with a query and insert.

[IBazz]

Hi,

I am querying my table for three cols. when I output them to my web page, all is well. however, when I insert them to the new table (1 to many), the second field ($serial) becomes populated in 600 out of 1218 records, with 2147483647. This value is not amongst those in the source table. I have no idea where it comes from and ask this question.

is there a mysql way to get three cols from one table and to input them to another table?

table structure

CREATE TABLE IF NOT EXISTS customer_machines
( customer_id int not null
, serial_number int not null
, date_of_purchase date not null
, last_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
, last_updated_by varchar (64) default NULL
, PRIMARY KEY (customer_id,serial_number,date_of_purchase)
, KEY second_ix (serial_number,customer_id)
, CONSTRAINT customer_fk 
    FOREIGN KEY (customer_id) 
      REFERENCES customer_details (customer_id) 
) ENGINE=INNODB DEFAULT CHARSET=LATIN1;

query to get from source table is here

select
                      c.customer_id
                    , c.serial_1
                    , c.date_of_purchase
                     from tbl_customers c

query prepare code, ready to insert that data

my $input_machine_data=$dbhconnect->prepare("insert into customer_machines
( customer_id
, serial_number
, date_of_purchase
, last_updated
, last_updated_by
)
values (?,?,?,'','') ")
or die "prepare Failed on: $srv\n\t$DBI::errstr\n";

and here's the code to put the retrieved results into an array as well as to input to the db.

while ( ($customer_id,$serial,$purchase) = $get_machine_data->fetchrow_array)
{

my ($year, $month_and_date) = ( $purchase =~ /(....)/g);
my ($month, $date) = ( $month_and_date =~ /(..)/g);
my $formatted_purchase_date = (join ( "-" ,($year, $month, $date)));

print qq( cus= $customer_id s= $serial f= $formatted_purchase_date <br /> ); # OUTPUTS TO SCREEN AS EXPECTED

$input_machine_data->execute($customer_id,$serial,$formatted_purchase_date) or die "update failed: $DBI::errstr\n"; #inputs wrong serial data

I have been working with and pondering this for 2 days now and can't understand what is wrong. looking for a better way.

bazz

[r937]

there you go posting a perl question in the mysql forum again

2147483647 (2*2*2*2*2*2*2*2*2*2*2*2*2*2*2*2*2*2*2*2*2*2*2*2*2*2*2*2*2*2*2*2 - 1) is the largest integer that fits into an INTEGER column

helps?

[IBazz]

I know it looked like a perl question but really it was a mysql one and I included perl to explain the whole thing. As it looks, the mysql column is the wrong data type because I didn't know that there was that limit on an integer column. (I had used 11 digit numbers before)

So thanks rudy, I can now look for the solution.

yay - bigint has done it.

bazz

[r937]

... and I included perl to explain the whole thing.

i have a question for you: do all mysql developers know perl?

hint: not even close

[IBazz]

I take your point OK and (in hoping I don't have that sort of [crossover] question again), I'll put it into the perl forum, if I do.

That said, you got it as soon as you saw, 2147483647 and I am not certain a perl person would have spotted it so quickly so I think it was an awkward one from the perspective of where to put it.

thanks again.

[r937]

anyone who has ever worked with integers would recognize that number

it's just that people working with databases get to see it so often in da manual

TINYINT ~ 127
SMALLINT ~ 32767
MEDIUMINT ~ 8388607 (this is a rare one that only mysql supports)
INTEGER ~ 2147483647
BIGINT ~ 9 quadrillion and summat

by the way, that number in parentheses that you often see with INT has nothing to do with the range of numbers that the column can hold

INT(1) and INT(11) and INT(937) all hold exactly the same range of numbers

[IBazz]

Thanks rudy, I read up on that after you gave me the tip earlier. (post#2)

The (11) (937) that you referred to last post, isn't that 'only' to do with zerofilling? I think I might have been confused earlier when I said I had used 11 digit INT's before. Actually they were double digit INTs, zerofilled.

bazz

[r937]

yes, only zerofilling