What is the correct way to retrieve and use user ID from one table to insert data into another table in PHP?

When inserting data into a table that requires a user ID from another table, you can first retrieve the user ID from the first table, then use that ID to insert data into the second table. This can be achieved by querying the first table to get the user ID based on certain criteria, and then using that retrieved user ID in the insert query for the second table. 

// Assume we have two tables: users and data
// We want to insert data into the data table with the user ID from the users table

// Connect to the database
$connection = new mysqli("localhost", "username", "password", "database");

// Query to get the user ID from the users table
$user_query = "SELECT id FROM users WHERE username = 'example_user'";
$user_result = $connection->query($user_query);
$user_row = $user_result->fetch_assoc();
$user_id = $user_row['id'];

// Insert data into the data table using the retrieved user ID
$data_query = "INSERT INTO data (user_id, data_column) VALUES ('$user_id', 'some_data')";
$connection->query($data_query);

// Close the database connection
$connection->close();

Keywords

PHP MySQL database query foreign key

Related Questions