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?
Code mysql: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
query prepare code, ready to insert that data
and here's the code to put the retrieved results into an array as well as to input to the db.my $input_machine_data=$dbhconnect->prepare("insert into customer_machines
values (?,?,?,'','') ")
or die "prepare Failed on: $srv\n\t$DBI::errstr\n";
I have been working with and pondering this for 2 days now and can't understand what is wrong. looking for a better way.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