Inserting into two tables - How to?

Collapse
This topic is closed.
X
X
 
  • Time
  • Show
Clear All
new posts
  • Will

    Inserting into two tables - How to?


    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

    #2
    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:


    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.

    Comment

    • Will

      #3
      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]


      Comment

      • Aggro

        #4
        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).

        Comment

        • Will

          #5
          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]


          Comment

          • Aggro

            #6
            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.

            Comment

            Working...