Insert into two tables, one having foreign key of the other

Question

I've looked at similar problems in SO and most of the solution were .NET centric and/or using stored procedures. So little help there. This is a snippet of the backend that I'm using to address an issue in my application.

$executestat=0;
//-------------------------
//PHASE-1
$sql = "INSERT INTO first_table (a_name,a_type,a_location) 
values('".$a_name."','".$a_type."','".$a_location."')";
if($result = $conn->query($sql)){
    $executestat=$executestat+1;
}
//-------------------------

//-------------------------
//PHASE-2
$sql = "SELECT f.a_id
from first_table as f
order by f.f_id desc limit 1";
if($result = $conn->query($sql)){
    $executestat=$executestat+1;
}
if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        $max_a_id = $row["a_id"];
    }
}else{$executestat=$executestat-1;}
//-------------------------

//-------------------------
//PHASE-3
$sql = "INSERT INTO second_table (a_id,b_title,b_location) 
values('".$max_a_id."','".$b_title."','".$b_location."')";
if($result = $conn->query($sql)){
    $executestat=$executestat+1;
}
//-------------------------

Problem Description:

• From a single form, I am trying to insert data into first_table and second_table
• second_table has the field a_id which is the foreign-key from first_table. (a_id is auto-increment primary-key on the first_table)

How I'm handling it right now:

I have been told that this type of problem is handled, generally, by querying the first_table, in the second query, to get the most recent id and then using it, while inserting data in the third query, to insert into the second_table.

Whats going wrong

I took this advice from somebody who's been dealing with databases for a long time. I don't know if it's something I'm doing because, occasionally, only the first query runs (or so I think)*, failing the other queries completely.

• *(or so I think) : because the success condition is if($executestat==3) . So I know that the first-query runs(because the Db gets updated with the first_table insertion) but I'm not sure if the second query is running or not. However, I do know that atleast one but not all queries are running. Is there a way to echo errors into a file in PHP. That'd be great for this
• Also, before anyone points out, I'll tackle the SQL injection problem with prepared statements later. I just need advice on this, now.

tl;dr

• Trying to insert two tables, one with foreign-key a_id of the other.
• Handled this by inserting first_table in the first query, finding out max a_id in the second query, using $max_a_id to insert into second_table in the third query.
• Sometimes this is unexpectedly failing. I know that atleast one but not all queries execute because first_table has new data inserted into it in the Db, but second_table, which should have a concurrent data pertaining to the first insertion, does not.
• How do I send echo of error to a file so that I can catch exactly where everything is failing?
• Is finding out $max_a_id a bad approach? Can anyone tell me a better way?

Can someone point out what I'm doing wrong? A small snippet (modification of this; even better is complete revamp)

Answer 1

After your insert, use $mysqli->insert_id; to get inserted value.

$sql = "INSERT INTO first_table (a_name,a_type,a_location) 
values('".$a_name."','".$a_type."','".$a_location."')";
if($result = $conn->query($sql)){
    $executestat=$executestat+1;
}
$max_a_id = $conn->insert_id;
//----