使用3个表插入INTO

Question

 $sql3 = "INSERT INTO users_addresses (ua_user_id,ua_address_id) VALUES ('','')";

I am new in php and my hint is to link 2 tables id's in in another one called users_addresses.When a user is registered in my database i want the user_id and address_id to clone in users_addresses(ua_user_id,ua_address_id)

My tables

$sql = "INSERT INTO users (user_fname,user_mname,user_lname,user_login,user_email,user_phone) 
            VALUES ('{$_SESSION['userinfo']['fname']}', '{$_SESSION['userinfo']['mname']}', '{$_SESSION['userinfo']['lname']}', '{$_SESSION['userinfo']['login']}', '{$_SESSION['userinfo']['email']}', '{$_SESSION['userinfo']['phone']}')";
    $sql1 = "INSERT INTO addresses (address_line_1,address_line_2,address_zip,address_city,address_province,address_country) 
            VALUES ('{$_SESSION['addressinfo']['adr1']}', '{$_SESSION['addressinfo']['adr2']}', '{$_SESSION['addressinfo']['zip']}', '{$_SESSION['addressinfo']['city']}', '{$_SESSION['addressinfo']['provinciq']}', '{$_SESSION['addressinfo']['durjava']}')"; 
    $sql2 = "INSERT INTO notes (note_text) 
            VALUES ('{$_SESSION['noteinfo']['note']}')";

These are my others SQL codes for adding session's data in DB.

Answer 1

Use mysqli_insert_id() to get the unique ID of the insert table, this example uses Procedural style:

<?php
include 'connection.php';
......
$InsertSQL =    "INSERT INTO users (user_fname,user_mname,user_lname,user_login,user_email,user_phone) 
                VALUES ('{$_SESSION['userinfo']['fname']}',
                        '{$_SESSION['userinfo']['mname']}',
                        '{$_SESSION['userinfo']['lname']}',
                        '{$_SESSION['userinfo']['login']}',
                        '{$_SESSION['userinfo']['email']}',
                        '{$_SESSION['userinfo']['phone']}')";
$ResultSQL = mysqli_query($conn, $InsertSQL) or die(mysqli_error($conn)); // <-- execute your query
$UserID = mysqli_insert_id($conn); // <-- get the UserID

$InsertSQL =    "INSERT INTO addresses (address_line_1,address_line_2,address_zip,address_city,address_province,address_country) 
                VALUES ('{$_SESSION['addressinfo']['adr1']}',
                        '{$_SESSION['addressinfo']['adr2']}',
                        '{$_SESSION['addressinfo']['zip']}',
                        '{$_SESSION['addressinfo']['city']}',
                        '{$_SESSION['addressinfo']['provinciq']}',
                        '{$_SESSION['addressinfo']['durjava']}')"; 
$ResultSQL = mysqli_query($conn, $InsertSQL) or die(mysqli_error($conn)); // <-- execute your query
$AddressID = mysqli_insert_id($conn); // <-- get the AddressID

$InsertSQL =    "INSERT INTO user_addresses (ua_user_id,ua_address_id) 
                VALUES ($UserID,$AddressID)"; // <-- INSERT INTO user_address
$ResultSQL = mysqli_query($conn, $InsertSQL) or die(mysqli_error($conn)); // <-- execute your query
?>

You should also look into SQL Injection vulnerability, check out prepared statements.

Hope that helps.