Insert in multiple tables with a single form

jasgames · Post by **jasgames** » Sat Oct 01, 2022 11:14 am

after joining tables, primary key field is not showing in var_dump($rsnew). Showing in Field List and how to add in view and $rsnew array. Any guidance please. Thanks

created 4 tables

create table main (id int PRIMARY key AUTO_INCREMENT, name1 varchar(30), mobile varchar(20));
create table child1 (id1 int AUTO_INCREMENT, id int not null default 0, amt1 int not null default 0, PRIMARY KEY (`id1`, `id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
create table child2 (id2 int AUTO_INCREMENT, id int not null default 0, amt2 int not null default 0, PRIMARY KEY (`id2`, `id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
create table child3 (id3 int AUTO_INCREMENT, id int not null default 0, amt3 int not null default 0, PRIMARY KEY (`id3`,`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

created view [main_child]
SELECT main.name1 AS name1, main.mobile AS mobile, child1.amt1 AS amt1,
  child2.amt2 AS amt2, child3.amt3 AS amt3, main.id AS mainid, child1.id AS
  mid1, child2.id AS mid2, child3.id AS mid3
FROM ((main JOIN
      child1 ON main.id = child1.id) JOIN
    child2 ON main.id = child2.id) JOIN
  child3 ON main.id = child3.id

main -> id
$vn = $rsnew['mainid'];
child1 -> id [master id linked with main, store via execute sql query]
child2 -> id [master id linked with main, store via execute sql query]
child3 -> id [master id linked with main, store via execute sql query]

3 Voucher Generated with 3 Unique Id linked with main table -> Id
$rsnew["mainid"] wants to insert in child tables.

Row_inserting

    $this->UpdateTable = "main";
        var_dump($rsnew);
        $amt1 = $rsnew['amt1'];
        $amt2 = $rsnew['amt2'];
        $amt3 = $rsnew['amt3'];
        $vn = $rsnew['mainid'];
        echo $vn;
        //unset($rsnew);
        $this->setSuccessMessage("Voucher tested.");
        die();
        Execute( "INSERT INTO main (name1, mobile) VALUES (".$rsnew['name1'].",".$rsnew['mobile'].")" );
        Execute( "INSERT INTO child1 (id, amt1) VALUES (".$vn.",".$amt1.");" );
        Execute( "INSERT INTO child2 (id, amt2) VALUES (".$vn.",".$amt2.");" );
        Execute( "INSERT INTO child2 (id, amt3) VALUES (".$vn.",".$amt3.");" );
        $this->setSuccessMessage("Voucher saved successfully");
        return FALSE;

showing error
Notice: Undefined index: mainid in C:\xampp\htdocs\prodcat\models\MainChild.php on line 1573

arbei · Post by **arbei** » Sat Oct 01, 2022 11:39 am

jasgames wrote:

Notice: Undefined index: mainid

That means there is no $rsnew['mainid'], the field is not in your form (because it is not updatable).

The main.id is an auto increment field, you do not know the value before insert, so you cannot do everything in Row_Inserting (which is fired before insert). You should move your Execute() to Row_Inserted server event (which is fired after insert) where you can get the insert id by, e.g.

$vn = $this->getConnection()->lastInsertId();

jasgames · Post by **jasgames** » Sat Oct 01, 2022 2:16 pm

Thanks for your kind response :)

i have checked, Row_Inserting Successful :-

       Execute( "INSERT INTO main_txn (name1, mobile) VALUES ('".$rsnew['name1']."','".$rsnew['mobile']."')" );
       $this->setSuccessMessage("Main Voucher saved.");
       return FALSE;

Checked main_txn table records inserted.

After return FALSE in Row_Inserting may be Row_Instered event not executing, how to execute this Row_Instered :-

        $amt1 = $rsnew['amt1'];
        $amt2 = $rsnew['amt2'];
        $amt3 = $rsnew['amt3'];
        //$vn = $rsnew['mainid'];
        $vn = $rsnew["id"];
        echo $vn;
        Execute( "INSERT INTO child1 (id, amt1) VALUES (".$vn.",".$amt1.");" );
        Execute( "INSERT INTO child2 (id, amt2) VALUES (".$vn.",".$amt2.");" );
        Execute( "INSERT INTO child2 (id, amt3) VALUES (".$vn.",".$amt3.");" );
        $this->setSuccessMessage("Voucher Details saved successfully");

mobhar · Post by **mobhar** » Sat Oct 01, 2022 4:47 pm

You cannot proceed to execute code in Row_Inserted if you have already return false in Row_Inserting server event.

So, for your case above, in order to execute code in Row_Inserted, make sure you did not return false in Row_Inserting server event.

Simply move this following code from Row_Inserting to Row_Inserted server event:

Execute( "INSERT INTO main_txn (name1, mobile) VALUES ('".$rsnew['name1']."','".$rsnew['mobile']."')" );
$this->setSuccessMessage("Main Voucher saved.");

and simply remove return FALSE; code from Row_Inserting server event.

jasgames · Post by **jasgames** » Sat Oct 01, 2022 5:20 pm

tried

in Row_Inserting remove all coding and set it default

if return true :- Error An internal error has occurred while processing your request.
if return false :- insert cancelled
if return :- insert cancelled
after remove return statament :- insert cancelled

in Row_Inserted :- (not triggered)

        $name1 = $rsnew['name1'];
        $mobile = $rsnew['mobile'];
        $amt1 = $rsnew['amt1'];
        $amt2 = $rsnew['amt2'];
        $amt3 = $rsnew['amt3'];

        $vn = $rsnew['id'];
        //$vn = $this->getConnection()->lastInsertId();
        Execute( "INSERT INTO main_txn (name1, mobile) VALUES ('".$rsnew['name1']."','".$rsnew['mobile']."')" );
        //$this->setSuccessMessage("Main Voucher saved successfully");
        //Execute( "INSERT INTO child1 (id, amt1) VALUES (".$vn.",".$amt1.");" );
        //Execute( "INSERT INTO child2 (id, amt2) VALUES (".$vn.",".$amt2.");" );
        //Execute( "INSERT INTO child2 (id, amt3) VALUES (".$vn.",".$amt3.");" );
        $this->setSuccessMessage("Voucher Details saved successfully");

mobhar · Post by **mobhar** » Sat Oct 01, 2022 6:17 pm

jasgames wrote:

if return true :- Error An internal error has occurred while processing your request.

Please enable debug and also check HTTP response. Post here the error message that you saw for more discussion.

jasgames · Post by **jasgames** » Sat Oct 01, 2022 8:46 pm

no error showing, debug active in settings nothing showing. Can anyone exact point where we are doing mistake? Thanks :)

[2022-10-01T17:40:31.374357+05:30] log.DEBUG: INSERT INTO `audittrail` (`datetime`, `script`, `user`, `action`, `table`, `field`, `keyvalue`, `oldvalue`, `newvalue`) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?) {"params":["2022-10-01 17:40:31","/phpmaker/prodcat/login","1","login","::1","","","",""],"types":[2,2,2,2,2,2,2,2,2],"executionMS":0.2532379627227783} []
[2022-10-01T12:10:31.709235+00:00] log.DEBUG: SELECT * FROM `users` WHERE `uid` = 1 {"params":[],"types":[],"executionMS":0.0019638538360595703} []
[2022-10-01T12:10:31.740995+00:00] log.DEBUG: SELECT COUNT(*) FROM `category` {"params":[],"types":[],"executionMS":0.000514984130859375} []
[2022-10-01T12:10:31.751569+00:00] log.DEBUG: SELECT * FROM `category` LIMIT 10 {"params":[],"types":[],"executionMS":0.00046706199645996094} []
[2022-10-01T12:10:54.273075+00:00] log.DEBUG: SELECT * FROM `users` WHERE `uid` = 1 {"params":[],"types":[],"executionMS":0.0025849342346191406} []
[2022-10-01T12:10:54.300537+00:00] log.DEBUG: SELECT COUNT(*) FROM `view1` {"params":[],"types":[],"executionMS":0.0007309913635253906} []
[2022-10-01T12:10:54.310533+00:00] log.DEBUG: SELECT * FROM `view1` LIMIT 10 {"params":[],"types":[],"executionMS":0.0006489753723144531} []
[2022-10-01T12:11:25.411382+00:00] log.DEBUG: SELECT * FROM `users` WHERE `uid` = 1 {"params":[],"types":[],"executionMS":0.0020949840545654297} []
[2022-10-01T12:12:30.725291+00:00] log.DEBUG: SELECT * FROM `users` WHERE `uid` = 1 {"params":[],"types":[],"executionMS":0.0023550987243652344} []
[2022-10-01T12:16:55.534749+00:00] log.DEBUG: SELECT * FROM `users` WHERE `uid` = 1 {"params":[],"types":[],"executionMS":0.003133058547973633} []
[2022-10-01T12:30:48.271635+00:00] log.DEBUG: SELECT * FROM `users` WHERE `uid` = 1 {"params":[],"types":[],"executionMS":0.0027599334716796875} []
[2022-10-01T12:34:47.917107+00:00] log.DEBUG: SELECT * FROM `users` WHERE `uid` = 1 {"params":[],"types":[],"executionMS":0.002485036849975586} []

Request URL: http://localhost/phpmaker/prodcat/view1add
Request Method: POST
Status Code: 200 OK
Remote Address: [::1]:80

Payload
csrf_name=csrf633832f844572&csrf_value=bc63fc74e5e5192e1b8f56175fa77712&t=view1&action=insert&modal=0&k_oldkey=&x_name1=test&x_mobile=12345&x_amt1=100&x_amt2=200&x_amt3=300

arbei · Post by **arbei** » Sat Oct 01, 2022 9:42 pm

I believe you have not enable debug correctly, you better enable all suggested settings, including "Treat PHP warnings and notices as errors".

I guess what happened was:

You returned true, so Row_Inserted is fired,
In your Row_Inserted you used $rsnew['id'] but the "id" field does not exist in your view (which only have main.name1 AS name1, main.mobile AS mobile, child1.amt1 AS amt1,
child2.amt2 AS amt2, child3.amt3 AS amt3, main.id AS mainid, child1.id AS mid1, child2.id AS mid2, child3.id AS mid3) at all, so you still have PHP notice about undefined index, (you should get the last insert id by suggested code instead)
You did not enable debug properly so it only shows internal error.

jasgames · Post by **jasgames** » Sat Oct 01, 2022 10:07 pm

Thanks for your response, will retry from zero level.

Really appreciable your help and guidance Dear Mobhar and Arbei. Hats Off :)