Guriddo » Topic: Add row to different table after insert

Add row to different table after insert

Guriddo
Add row to different table after insert

This topic has 9 replies, 2 voices, and was last updated 6 months, 3 weeks ago by guriddo.support.

Creator

Topic

February 2, 2023 at 2:56 pm #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:

<?php
require_once '/var/www/llpc.tinicumconservancy.org/public_html/jqSuite/jq-config.php';
// include the jqGrid Class
require_once ABSPATH."php/PHPSuito/jqGrid.php";
// include the driver class
require_once ABSPATH."php/PHPSuito/DBdrivers/jqGridPdo.php";

// Connection to the server
$conn = new PDO(DB_DSN."tinicum",DB_USER,DB_PASSWORD);
// Tell the db that we use utf-8
$conn->query("SET NAMES utf8");
// Create the jqGrid instance
$grid3 = new jqGridRender($conn);
$grid3->debug=true;
$grid3->showError = true;
// Write the SQL Query
$grid3->SelectCommand = "
    select m.LpcMemberID,
           m.FirstName,
           m.LastName,
           m.Phone,
           m.eMail,
           t.LpcDescription as LPC,
           m.Notes,
           case
               when MemberInactive = 0 then 'Active'
               else 'Inactive'
           end MemberInactive
    from tblLpcMembers m,
         tblLpcType t
    where m.LPC = t.LpcID";

// Set output format to json
$grid3->dataType = 'json';
$grid3->table='tblLpcMembers';
$grid3->setPrimaryKeyID('LpcMemberID');
// Let the grid create the model
$grid3->setColModel();

// Set some grid options
$grid3->setGridOptions(array(
    "rowNum"=>40,
    "rowList"=>array(30,40,50),
    "sortname"=>"LastName,FirstName",
    // "sortorder"=>"asc",
    "hoverrows"=>true,
    "altRows"=>true,
    "height"=>"600",
    "autowidth"=>true,
    "toppager"=>true,
    "caption"=>"tblLpcMembers"
));
// Set the url from where we obtain the data
$grid3->setUrl('tblLpcMembers.php');

// Change some property of the field(s)
$grid3->setColProperty("LpcMemberID", array("editable"=>false,"hidden"=>true));
$grid3->setColProperty("FirstName", array("label"=>"First Name", "required"=>true));
$grid3->setColProperty("LastName", array("label"=>"Last Name", "required"=>true));
$grid3->setColProperty("Phone", array("required"=>true));
$grid3->setColProperty("eMail", array("required"=>true));
$grid3->setColProperty("LPC", array("label"=>"LPC Description","editrules"=>array("required"=>true)));
$grid3->setColProperty("Notes", array("label"=>"Notes","width"=>"200", "searchoptions"=>array("sopt"=>array('cn','bw','bn','nc')),"edittype"=>"textarea", "editoptions"=>array("rows"=>5, "cols"=>80)));
$grid3->setColProperty("MemberInactive", array("label"=>"Active Status","required"=>true));

$cid = jqGridUtils::GetParam('LpcMemberID');
$grid3->setAfterCrudAction("add","insert into tblLpcMemberCreds (LpcMemberID,username,password) values(?,'','')",array($cid));

// Enable filter toolbar searching
$grid3->toolbarfilter = true;
// Enable operation search
$grid3->setFilterOptions(array("searchOperators"=>true));

$grid3->setSelect("LPC", "select LpcID,LpcDescription from tblLpcType order by 2", false, true, false);
$grid3->setSelect("MemberInactive", array(0=>'Active',1=>'Inactive'), false, true, false);

$grid3->navigator = true;
$grid3->setNavOptions('navigator', array("excel"=>false,"add"=>true,"edit"=>true,"del"=>true,"view"=>true, "search"=>true, "cloneToTop"=>true));
$grid3->setNavOptions('edit',array("height"=>"auto","dataheight"=>"auto","width"=>700,"closeAfterEdit"=>true));
$grid3->setNavOptions('add',array("height"=>"auto","dataheight"=>"auto","width"=>"auto","closeAfterAdd"=>true));
$grid3->setNavOptions('view',array("top"=>30,"left"=>30,"height"=>"auto","dataheight"=>"auto","width"=>800,"labelswidth"=>"20%"));

//Trigger toolbar with custom button
$search = <<<SEARCH
jQuery("#searchtoolbar").click(function(){
    jQuery('#grid')[0].triggerToolbar();
    return false;
});
SEARCH;
$grid3->setJSCode($search);

$txtbx = <<<TB
function(formid) {
    jQuery('#v_Notes').css({'height':'80px','white-space':'break-spaces'});
    jQuery('#viewhdgrid3').html('LpcMemberID '+$('#v_LpcMemberID span').html());
}
TB;
$grid3->setNavEvent('view','beforeShowForm',$txtbx);
// Enjoy
$grid3->renderGrid('#grid3','#grid3-toppager',true, null, null, true, false);
?>

100

101

<?php

require_once '/var/www/llpc.tinicumconservancy.org/public_html/jqSuite/jq-config.php';

// include the jqGrid Class

require_once ABSPATH."php/PHPSuito/jqGrid.php";

// include the driver class

require_once ABSPATH."php/PHPSuito/DBdrivers/jqGridPdo.php";

// Connection to the server

$conn = new PDO(DB_DSN."tinicum",DB_USER,DB_PASSWORD);

// Tell the db that we use utf-8

$conn->query("SET NAMES utf8");

// Create the jqGrid instance

$grid3 = new jqGridRender($conn);

$grid3->debug=true;

$grid3->showError = true;

// Write the SQL Query

$grid3->SelectCommand = "

select m.LpcMemberID,

m.FirstName,

m.LastName,

m.Phone,

m.eMail,

t.LpcDescription as LPC,

m.Notes,

case

when MemberInactive = 0 then 'Active'

else 'Inactive'

end MemberInactive

from tblLpcMembers m,

tblLpcType t

where m.LPC = t.LpcID";

// Set output format to json

$grid3->dataType = 'json';

$grid3->table='tblLpcMembers';

$grid3->setPrimaryKeyID('LpcMemberID');

// Let the grid create the model

$grid3->setColModel();

// Set some grid options

$grid3->setGridOptions(array(

"rowNum"=>40,

"rowList"=>array(30,40,50),

"sortname"=>"LastName,FirstName",

// "sortorder"=>"asc",

"hoverrows"=>true,

"altRows"=>true,

"height"=>"600",

"autowidth"=>true,

"toppager"=>true,

"caption"=>"tblLpcMembers"

));

// Set the url from where we obtain the data

$grid3->setUrl('tblLpcMembers.php');

// Change some property of the field(s)

$grid3->setColProperty("LpcMemberID", array("editable"=>false,"hidden"=>true));

$grid3->setColProperty("FirstName", array("label"=>"First Name", "required"=>true));

$grid3->setColProperty("LastName", array("label"=>"Last Name", "required"=>true));

$grid3->setColProperty("Phone", array("required"=>true));

$grid3->setColProperty("eMail", array("required"=>true));

$grid3->setColProperty("LPC", array("label"=>"LPC Description","editrules"=>array("required"=>true)));

$grid3->setColProperty("Notes", array("label"=>"Notes","width"=>"200", "searchoptions"=>array("sopt"=>array('cn','bw','bn','nc')),"edittype"=>"textarea", "editoptions"=>array("rows"=>5, "cols"=>80)));

$grid3->setColProperty("MemberInactive", array("label"=>"Active Status","required"=>true));

$cid = jqGridUtils::GetParam('LpcMemberID');

$grid3->setAfterCrudAction("add","insert into tblLpcMemberCreds (LpcMemberID,username,password) values(?,'','')",array($cid));

// Enable filter toolbar searching

$grid3->toolbarfilter = true;

// Enable operation search

$grid3->setFilterOptions(array("searchOperators"=>true));

$grid3->setSelect("LPC", "select LpcID,LpcDescription from tblLpcType order by 2", false, true, false);

$grid3->setSelect("MemberInactive", array(0=>'Active',1=>'Inactive'), false, true, false);

$grid3->navigator = true;

$grid3->setNavOptions('navigator', array("excel"=>false,"add"=>true,"edit"=>true,"del"=>true,"view"=>true, "search"=>true, "cloneToTop"=>true));

$grid3->setNavOptions('edit',array("height"=>"auto","dataheight"=>"auto","width"=>700,"closeAfterEdit"=>true));

$grid3->setNavOptions('add',array("height"=>"auto","dataheight"=>"auto","width"=>"auto","closeAfterAdd"=>true));

$grid3->setNavOptions('view',array("top"=>30,"left"=>30,"height"=>"auto","dataheight"=>"auto","width"=>800,"labelswidth"=>"20%"));

//Trigger toolbar with custom button

$search = <<<SEARCH

jQuery("#searchtoolbar").click(function(){

jQuery('#grid')[0].triggerToolbar();

return false;

});

SEARCH;

$grid3->setJSCode($search);

$txtbx = <<<TB

function(formid) {

jQuery('#v_Notes').css({'height':'80px','white-space':'break-spaces'});

jQuery('#viewhdgrid3').html('LpcMemberID '+$('#v_LpcMemberID span').html());

}

TB;

$grid3->setNavEvent('view','beforeShowForm',$txtbx);

// Enjoy

$grid3->renderGrid('#grid3','#grid3-toppager',true, null, null, true, false);

Creator

Topic

Viewing 9 replies - 1 through 9 (of 9 total)

Author

Replies

February 3, 2023 at 3:04 am #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?

February 6, 2023 at 10:36 am #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

$grid->setTable('main_table');
// set the primary(uniquie) key
$grid->setPrimaryKeyId('OrderID');
if($grid->oper == 'add') {
    // obtain the data posted from the client
   $data = filter_input_array(INPUT_POST);
    // try to insert the data in the table
   if( $grid->insert($data) ) {
       $LastInsert = $grid->getLastInsertId();
        if($LastInsert) {
             $grid->setTable('child_table');
             $grid->setPrimaryKeyId('OtherKey');
             $grid->serialKey = false;
             $grid->insert(  array( "OtherKey"=>$LastInsert, .....) );
        }
   }
}
$grid->add = false;

$grid->setTable('main_table');

// set the primary(uniquie) key

$grid->setPrimaryKeyId('OrderID');

if($grid->oper == 'add') {

// obtain the data posted from the client

$data = filter_input_array(INPUT_POST);

// try to insert the data in the table

if( $grid->insert($data) ) {

$LastInsert = $grid->getLastInsertId();

if($LastInsert) {

$grid->setTable('child_table');

$grid->setPrimaryKeyId('OtherKey');

$grid->serialKey = false;

$grid->insert( array( "OtherKey"=>$LastInsert, .....) );

}

$grid->add = false;

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

February 8, 2023 at 10:48 pm #128099

kgrogers

Participant

Thank you Will – I will give that a try.

Ken

February 24, 2023 at 1:19 am #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:

// Set output format to json
$grid2->dataType = 'json';
$grid2->setTable = 'tblLandOwners';
$grid2->table = 'tblLandOwners';
$grid2->setPrimaryKeyID('LandOwnerID');
$grid2->serialKey = true;
$lastInsertID = "none set";
if ($grid2->oper == 'add') {
    $fh = fopen('/tmp/addloform.txt','w');
    $data = filter_input_array(INPUT_POST);
    fwrite($fh,"Data: ".print_r($data,true)."\n");
    if ($grid2->insert($data)) {
        $lastInsertID = $grid2->getLastInsertId();
        fwrite($fh,"Last insert LandOwnerID: ".$lastInsertID."\n");
        fclose($fh);
    }
}
$grid2->add = false;

// Let the grid create the model
$grid2->setColModel();

// Set output format to json

$grid2->dataType = 'json';

$grid2->setTable = 'tblLandOwners';

$grid2->table = 'tblLandOwners';

$grid2->setPrimaryKeyID('LandOwnerID');

$grid2->serialKey = true;

$lastInsertID = "none set";

if ($grid2->oper == 'add') {

$fh = fopen('/tmp/addloform.txt','w');

$data = filter_input_array(INPUT_POST);

fwrite($fh,"Data: ".print_r($data,true)."\n");

if ($grid2->insert($data)) {

$lastInsertID = $grid2->getLastInsertId();

fwrite($fh,"Last insert LandOwnerID: ".$lastInsertID."\n");

fclose($fh);

}

$grid2->add = false;

// Let the grid create the model

$grid2->setColModel();

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:

getLastInsertId( )

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

February 24, 2023 at 11:18 am #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

February 24, 2023 at 4:27 pm #128111

kgrogers

Participant

 show create table tblLandOwners\G
*************************** 1. row ***************************
       Table: tblLandOwners
Create Table: CREATE TABLE <code>tblLandOwners</code> (
LandOwnerID</code> int(4) NOT NULL AUTO_INCREMENT,
CurrentlyAssignedTo</code> int(4) NOT NULL DEFAULT 38,
LandOwner</code> varchar(220) DEFAULT NULL,
LandOwnerNotes</code> varchar(4044) DEFAULT NULL,
Status</code> tinyint(2) NOT NULL,
LandOwnerAddress1</code> varchar(220) DEFAULT NULL,
LandOwnerAddress2</code> varchar(220) DEFAULT NULL,
LandOwnerCity</code> varchar(220) DEFAULT NULL,
LandOwnerState</code> varchar(220) DEFAULT NULL,
LandOwnerZip</code> varchar(220) DEFAULT NULL,
HowToContact</code> varchar(255) DEFAULT NULL,
MailingSalutation</code> varchar(220) DEFAULT NULL,
AddressedTo</code> varchar(220) DEFAULT NULL,
  PRIMARY KEY (<code>LandOwnerID</code>),
  KEY <code>CurrentlyAssignedTo</code> (<code>CurrentlyAssignedTo</code>),
  KEY <code>Status</code> (<code>Status</code>),
  CONSTRAINT <code>tblLandOwners_ibfk_1</code> FOREIGN KEY (<code>Status</code>) REFERENCES <code>tblLandOwnerStatus</code> (<code>StatusID</code>) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=169 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci ROW_FORMAT=COMPACT
1 row in set (0.018 sec)

show create table tblLandOwners\G

*************************** 1. row ***************************

Table: tblLandOwners

Create Table: CREATE TABLE <code>tblLandOwners</code> (

LandOwnerID</code> int(4) NOT NULL AUTO_INCREMENT,

CurrentlyAssignedTo</code> int(4) NOT NULL DEFAULT 38,

LandOwner</code> varchar(220) DEFAULT NULL,

LandOwnerNotes</code> varchar(4044) DEFAULT NULL,

Status</code> tinyint(2) NOT NULL,

LandOwnerAddress1</code> varchar(220) DEFAULT NULL,

LandOwnerAddress2</code> varchar(220) DEFAULT NULL,

LandOwnerCity</code> varchar(220) DEFAULT NULL,

LandOwnerState</code> varchar(220) DEFAULT NULL,

LandOwnerZip</code> varchar(220) DEFAULT NULL,

HowToContact</code> varchar(255) DEFAULT NULL,

MailingSalutation</code> varchar(220) DEFAULT NULL,

AddressedTo</code> varchar(220) DEFAULT NULL,

PRIMARY KEY (<code>LandOwnerID</code>),

KEY <code>CurrentlyAssignedTo</code> (<code>CurrentlyAssignedTo</code>),

KEY <code>Status</code> (<code>Status</code>),

CONSTRAINT <code>tblLandOwners_ibfk_1</code> FOREIGN KEY (<code>Status</code>) REFERENCES <code>tblLandOwnerStatus</code> (<code>StatusID</code>) ON UPDATE CASCADE

) ENGINE=InnoDB AUTO_INCREMENT=169 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci ROW_FORMAT=COMPACT

1 row in set (0.018 sec)

And just for completeness – here’s tblParcels

show create table tblParcels\G
*************************** 1. row ***************************
       Table: tblParcels
Create Table: CREATE TABLE <code>tblParcels</code> (
ParcelID</code> int(4) NOT NULL AUTO_INCREMENT,
LandOwnerID</code> int(4) DEFAULT NULL,
WatershedID</code> int(4) NOT NULL DEFAULT 9,
DeededTo</code> varchar(220) DEFAULT NULL,
ParcelNum</code> varchar(220) NOT NULL DEFAULT '00-000-000',
Acres</code> decimal(8,4) DEFAULT 0.0000,
ContiguousParcels</code> int(1) NOT NULL DEFAULT 0,
GasLease</code> int(1) NOT NULL DEFAULT 0,
DisqualifyingUses</code> int(1) NOT NULL DEFAULT 0,
LandUse</code> int(2) NOT NULL DEFAULT 10,
ParcelRoadNum</code> varchar(220) DEFAULT NULL,
ParcelRoad</code> varchar(220) DEFAULT NULL,
ParcelCity</code> varchar(220) DEFAULT NULL,
ParcelState</code> varchar(220) DEFAULT NULL,
ParcelZip</code> varchar(220) DEFAULT NULL,
LPC</code> int(1) NOT NULL DEFAULT 6,
  PRIMARY KEY (<code>ParcelID</code>),
  KEY <code>LandOwnerID</code> (<code>LandOwnerID</code>),
  KEY <code>WatershedID</code> (<code>WatershedID</code>),
  KEY <code>LandUse</code> (<code>LandUse</code>),
  CONSTRAINT <code>tblParcels_ibfk_2</code> FOREIGN KEY (<code>WatershedID</code>) REFERENCES <code>tblWatersheds</code> (<code>WatershedID</code>) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT <code>tblParcels_ibfk_3</code> FOREIGN KEY (<code>LandUse</code>) REFERENCES <code>tblLandUses</code> (<code>LandUseID</code>) ON UPDATE CASCADE,
  CONSTRAINT <code>tblParcels_ibfk_4</code> FOREIGN KEY (<code>LandOwnerID</code>) REFERENCES <code>tblLandOwners</code> (<code>LandOwnerID</code>) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=238 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci ROW_FORMAT=COMPACT
1 row in set (0.000 sec)

show create table tblParcels\G

*************************** 1. row ***************************

Table: tblParcels

Create Table: CREATE TABLE <code>tblParcels</code> (

ParcelID</code> int(4) NOT NULL AUTO_INCREMENT,

LandOwnerID</code> int(4) DEFAULT NULL,

WatershedID</code> int(4) NOT NULL DEFAULT 9,

DeededTo</code> varchar(220) DEFAULT NULL,

ParcelNum</code> varchar(220) NOT NULL DEFAULT '00-000-000',

Acres</code> decimal(8,4) DEFAULT 0.0000,

ContiguousParcels</code> int(1) NOT NULL DEFAULT 0,

GasLease</code> int(1) NOT NULL DEFAULT 0,

DisqualifyingUses</code> int(1) NOT NULL DEFAULT 0,

LandUse</code> int(2) NOT NULL DEFAULT 10,

ParcelRoadNum</code> varchar(220) DEFAULT NULL,

ParcelRoad</code> varchar(220) DEFAULT NULL,

ParcelCity</code> varchar(220) DEFAULT NULL,

ParcelState</code> varchar(220) DEFAULT NULL,

ParcelZip</code> varchar(220) DEFAULT NULL,

LPC</code> int(1) NOT NULL DEFAULT 6,

PRIMARY KEY (<code>ParcelID</code>),

KEY <code>LandOwnerID</code> (<code>LandOwnerID</code>),

KEY <code>WatershedID</code> (<code>WatershedID</code>),

KEY <code>LandUse</code> (<code>LandUse</code>),

CONSTRAINT <code>tblParcels_ibfk_2</code> FOREIGN KEY (<code>WatershedID</code>) REFERENCES <code>tblWatersheds</code> (<code>WatershedID</code>) ON DELETE CASCADE ON UPDATE CASCADE,

CONSTRAINT <code>tblParcels_ibfk_3</code> FOREIGN KEY (<code>LandUse</code>) REFERENCES <code>tblLandUses</code> (<code>LandUseID</code>) ON UPDATE CASCADE,

CONSTRAINT <code>tblParcels_ibfk_4</code> FOREIGN KEY (<code>LandOwnerID</code>) REFERENCES <code>tblLandOwners</code> (<code>LandOwnerID</code>) ON DELETE SET NULL ON UPDATE CASCADE

) ENGINE=InnoDB AUTO_INCREMENT=238 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci ROW_FORMAT=COMPACT

1 row in set (0.000 sec)

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:

// 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);
    if ($grid2->insert($data)) {
        $sql = "select LAST_INSERT_ID()";
        $stmt = $conn->query($sql);
        $lastInsertID = $stmt->fetch();
        //$lastInsertID = $grid2->getLastInsertId();
        // Create a 'blank' record for this landowner in tblParcels
        $sql = "insert into tblParcels (LandOwnerID) values(".$lastInsertID[0].")";
        $conn->query($sql);
    }
}
$grid2->add = false;

// Let the grid create the model
$grid2->setColModel();

// 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);

if ($grid2->insert($data)) {

$sql = "select LAST_INSERT_ID()";

$stmt = $conn->query($sql);

$lastInsertID = $stmt->fetch();

//$lastInsertID = $grid2->getLastInsertId();

// Create a 'blank' record for this landowner in tblParcels

$sql = "insert into tblParcels (LandOwnerID) values(".$lastInsertID[0].")";

$conn->query($sql);

}

$grid2->add = false;

// Let the grid create the model

$grid2->setColModel();

That worked like a champ.

Ken

February 24, 2023 at 5:10 pm #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

        $sql = "select LAST_INSERT_ID()";
        $stmt = $conn->query($sql);
        $lastInsertID = $stmt->fetch();

$sql = "select LAST_INSERT_ID()";

$stmt = $conn->query($sql);

$lastInsertID = $stmt->fetch();

with

....
$lastInsertID = $conn->lastInsertId();
...

....

$lastInsertID = $conn->lastInsertId();

...

where $conn is the connection object.

and disable the transactions before do the insert

...
$grid2->trans= false;
...

...

$grid2->trans= false;

...

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

Regards,

Will

Guriddo Support Team

February 24, 2023 at 6:08 pm #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

February 28, 2023 at 11:19 am #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

...
$grid->getLastInsert = true;
...

...

$grid->getLastInsert = true;

...

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

Author

Replies

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!

Add row to different table after insert

Search Forum

Recent Replies

Forum Statistics

UserOnline