Inserting data into two tables on user sign up

[Josh Johnson]

So I have a MySQL table for users which holds core information such as username, passwords etc. As the website has 3 different types of users however, I also have a table for each user group (so Designers, Developers & Employers) which holds profile information such as bios/avatars etc.

What I would like to know is, how do you insert data into two tables on sign up and then link them so I can grab all the data for one user. I’m pretty sure I should be using a JOIN somewhere – I just don’t know when/how?

Thanks

[__]

the website has 3 different types of users however, I also have a table for each user group (so Designers, Developers & Employers) which holds profile information such as bios/avatars etc.

Are the three user types really so different that they need to be stored in different tables? This seems unlikely. Columns which are common to all users (I imagine that bios/avatars would be) should not be duplicated across different tables. In fact, if those fields are also unique per user, I would expect to find them in the same table as usernames and hashes.

Speaking of which,

I have a MySQL table for users which holds core information such as username, passwords etc.

I hope by “passwords,” you mean “hashes”…? Storing passwords in plain text is A Very Bad Thing.

What I would like to know is, how do you insert data into two tables on sign up

By using two SQL statements. When you have a situation like this, where multiple statements depend on each other’s success (i.e., if storing the user’s name fails, it makes no sense to try to store their bio), you should wrap them in a transaction for consistency.

and then link them so I can grab all the data for one user.

Your thought was correct; use joins when you retrieve the data. As @shaneisme said, you’d need to share your DB schema if you needed a more specific answer.

[Josh Johnson]

@traq, @shaneisme Thanks for your responses. Yes, i’m using bcrypt for hashing passwords – don’t worry!

Here is my database table structure:

Designers & Developers (two tables at the moment, but I’m seeing your point)

user_id  | jobtitle | age | experience | bio | speciality

Employers

user_id  | businessname | businesstype | businesswebsite | businessbio |

Users

id | firstname | lastname | email | email_code | email_code | time | password | confirmed | email_code | generated_string | email_code | ip | user_type |

I’m pretty sure user_id needs to be a foreign key in the user table right?

[chrisburton]

Why is email_code duplicated so many times?

[Josh Johnson]

@chrisburton Just noticed that – lazy typing, my bad.

[chrisburton]

Oh, I see. I was looking at it in a way to help you be more efficient.

[Josh Johnson]

@jurotek This is exactly what I needed! Thanks

[Author]

Posts