#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Posts
    1
    Rep Power
    0

    Add the ID from one table to another table


    Hi Guys

    Im trying to find a way to update an ID from one table to another. The tables i have are listed below:

    Tutors_table
    TutorID (PRIMARY KEY - AUTO INCREMENT)
    TutorName
    RoomNumber

    Modules_table
    ModuleID (PRIMARY KEY - AUTO INCREMENT)
    TutorID (FOREIGN KEY - REFERENCES Tutors.TutorID)
    Title
    ModCode
    Hours

    I want on INSERT for TutorID from Tutors to update TutorID in Modules, so as a Tutor can have many Modules assigned to them. I have tried many ways and for days to try and find a way. Then i thought i had it with the code i will post below, with this code i get no Errors....but when i look at the Data, only Tutors has any Values in it. Modules stays empty. Thankyou for any help with this problem.

    $sqlstatement ="INSERT INTO Tutors VALUES ( 'NULL', '$name', '$roomno')";
    $sql_result = mysql_query($sqlstatement, $connection)or die ("Could not insert into the DATABASE1");
    $sqlstatement ="SET @last_id_in_Tutors = LAST_INSERT_ID()";
    //ISSUE AND SQL INSERT INTO THE MODULES TABLE
    $sqlstatement ="INSERT INTO Modules VALUES ( last_id_in_Tutors(), '$title', '$modcode', '$hours')";
    $sql_result = mysql_query($sqlstatement, $connection)or die ("Could not insert into the DATABASE!!2");

    Rgds Willo
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,148
    Rep Power
    4274
    try replacing last_id_in_Tutors() with @last_id_in_Tutors
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo