Reputation: 359
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.
Upvotes: 0
Views: 171
Reputation: 11975
To store the name and age of your user you must have their USER_ID
. This is because you are using USER_ID
as the unique identifier for your users and it is a foreign key in the userprofile
table.
Typically when a user "logs in" - whatever that means for your website - you will determine their USER_ID
and use it as a session variable (eg. in $_SESSION
) so that you make use of it on subsequent pages (eg. the page where they edit their profile).
Regarding synchronisation: based on your definitions you do not need to add a record to userprofile
at the same time as user
. You can, but it would be effectively empty, as you do not know their name or age yet.
That said, you also need to check whether their userprofile
already exists. That is, are you updating their profile or adding to it. So it would be simpler to create their user profile record when you create their user record. That way, you know you are always updating.
You should add a UNIQUE
constraint to USER_ID
in the userprofile
schema. That way, you won't accidentally add duplicate records.
Upvotes: 1