PHP & Foreign Keys ?

[PHPFAN10]

Hello,

 

I posted here as although it's in regards to mysql the thing i want to ask is in regards to php itself.

 

I have two tables below as follows:

 

    CREATE TABLE `users` (
    `uid` int(11) NOT NULL AUTO_INCREMENT,
    `status` char(10) NOT NULL,
    `username` varchar(15) NOT NULL,
    `email` varchar(50) NOT NULL,
    `password` char(32) NOT NULL,
    `reg_date` int(11) NOT NULL,
    `ip` varchar(39) DEFAULT NULL,
    PRIMARY KEY (`uid`),
    UNIQUE KEY `username` (`username`,`email`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
     
     
    CREATE TABLE `users_profiles` (
    `uid` int(11) NOT NULL,
    `first_name` varchar(40) DEFAULT NULL,
    `last_name` varchar(50) DEFAULT NULL,
    `gender` char(6) DEFAULT NULL,
    `website` varchar(100) DEFAULT NULL,
    `msn` varchar(60) DEFAULT NULL,
    `aim` varchar(60) DEFAULT NULL,
    `yim` varchar(60) DEFAULT NULL,
    `twitter` varchar(15) DEFAULT NULL,
    UNIQUE KEY `uid` (`uid`),
    CONSTRAINT `users_profiles_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `users` (`uid`) ON DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

 

From the above you can see the uid in the users_profiles table is a foreign key and references the uid in the users table to link both tables together.

 

My scenario is this:

 

A user fills out a registration form that asks for username, email and a password. After validating the form data etc it is inserted into the users table.

 

Now at this stage they have no profile in the users_profiles table as they only just signed up. They login and xxx data is stored in a session along with there uid from users table. They now visit there profile, they fill in the profile form fields and submit the form.

 

Now can someone tell me if what i think i should be doing is correct, if not please tell me what i should do or a better way:

 

After validating the data that was submitted on profile form etc i could do something like a simple if else statement which will first check to see if the uid in session which is from users table matched a uid in the users_profiles table. If there's no match which in this case it won't be as they are a new user and don't have a profile record in users_profiles table then do an INSERT query into users_profiles table which would insert the submitted values from the form and the uid from session, on success that would now link the user from users table to there newly created profile in the users_profiles table. But if the uid in session matched a uid in user_profiles then obviously this means the user already has a profile ie.. submitted profile info previously so do an UPDATE query instead.

 

Am i rite in thinking this is how i would do it?

 

example below, note it's not valid code just to try an illustrate what i am saying; Hopefully!

 

<?php
    if($_SESSION['uid'] == user_profiles.uid)
    {
     /* If the uid in session which is the uid from users table
      * (we got it on successful login)
      * matches a users uid in user_profiles table then profile already exists
      * for user so do an UPDATE query here
      */
    } else {
     /* $_SESSION['uid'] does not match a uid in users_profiles table
      * so instead do a INSERT QUERY here. This means no profile exists for user.
      * The uid from session is inserted into users_profiles table (column uid)
      * which links the profile to user.
      */
    }
?>

 

I know if i delete a user it will also delete there profile if they have one like it should do to obviously not leave redundant data in the database and to ensure data intergity (sorry i think that's what it's called)

 

Thanks for any help.

 

PHPFAN

[DavidAM]

Just a couple of comments before I answer your question.

 

1) I see no reason not to make the uid in the user_profiles table the PRIMARY KEY for that table.

 

2) Be aware, that innoDB is the only engine that supports Foreign Keys. If you run those create statements at a host that does NOT support innoDB, the server will use whatever the default engine is (probably myIsam) which will NOT define and will NOT enforce the foreign key constraints -- and will NOT, repeat NOT, issue any errors or warnings about them not being created (I found this out the hard way).  So, if the engine is NOT innoDB, you will have to manually delete BOTH records as the foreign key CASCADE will not happen.

 

On to your question.

 

When a user visits their profile page, I would guess that you want to read their current values from the database and provide them as the default values on the form, so if they already have a profile, they do not have to enter everything every time they make a change. If you are doing this, it should be a simple matter to add a SESSION variable indicating they already have a profile. Then when they POST the form, you check this variable to determine if you should do an INSERT or UPDATE. That should save you an extra trip to the database.

 

Another option is the use the Insert ... On Duplicate Key Update command. Although, as a (personal) rule, I stay away from it. It is a viable choice, and probably not much worse (performance wise) from doing the extra select.

[PHPFAN10]

Thanks David for you reply. Is what i expected. To clear things up:

 

 

1) I see no reason not to make the uid in the user_profiles table the PRIMARY KEY for that table.

 

the uid in users_profiles is unique as one user can have only one profile, it has an index and is a primary key.

 

 

2) Be aware, that innoDB is the only engine that supports Foreign Keys. If you run those create statements at a host that does NOT support innoDB, the server will use whatever the default engine is (probably myIsam) which will NOT define and will NOT enforce the foreign key constraints -- and will NOT, repeat NOT, issue any errors or warnings about them not being created (I found this out the hard way).  So, if the engine is NOT innoDB, you will have to manually delete BOTH records as the foreign key CASCADE will not happen.

 

My webhost supports innoDB, i checked that first, infact it supports several including postgres, but i have never heard or used the others so not sure what the advantages and disadvantages are, plus i would expect that the syntax in php queries to be different which would mean learning another db syntax if syntax is different.

 

When a user visits their profile page, I would guess that you want to read their current values from the database and provide them as the default values on the form, so if they already have a profile, they do not have to enter everything every time they make a change. If you are doing this, it should be a simple matter to add a SESSION variable indicating they already have a profile. Then when they POST the form, you check this variable to determine if you should do an INSERT or UPDATE. That should save you an extra trip to the database.

 

When they login i would have to check to see if a profile exists in the users_profiles table for them and if so store all there profile details in session on login with there users.uid etc. Obviously i would have there profile details showing in form fields if they have one and are filled in, i do this now by prefilling the form details with details from sessions to save mysql queries etc and when they update profile i also get it to update session variables to keep session data and db data sync.

 

Another option is the use the Insert ... On Duplicate Key Update command. Although, as a (personal) rule, I stay away from it. It is a viable choice, and probably not much worse (performance wise) from doing the extra select.

 

Ok will take your advice on this one but i will stil take a read as i like to learn what i can when i can.

 

Thanks for all your help.

PHPFAN