Hi
I have a php script with mysql, which users use to register, in the table the 1st column is a primary key which auto increments every time a new user registers, is it possible to get this to be entered into another table where it will be just normal data, (not a primary key).

    Yes, I'll explain in more detail if it helps. I did search the fourm and found stuff on inserting into multiple tables, but not if its an auto incrementing number.
    Basically..
    Users register, table 'users' gets updated, and the 1st column is 'user_id', (primary key, auto incrementing). What I want now, is for user_id to be copied from table 'users', to table 'dates' (which has its own primary key, auto increment called date_id).
    Does this explain better?
    Thanks for the quick reply
    Ben

      Hi again,

      Thanks for that, i think i understand now.

      Try using this.

      $last_id = mysql_insert_id();

      Then insert $last_id into your next table using the normal INSERT command in the place where you want it.

      Note - mysql_id(); must follow the SQL execution statement thet you want the ID from otherwise you may get the last autoincrement ID from the incorect table.

      Hope this Helps.

      Regards, Anand

        Excellent, many thanks for that, I'll give it a try and see how I get on.
        Just out of interest is there any chance that if i us mysql_insert_id() and 2 people are registering at the same time, that it could pull the wrong last_id? Or does it work by a client/session?

          Im not sure exactly how it works, but im guessing that because the insert_id() will follow the SQL execution then it "shouldn't" effect it. Only way to really know is if you try it.

          Regards, Anand

            I added this to my script

            function updatetable2($last_id) {
            $last_id = mysql_insert_id();
            $sql="INSERT INTO table2 (user_id) ".
            "VALUES ('$last_id')";
            }

            but it didn't seem to work. Can you see anything wrong with it?

            Am I putting the $last_id = mysql_insert_id(); in the right place? Or should it be where the ($last_id) is at the top? Or mebe completly separetly

              I just have seen that their are problems with using this command, What your gonna have to so is use the MySQL equivalent.

              Your code will change to this..

              function updatetable2()
              {
              $sql="INSERT INTO table2 (user_id) ".
              "VALUES (LAST_INSERT_ID())";
              }

              This should work (i havn't tried it)

              Anand

                Hmm it didn't work, it should, I can't see anything wrong!
                Argh, /me bangs head on keyboard!
                I looked on MySQL.com and they have something close to that! I can't see why its not working. Maybe I won't put it in its own function. Just stick it right after the 1st table is update!

                  mysql_insert_id() gets the value from the previous sql insert. Right after the insert, assign it to the variable and then pass it to the function.

                    Only way to really know is if you try it

                    In one sense that's true, but I would also advise consulting the manual, which does in fact promise that mysql_insert_id() works on a per-connection basis. (It wouldn't be all that useful if it didn't.)

                      Do you know what function would up date the 2nd table? I have tried everything I can think of! Inserted it in its own function, added it to another function (which worked, but cocked up the function it was added to).
                      So far I have

                      function updatetable2() {
                      $sql="INSERT INTO table2 (user_id) ".
                      "VALUES (LAST_INSERT_ID())";
                      }

                      The above doesn't work, is there anything I need to add?

                      I even tried adding..

                      $result=db_query($sql);
                      if (!$result) {
                      $feedback .= ' ERROR - '.db_error();
                      return false;
                      }

                      Because this is whats after another function that updates a table (which works).

                      Soryy about this, I only statred learning PHP last week.

                        what database are you using? MySQL?

                          Yes, I have a register script, which users register with, and the new record gets a user_id, which i then want inserted into a 2nd table. Only it doesn't want to work. Its very much the same thing Griffith wants to do in his post! Hopefully someone can help us both.

                            Okay, here is what I use and it works like a charm.

                            Maybe there is a better way, but this gets the job done.

                            $sql = "INSERT INTO pr_tbl1 (acctno, headline, summary) VALUES ($acctno, '$headline', '$summary')";

                            $result = @($sql, $connection);

                            $prid = mysql_insert_id();

                            $sql = "INSERT INTO pr_tbl2(prid, body) VALUES ($prid, '$body')";

                            $result = @($sql, $connection);

                              Excellent, thanks for that, works perfectly.

                                5 months later

                                Thank you thank you thank you! You've saved me SO much time. This works perfectly

                                  Write a Reply...