Add row to different table after insert

  • Guriddo
  • Add row to different table after insert

Home Forums Guriddo Suito PHP Add row to different table after insert

  • Creator
    Topic
  • #128095
    kgrogers
    Participant

    I have two tables that are related to each other. and after I add a row to table 1 I need to add a row to table 2.

    Table1 (tblLpcMembers) primary key is LpcMemberID (auto-increment) and I need to get the index number from the insert to use that as the index into table2 (tblMemberCreds).

    When I add the row in table1 nothing changes in the grid – I don’t get any errors, but the debug log does show the inserts for table1 and table2.

    I am using the setAfterCrudAction method, and I know that if the original insert fails it won’t fire. But since I don’t see any errors I don’t know what’s going on.

    Here is my code:

     

Viewing 9 replies - 1 through 9 (of 9 total)
  • Author
    Replies
  • #128096
    kgrogers
    Participant

    I did a lot of further digging and testing, and I think the issue is that the insert into the first table isn’t sending the LpcMemberID in the post message since that field is an auto-increment field, and my understanding of the function gets its data from either the $_GET or $_POST data. Looking at the debug log I never see any value set for $cid other than _empty, so the setAfterCrudAction sql fails with an error (I had to enable error logs in my DB engine).

    So the question is – is it even possible to get the last primary key value (LpcMemberID) in the script?

    #128098
    guriddo.support
    Participant

    Hello,

    Yes, there is a way to get the last inserted id, but only when the insert is done.

    At the moment to do what you want some trick should be applied.

     

    One possible way is to disable automatic add and at certain point of the code and perform some other operations

    Note setTable before if and $grid->add = false after the if.

     

    You can do a lot variations using this approach.

     

    In the upcoming release we will add a special word in which the last inserted id can be used in setAfterCrud (thank you for point us to add this feature)

     

    Kind Regards

    Will

    Guriddo Support Team

    #128099
    kgrogers
    Participant

    Thank you Will – I will give that a try.

     

    Ken

    #128109
    kgrogers
    Participant

    I added some test code based on what you said, but I never get any value with the $grid->getLastInsertId method.

     

    Here’s what I added:

    After I perform the insert, the text file I created (addloform.txt) contains:

    Data: Array
    (
    [LandOwnerID] =>
    [LandOwner] => Rogers, K&T
    [LandOwnerNotes] => jj
    [Status] => 1
    [CurrentlyAssignedTo] => 26
    [LandOwnerAddress1] => 86 Gruiver Rd
    [LandOwnerAddress2] =>
    [LandOwnerCity] => Branchburg
    [LandOwnerState] => NJ
    [LandOwnerZip] => 08876
    [HowToContact] => kjh
    [MailingSalutation] => lkjh
    [AddressedTo] => lkjh
    [oper] => add
    )

    Last insert LandOwnerID:

    It looks like the $grid->getLastInsertId method is returning something (since the value got reset from “none set”), but it’s not the primary key from the insert.

     

    The Help file for this method reads:

    The method return the last inserted id when a add is performed and the primary key is serial. In order to have effect this method should be called after renderGrid, editGrid or insert methods – see below

    API Tags:
    Access: public

    Is it possible I have it in the wrong place?

    Ken

    #128110
    guriddo.support
    Participant

    Hello Ken

     

    Would be possible to send us the table definition of  tblLandOwners?

    I mean the create table script.

     

    Thank you.

     

    Kind Regards

    Will

    Guriddo Support Team

    #128111
    kgrogers
    Participant

     

    And just for completeness – here’s tblParcels

    I did manage to find a workaround that actually worked. Rather than using the $grid->getLastInsertId() method, I used the MySQL LAST_INSERT_ID() function:

     

    That worked like a champ.

     

    Ken

    #128112
    guriddo.support
    Participant

    Hello Ken,

     

    Thank you for the update.

     

    Actually we use the PDO lastInserId

     

    I’m very curious if this script will work:

    Replace

    with

    where $conn is the connection object.

    and disable the transactions before do the insert

    I think that maybe the problem was in the transaction set to true.

     

    Regards,

    Will

     

    Guriddo Support Team

    #128113
    kgrogers
    Participant

    (For some reason the Code icon is missing from this compose window – sorry in advance for the ugly code block).

     

    I made the changes you suggested:
    <p style=”padding-left: 40px;”>// Set output format to json
    $grid2->dataType = ‘json’;
    $grid2->setTable = ‘tblLandOwners’;
    $grid2->table = ‘tblLandOwners’;
    $grid2->setPrimaryKeyID(‘LandOwnerID’);
    $grid2->serialKey = true;
    if ($grid2->oper == ‘add’) {
        $data = filter_input_array(INPUT_POST);
        $grid2->trans = false;
        if ($grid2->insert($data)) {
            // $sql = “select LAST_INSERT_ID()”;
            // $stmt = $conn->query($sql);
            // $lastInsertID = $stmt->fetch();
            $lastInsertID = $conn->lastInsertId();
            // Create a ‘blank’ record for this landowner in tblParcels
            $sql = “insert into tblParcels (LandOwnerID) values(“.$lastInsertID[0].”)”;
            $conn->query($sql);
        }
    }
    $grid2->add = false;</p>
    <p style=”padding-left: 40px;”>// Let the grid create the model
    $grid2->setColModel();</p>
    The insert for tblLandOwners worked, but no record was created in tblParcels.

    Ken

    #128114
    guriddo.support
    Participant

    Hello Ken,

     

    Just do some test with your table.

    I apologize really for that, that I  miss some important setting in the script.

     

    Our original script work like champ if you add only one important setting

     

     

    This option enables getting the lastInserId from the table.

    By default it is fale.

     

    We have tested with this setting and it work.

     

     

    Sorry again for my mistake on this setting.

     

    Kind Regards,

    Will

     

    Guriddo Support Team

Viewing 9 replies - 1 through 9 (of 9 total)
  • You must be logged in to reply to this topic.

Stay connected with us in your favorite flavor!