PHP - MYSQL insert_id issue-CodePudding

i am getting data from a json file with curl and i want to add data id to my "tblcustomfieldsvalues" mysql table according to "tblclients" table. I am using mysqli_insert_id but i couldn't have success. When i add data its insert last id from tblclients to tblcustomfieldsvalues table. I believe i have to make an array to add them related table.

So, tblclients id(it's a primary key and autoincrement) value must be equal with $relid for each row. Here is the tblclients and tblcustomfieldsvalues codes for connect mysql from php.

$stmt = $mysqli->prepare("
    INSERT INTO tblclients(company,country,active,datecreated,default_currency,show_primary_contact,registration_confirmed,addedfrom)
    VALUES(?,?,?,?,?,?,?,?)
");


$stmt->bind_param("siisiiii",$title,$country,$active,$datecreated,$default_currency,$show_primary_contact,$registration_confirmed,$addedfrom);

$inserted_rows = 0;

foreach ($json['result'] as $product) {

    $title = $product['a'];
    $country = $product['b'];
    $active = $product['c'];
    $datecreated = $product['_date'];
    $default_currency = $product['d'];
    $show_primary_contact = $product['e'];
    $registration_confirmed = $product['f'];
    $addedfrom = $product['g'];

    $stmt->execute();
    $inserted_rows   ;
    
}

and

$stmt2 = $mysqli2->prepare("
    INSERT INTO tblcustomfieldsvalues(relid,fieldid,fieldto,value)
    VALUES(?,?,?,?)
");

$stmt2->bind_param("iiss", $relid,$fieldid,$fieldto,$value);
$inserted_rows = 0;


foreach ($json['result'] as $product) {

    $relid = mysqli_insert_id($mysqli);
    $fieldid = "1";
    $fieldto = "customers";
    $value = $product['z'];
    
    $stmt2->execute();
    $inserted_rows   ;
}

I am just new for coding, i made research and i couldn't figure that out. I might have other mistakes on my code. Please correct me if i am getting wrong with anything.

Thank you.

CodePudding user response：

Please put the 2nd query below the 1st query within the loop (so that you can get the last insert id from $stmt->insert_id; and then use it in the 2nd query )

Just one foreach loop is enough, so the code is:

<?php

$stmt = $mysqli->prepare("
    INSERT INTO tblclients(company,country,active,datecreated,default_currency,show_primary_contact,registration_confirmed,addedfrom)
    VALUES(?,?,?,?,?,?,?,?)
");


$stmt->bind_param("siisiiii",$title,$country,$active,$datecreated,$default_currency,$show_primary_contact,$registration_confirmed,$addedfrom);

$stmt2 = $mysqli2->prepare("
    INSERT INTO tblcustomfieldsvalues(relid,fieldid,fieldto,value)
    VALUES(?,?,?,?)
");

$stmt2->bind_param("iiss", $relid,$fieldid,$fieldto,$value);
$inserted_rows = 0;


//$inserted_rows = 0;

foreach ($json['result'] as $product) {

    $title = $product['a'];
    $country = $product['b'];
    $active = $product['c'];
    $datecreated = $product['_date'];
    $default_currency = $product['d'];
    $show_primary_contact = $product['e'];
    $registration_confirmed = $product['f'];
    $addedfrom = $product['g'];

    $stmt->execute();

/////////////////

//  $relid = mysqli_insert_id($mysqli);
    $relid =$stmt->insert_id;

    $fieldid = "1";
    $fieldto = "customers";
    $value = $product['z'];
    
    $stmt2->execute();

    $inserted_rows  ;
}