Add value of primary key in parent table into foreign key of child table with ajax?

I have 2 tables - viewdatamap (parent) and datamap (child). datamap has a foreign key called ‘view_data_id_fk’ which references the primary key ‘view_data_id’ in viewdatamap.

I’m having trouble adding a new row in viewdatamap and inserting that particular row’s view_data_id value into datamap’s ‘view_data_id_fk’.

On another php file, I used LAST_INSERT_ID() to propogate the value of ‘view_data_id’ into ‘view_data_id_fk’ of datamap, for insertion of the first row in viewdatamap.

 // check if there are any values in the table 'viewdatamap'. If not, 
// insert one row of data into both viewdatamap and datamap.
if (!$stmt->fetch()) {

$insertvdm = $db-> prepare("INSERT INTO viewdatamap (view_data_id, row_id,datamapname) 
    VALUES ('', ?,?)");
$insertvdm ->bindParam(1,$rowidvdm);
$insertvdm->bindParam(2,$sample);
$insertvdm ->execute();

$insertdm = $db-> prepare("INSERT INTO datamap (data_id, row_id,view_data_id_fk) VALUES ('', ?,LAST_INSERT_ID())");
$insertdm->bindParam(1,$rowiddm);
$insertdm ->execute();

}

Problem is within the 2 files below.

viewdatamap.php

// Add new row
$(document).on('click', '.btn_new_row', function(event) 
{
    event.preventDefault();
    //create a random id
    var row_id = Math.random().toString(36).substr(2);

    //get table rows
    var tbl_row = $(document).find('.tbl_code').find('tr');  
    var tbl = '';
    tbl +='<tr row_id="'+row_id+'">';
        tbl +='<td><input type="checkbox" class="sub_chk" row_id="'+row_id+'" ></td>';
        tbl +='<td></td>'; //For the # number
        tbl +='<td ><div class="new_row_data datamapname bg-warning" contenteditable="true" edit_type="click" col_name="datamapname"></div></td>';
        tbl +='<td ><div class="new_row_data description bg-warning" contenteditable="true" edit_type="click" col_name="description"></div></td>';
        tbl +='<td></td>'; //For the link.

        //--->edit options > start
        tbl +='<td>';            
            tbl +='  <a href="#" class="btn btn-link btn_new" row_id="'+row_id+'" > Add Entry</a>   | ';
            tbl +='  <a href="#" class="btn btn-link btn_remove_new_entry" row_id="'+row_id+'"> Remove</a> ';
        tbl +='</td>';
        //--->edit options > end    

    tbl +='</tr>';
    tbl_row.last().after(tbl);

    $(document).find('.tbl_code').find('tr').last().find('.datamapname').focus();
});

    $(document).on('click', '.btn_new', function(event) 
{
    event.preventDefault();

    var ele_this = $(this);
    var ele = ele_this.closest('tr');

    //remove all old alerts
    ele.find('.alert_msg').remove();

    //get row id
    var row_id = $(this).attr('row_id');

    //get field names
    var datamapname = ele.find('.datamapname');
    var description = ele.find('.description');

        var data_obj=
        {
            call_type:'new_row_entry_vdm',
            row_id:row_id,
            datamapname:datamapname.html(),
            description:description.html(),             
        };  

        ele_this.html('<p class="bg-warning">Please wait....adding your new row</p>');

        $.post(ajax_url, data_obj, function(data) 
        {
            var d1 = JSON.parse(data);

            var tbl = '';
            tbl +='<a href="#" class="btn btn-link btn_edit" row_id="'+row_id+'" > Edit</a>';
            tbl +='<a href="#" class="btn btn-link btn_save"  row_id="'+row_id+'" style="display:none;"> Save</a>';
            tbl +='<a href="#" class="btn btn-link btn_cancel" row_id="'+row_id+'" style="display:none;"> Cancel</a>';
            tbl +='<a href="#" class="btn btn-link text-warning btn_delete" row_id="'+row_id+'" style="display:none;" > Delete</a>';

            if(d1.status == "error")
            {
                var msg = ''
                + '<h3>There was an error while trying to add your entry</h3>'
                +'<pre class="bg-danger">'+JSON.stringify(data_obj, null, 2) +'</pre>'
                +'';

                $('.post_msg').html(msg);
            }
            else if(d1.status == "success")
            {
                ele_this.closest('td').html(tbl);
                ele.find('.new_row_data').removeClass('bg-warning');
                ele.find('.new_row_data').toggleClass('new_row_data row_data');
            }
        });
    // }
});

ajax.php

//--->new row entry for viewdatamap > start
if(isset($_POST['call_type']) && $_POST['call_type'] =="new_row_entry_vdm")
{   
$row_id     = app_db()->CleanDBData($_POST['row_id']);
$datamapname    = app_db()->CleanDBData($_POST['datamapname']);  
$description    = app_db()->CleanDBData($_POST['description']); 


$view_data_id_fk    = LAST_INSERT_ID;


$newrowvdm = app_db()->select("select * from viewdatamap where 
row_id='$row_id'");
if($newrowvdm < 1) 
{
    //add new row into viewdatamap
    $strTableName = "viewdatamap";

    $insert_arrays = array
    (
        'row_id' => $row_id,
        'datamapname' => $datamapname,
        'description' => $description

    );

    //Insert into viewdatamap.
    app_db()->Insert($strTableName, $insert_arrays);

    //add new row into datamap
    $strTableName2 = "datamap";

    $insert_arrays2 = array
    (

        'view_data_id_fk' => $view_data_id_fk,
        'row_id' => $row_id

    );


    //Insert into viewdatamap.
    app_db()->Insert($strTableName2, $insert_arrays2);



    echo json_encode(array(
        'status' => 'success',
        'msg' => 'added new entry'
    ));

    die();



}
}
//--->new row entry for viewdatamap > end

Now I get stuck on the adding row screen: stuckaddnewrow.png

The second row of the viewdatamap table will not be able to create another row in datamap either (with values for data_id, row_id and view_data_id_fk)

viewdatamap

viewdatamap.png

datamap

datamap.png

Where does the value of this constant come from, early in your PHP code? Is it defined in your include file for the database connection, or defined prior to the code you posted?

$view_data_id_fk    = LAST_INSERT_ID;

If you’re trying to use the last insert id from viewdatamap when you insert a new record into datamap, surely you’ll need to retrieve that after you run the first query? You’re obviously using some kind of database wrapper rather than pure PDO or mysqli so I don’t know what the function is. But somewhere after this line

$strTableName2 = "datamap";

I would expect to see a call to retrieve the last insert ID from the first query. You can’t do it in the place where you assign that constant, because the first query hasn’t run yet, so the value isn’t known at that point.

ETA - maybe it’s the “PHP Simple Database Class” that I found on a search for the CleanDBData() function ( https://codewithmark.com/php-simple-database-class ). If it is, you can just grab the return code from your first call to the Insert() function, as that’s the last insert ID:

//Insert into viewdatamap.
$view_data_id_fk =  app_db()->Insert($strTableName, $insert_arrays);

You probably want some code in there to make sure that the first insert actually worked and that you have a suitable value for the second insert before you try to run it.

1 Like

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.