Can't Insert Data To Multiple Table With DBI::Mysql [SOLVED]

pakeidoprek has asked for the wisdom of the Perl Monks concerning the following question:

Get Parameter By Form & then insert into tbl

 if ( param('Submit') )
    {
    # get data from parameter to database
    my @params = param();
    my $full_name = param ('full_name');
    my $pob = param ('pob');
    my $dob = param ('dob');
    my $gender = param ('gender');
    my $address = param ('address');
    my $email = param ('email');
    my $phone = param ('phone');
    my $username = param ('username');
    my $password = param ('password');
    my $high_school = param ('high_school');
    my $school_dep = param ('school_dep');
    my $year_in = param ('years');

    # data to tbl:students
    # ----------------------------------------------------------------
    my $students = $dsn->prepare("INSERT INTO students
                       (username, password)
                        values
                       (?,?) 
                       ");
    $students->execute($username,$password)
          or die $DBI::errstr;
    $students->finish();
    
    # data to tbl:students_bio
    # ----------------------------------------------------------------
    my $students_bio = $dsn->prepare("INSERT INTO students_bio
                       (
                       username, full_name, pob, dob, gender, addr,
                       email, phone, high_school, school_dep, years
                       )
                        values
                       (?,?,?,?,?,?,?,?,?,?,?) 
                       ");
    $students_bio->execute($username,$full_name,$pob,$dob,
                           $gender,$address,$email,$phone,
                           $high_school,$school_dep,$year_in)
          or die $DBI::errstr;
          $students_bio->finish();
          
    # data to tbl:students_parent
    # ----------------------------------------------------------------
    my $students_parent = $dsn->prepare("INSERT INTO students_parent
                                        (username)
                                        values
                                        (?)
                                        ");
    $students_parent->execute($username)
          or die $DBI::errstr;
          $students_parent->finish();    

    };
[download]

I don't have problem to insert data to 1 tables with this method. but when i used this methods to insert data to multiple or more tables, the data just insert into 1st table and no activity with the 2nd $ 3rd table

I'm so sorry if my english so bad. Thank's for the attention, Cheers for u, i'm from indonesia.

Comment on Can't Insert Data To Multiple Table With DBI::Mysql [SOLVED] Download Code

Replies are listed 'Best First'.
Re: Can't Insert Data To Multiple Table With DBI::Mysql by graff (Chancellor) on Jun 26, 2016 at 17:58 UTC
Just to make sure I understand... The code you posted shows three insert statements, one for each table, and these all work okay, as expected, right? You said: "when I used this methods to insert data into multiple or more tables, the data just insert into the 1st table and no activity in the 2nd and 3rd table" but you did not show any code that tried to do that, so you have not shown the code that fails to do you want. Do I have that right? If so, then the problem is that in MySQL (and, I think, in any database that supports "Structured Query Language"), the "INSERT" statement only works on one table at a time. You cannot do anything like "INSERT into table1, table2, table3 ( table1.col_x, table2.col_y, table3_col_z ) values (?,?,?)".	[reply]
Re^2: Can't Insert Data To Multiple Table With DBI::Mysql by chacham (Prior) on Jun 27, 2016 at 12:49 UTC
(and, I think, in any database that supports "Structured Query Language"), the "INSERT" statement only works on one table at a time. Oracle supports INSERT ALL (INSERT ALL INTO A ... INTO B ... INTO C....) and INSERT FIRST. Perhaps not what you meant, but SQL Server indirectly allows it, via Partitioned Views or Views with INSTEAD OF Triggers. Those, in a sense, extend the original INSERT statement to include multiple tables. Same goes for DB2. Of course, a trigger can continue inserting into other tables, but that is mot definitely not the same statement.	[reply]
Re^3: Can't Insert Data To Multiple Table With DBI::Mysql by pakeidoprek (Novice) on Jul 16, 2016 at 14:40 UTC
Yup, MySql doesn't support insert multiple table in one query. so i fix it with if elsif function :) ^_^	[reply]
Re^2: Can't Insert Data To Multiple Table With DBI::Mysql by pakeidoprek (Novice) on Jul 16, 2016 at 14:35 UTC
i'm sorry before, cuz i'm late repy ur comment. i have fix it the problems :). yup i have query insert for 3 tables, and i'm just use conditional statement to manipulate the process one by one :) because we can't do like "INSERT into table1, table2, table3 ( table1.col_x, table2.col_y, table3_col_z ) values (?,?,?)". #cheer ^_^	[reply]
Re: Can't Insert Data To Multiple Table With DBI::Mysql by GrandFather (Saint) on Jun 26, 2016 at 21:13 UTC
Write a minimal self contained example script that shows the problem. We can't run your current code to see the issue and, although your English is fine, you haven't explained the problem well enough for us to give you much help. See I know what I mean. Why don't you?. Premature optimization is the root of all job security	[reply]
Re: Can't Insert Data To Multiple Table With DBI::Mysql by poj (Abbot) on Jun 26, 2016 at 14:17 UTC
`INSERT INTO students_bio ( username, full_name, pob, dob, gender, addr, email, phone, high_school, school_dep, years)` [download] Is field name `addr` correct or should it be `address` like the parameter name ? poj	[reply] [d/l] [select]
Re^2: Can't Insert Data To Multiple Table With DBI::Mysql by pakeidoprek (Novice) on Jun 26, 2016 at 14:25 UTC
addr is my field name in my tables, and i think it's doesn't matter if my variabel $address diefferent with parameter name :). the problem is, why only my first query for insert table run, then the 2nd and the 3rd query for the next table doesn't run :)	[reply]


Perl Monk, Perl Meditation
	PerlMonks