Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • Entering Values in Second table with help of Primary Key of First Table

    • 0
    • 0
    • 0
    • 3
    • 0
    • 0
    • 0
    • 727
    Answer it

    Hello EveryOne.... Here is the scenario., In MySql database, I have two tables.. User and UserDetails... In 'User' table, i had generated userID made it as a primaryKey. For Example, 101->user1.... 102->User2 Now I need to Insert/update details of 'UserDetails' depending upon primary Key of 'User' table. for Example.. i need to update the values where UserID = 102. I'm sorry i am unable to explain scenario in most effective way.. But please any help would be appreciable. Please help me with such queries :)

 3 Answer(s)

  • Hi

    As you mentioned your issue , you may be trying to do after Log in User, if not you can only achieve insert/update userDetails by following the steps :-

    1. After User Login
    2. Fetch User Id from user table :- I am writing the query "SELECT `userId` FROM `users` WHERE `username` = '$username' AND `password` = '$password'" .
    3. Keep UserId in Session Variable :- put session_start() at the top of page then $_SESSION["userid"] = user Id fetched from step 2
    4. In the Page where you would like to insert/update Logged In User Details in userDetails table.
    5. Now you have userId in Session variable i.e $_SESSION["userid"] , if you want to insert User Details in UserDetail table then simple insert query including saving $_SESSION["userid"] into userId field in UserDetails Table which will be called as Foreign Key
    6. for update just need to execute update query where userid is $_SESSION["userid"]


    If this is your Concept , then please let me know, if you need help with detailed query

    Thanks

  • I'm not sure if you are asking for this scenery: Insert a new user, and with the auto-increment id generated. insert the details of that user, which depends on the first table auto-generated id.

    If this is the situation, in the context of the same mysql connection, you can ask for it with the LAST_INSERT_ID() function. This works because it asks for the last inserted id within this current connection ( it doesn't matter if someone is inserting in the same table, as it will be using other connection to mysql ).

    The process will be ( in pseudo code ):

    $ins = $db->query('insert into mytable (user_id, name) values (NULL, 'username');

    $newUserId = $db->query('select LAST_INSERT_ID()' );

    $insertDetails = $db->query("insert into user_details (details_id, user_id, details) values (NULL, $newUserId, 'my details')");

    Hope this helps you.

    More info: https://dev.mysql.com/doc/refman/5.5/en/getting-unique-id.html

  • Hi, you just need to create foreign key 'userId' in your second table UserDetail and write the below query to update UserDetail table as below:

    update UserDetail set column_name = value where userId = 102;

    Hope this will help you..if not, then please elaborate your question.

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: