SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Strange happenings with a query and insert.

    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
    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

    Code mysql:
    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

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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?

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by IBazz View Post
    ... 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

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    yes, only zerofilling
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •