PHP - MYSQL insert_id issue

Question

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.

Can I suggest having the second loop inside the loop inserting the first set of rows. As you seem to understand the idea that the ID is the last row inserted, you could insert tho row to tblclients, get the ID (just once) and then insert the tblcustomfieldsvalues rows inside straight away. — Nigel Ren
– Nigel Ren, Commented Oct 16, 2022 at 13:59
Hello Nigel, thanks for reply. I did try that but i couldn't make possible what i thought. I might made wrong second loop and that is why it's not worked. Could you please help me that then i can create new loop in a loop? If you can give me example that will be awesome for me. — imp
– imp, Commented Oct 16, 2022 at 14:12

Ken Lee · Accepted Answer · 2022-10-16 14:48:30Z

1

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++;
}

edited Oct 16, 2022 at 14:48

answered Oct 16, 2022 at 14:40

Ken Lee

8,4053 gold badges14 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

imp Over a year ago

Do you know any way to control if same $value data exist before? if yes can we update this data? if not can we continue write data? I have tried but i had failure. If you know any way can you please share this code with me? thank you very much

imp Over a year ago

Also if $value data exist it s must be update tblclients too. i dont want to add extra data this table too.

Ken Lee Over a year ago

you may change your query to insert into.... on duplicate key update ..... something like this SO post . But if you encounter further problems, please make a new question so that we can further advise. Thanks

imp Over a year ago

Can you please help me with my other question? stackoverflow.com/questions/74091104/…

Collectives™ on Stack Overflow

PHP - MYSQL insert_id issue

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related