How to add nested data into MySQL tables using PHP

Question

I have 3 tables and I'm working with wordpress too: wp7s_g_bookings, wp7s_g_tests and wp7s_g_clients. This is a study project, not to sell.

I want to be able to save 1 booking with X clients and Y tests.

I read about LAST_INSERT_ID() but I don't think it will work in this case and I'm lost.

Possible scenarios:

1. You are a partner, then the data is beeing pulled, so we have the ID because it exist already; In this case we are adding other users data inside repeatable fields like name="client_birth[]" ... // client_vat only show/exist if the partner don't exist.
2. You are a normal client, in this case you fill the same client data plus client_vat (so I'm sure when adding into database that only the guy that have client_vat is the booking_client)

if (!empty($_POST)) {
    $wpdb->query('START TRANSACTION');
    
    
    //All this fields are repeatable inputs, except client_vat that can only be the 1st record. I think it's missing a foreach
    $default = array(
        'client_name' => $_POST['client_name'],
        'client_birth' => $_POST['client_birth'],
        'client_sns' => $_POST['client_sns'],
        'client_vat' => $_POST['client_vat'],
        'client_city' => $_POST['client_city'],
        'client_email' => $_POST['client_email'],
        'client_phone' => $_POST['client_phone']
    );
    $item = shortcode_atts( $default, $_REQUEST );
    $addClients = $wpdb->insert( 'wp7s_g_clients', $item );
    
    
    $default = array(
        'test_client' => ???, //ID of previous client that have client_vat filled [0]
    );
    $item = shortcode_atts( $default, $_REQUEST );
    $addTests = $wpdb->insert( 'wp7s_g_tests', $item );

    
    $collectDate = date('Y-m-d H:i:s', strtotime($_POST['booking_collect_date']));
    $default = array(
        'booking_status' => 1,
        'booking_partner' => $_POST['booking_partner'], //ID of the partner, if not empty
        'booking_client' => ???, //If partner don't exist -> ID of client that have client_vat filled
        'booking_type' => $_POST['booking_type'],
        'booking_clients' => ???, //Array of all IDs previously added in wp7s_g_clients table
        'booking_city' => $_POST['booking_city'],
        'booking_address' => $_POST['booking_address'],
        'booking_collect_date' => $collectDate,
        'booking_payment' => $_POST['booking_payment'],
        'booking_amount' => $_POST['booking_amount'],
        'booking_obs' => nl2br($_POST['booking_obs']),
    );
    $item = shortcode_atts( $default, $_REQUEST );
    $addBookings = $wpdb->insert( 'wp7s_g_bookings', $item );
    
    
    if($addClients && $addTests && $addBookings) {
        $wpdb->query('COMMIT');
        $msg = "Success";
        wp_redirect( esc_url( get_page_link( 6 ) ) );
        exit;
    }
    else {
        $wpdb->query('ROLLBACK');
        $msg = "Error";
    }

}

My issues are adding this properly into database the repeatable fields and using a previous created ID(s). I tried to explain everything and comment so it's better to understand.

Answer 1

After you perform a query with $wpdb you can access the $wpdb->insert_id to get the last inserted id. You can find more details on the documentation page of wpdb

If you need more ids because u have more $wpdb->insert all you need to do is store $wpdb->insert_id after each insert, like:

...
$item = shortcode_atts( $default, $_REQUEST );
$addClients = $wpdb->insert( 'wp7s_g_clients', $item );
$clientId = $wpdb->insert_id;
....
$default = array(
        'test_client' => ???, //ID of previous client that have client_vat filled [0]
    );
$item = shortcode_atts( $default, $_REQUEST );
$addTests = $wpdb->insert( 'wp7s_g_tests', $item );
$testId = $wpdb->insert_id;
...
rest of your code