Inserting into two tables - How to?

[Will]

Hi everyone,

I have two tables one for 'users' information and one for their 'profiles'.

After a user fills his registration and profile information form, I would
like to insert those information into the previously mentioned tables and
keep the relation between the records.

Ex.

Table 1. 'users'
Col 1. ID INT Autoincrement
Col 2. UserName
Col 3. Password

Table 2. 'profiles'
Col 1. ID
Col 2. Age
Col 3. Gender

What is the most efficient way to accomplish this task?

Thanks in advance.

[Aggro]

Re: Inserting into two tables - How to?

Will wrote:
[color=blue]
> Table 1. 'users'
> Col 1. ID INT Autoincrement
> Col 2. UserName
> Col 3. Password
>
> Table 2. 'profiles'
> Col 1. ID
> Col 2. Age
> Col 3. Gender
>
> What is the most efficient way to accomplish this task?[/color]

LAST_INSERT_ID( )
See this page for more info and examples:

http://dev.mysql.com/doc/mysql/en/Getting_unique_ID.html

Few pointers:
- I recommend to write all table and column names with lower case,
because some operating systems are case sensitive and others are not.
And this can cause problems at some point when moving to another platform.
- You propably just gave a simplified example, but with that table
structure, I would recommend using only one table, where id, username,
password, age and gender are. This will make the queries faster and the
database will be more simple to understand and use.

Note that the above won't work if each user can have multiple profiles.
But if each user have only one profile, that is propably the smartest
thing to do.

[Will]

Re: Inserting into two tables - How to?

Thanks Aggro for your quick response.

I knew about LAST_INSERT_ID( ), but I was wondering if there is a better,
safer way or some type of syntax that I don't know about.

Thanks again.

"Aggro" <spammerdream@y ahoo.com> wrote in message
news:MrLpd.315$ xY2.255@read3.i net.fi...[color=blue]
> Will wrote:
>[color=green]
> > Table 1. 'users'
> > Col 1. ID INT Autoincrement
> > Col 2. UserName
> > Col 3. Password
> >
> > Table 2. 'profiles'
> > Col 1. ID
> > Col 2. Age
> > Col 3. Gender
> >
> > What is the most efficient way to accomplish this task?[/color]
>
> LAST_INSERT_ID( )
> See this page for more info and examples:
> http://dev.mysql.com/doc/mysql/en/Ge...unique_ID.html
>
> Few pointers:
> - I recommend to write all table and column names with lower case,
> because some operating systems are case sensitive and others are not.
> And this can cause problems at some point when moving to another platform.
> - You propably just gave a simplified example, but with that table
> structure, I would recommend using only one table, where id, username,
> password, age and gender are. This will make the queries faster and the
> database will be more simple to understand and use.
>
> Note that the above won't work if each user can have multiple profiles.
> But if each user have only one profile, that is propably the smartest
> thing to do.[/color]

[Aggro]

Re: Inserting into two tables - How to?

Will wrote:
[color=blue]
> I knew about LAST_INSERT_ID( ), but I was wondering if there is a better,
> safer way or some type of syntax that I don't know about.[/color]

Not really.

But what do you mean about safer?

FYI the return value from LAST_INSERT_ID( ) is not mixed between
different connections to MySQL server. So even if you run a webserver
with thousands of clients inserting rows simultaneously, you can still
safely use that, and you don't even have to lock tables for that (unless
your logic needs it for other reasons).

[Will]

Re: Inserting into two tables - How to?

I apologize for the late reply.

I meant by safer is I'm worrying that the second insert may fail and ends
with a raw in the 'users' table that doesn't correspond to a raw in the
'profiles' table.

Thanks a lot for the follow-up.


"Aggro" <spammerdream@y ahoo.com> wrote in message
news:UN0qd.182$ d74.149@read3.i net.fi...[color=blue]
> Will wrote:
>[color=green]
> > I knew about LAST_INSERT_ID( ), but I was wondering if there is a better,
> > safer way or some type of syntax that I don't know about.[/color]
>
> Not really.
>
> But what do you mean about safer?
>
> FYI the return value from LAST_INSERT_ID( ) is not mixed between
> different connections to MySQL server. So even if you run a webserver
> with thousands of clients inserting rows simultaneously, you can still
> safely use that, and you don't even have to lock tables for that (unless
> your logic needs it for other reasons).[/color]

[Aggro]

Re: Inserting into two tables - How to?

Will wrote:
[color=blue]
> I meant by safer is I'm worrying that the second insert may fail and ends
> with a raw in the 'users' table that doesn't correspond to a raw in the
> 'profiles' table.[/color]

Why would the second fail? If you validate the data before you try
inserting it into table, it is very unlikely that it will happen.

But of course, you should validate the data also when you load it from
the database. If you don't have profile for some user, you should
perhaps just delete it and give user an error message asking him to try
to create the account again. You need to do this anyway, because it is
always possible that for some reason all or some of the rows are deleted
from the profiles table.