I have two tables user
and userprofile
in MySQL
and I have a php script on my web server that handles the insertion of data into these tables.
Here are the SQL commands used to define the tables.
CREATE TABLE user (
USER_ID INT NOT NULL AUTO_INCREMENT,
USERNAME VARCHAR(20),
USER_PASSWORD VARCHAR(255),
PRIMARY KEY (USER_ID)
);
CREATE TABLE userprofile (
USER_ID INT NOT NULL,
FULL_NAME VARCHAR(255),
AGE INT,
FOREIGN KEY (USER_ID) REFERENCES user (USER_ID)
);
I am able to successfully write to the database table using PHP
for the user
table with the following SQL
command "INSERT INTO user (USERNAME, USER_PASSWORD) VALUES ('$username', '$hashedPassword')"
which is executed inside a PHP
function that I have defined.
The problem I am experiencing now is let's say that I need to add information such as the FULL_NAME
, and AGE
of the user. Since I am allowing the user to include this information later on (they are optional because of user privacy). How exactly would I populate the database table userprofile
without having the USER_ID
?
I have looked over the documentation for creating database tables and I believe that my definitions are correct. My initial thought was to implement a function to get the USER_ID
of the current user from the database, and then use that to insert their data into the userprofile
table. The expected behavior I wanted was after adding the user with the SQL
command above, the userprofile
would also have a row created since it is linked by a foreign key USER_ID
. I am not really sure how to guarantee that these two tables are synchronized with each other.