Insert into two diffrent tables a foregin key from third table in same form submit

Question

I have three tables: users, courses and grads.

users table (id, username, pass, course_id).
courses table (id, coursename, description).
grads table (user_id, course_id, grade).

I have a form to add a new user which include a dropdown list which shows the available courses with the value "id" from courses table.

When a course been added to the database the dropdown list will be updated.

Now, what I try to do is:

When I use the form to add new user and I choose the course from the dropdown list, I want the same value for the course being choosen sent to the grades table (course_id) and the (user_id) which will be added with the same submit action.

I use Wrapper classes php and PDO, just explain with normal PDO.

check($_POST, array(

    'email' => array(
        'required' => true,
        'min'      => 2,
        'max'      => 20,
        'unique'   => 'users'
    ),

    'password' => array(
        'required'  => true,
        'min'       => 6
    ),

    'password_again' => array(
        'required'  => true,
        'matches'   => 'password'
    ),

    'fullname' => array(
        'required'  => true,
        'min'       => 2,
        'max'       => 20
    ),

    'group_id' => array(

        'min'       => 1,
        'max'       => 20
    ),

    'class_id' => array(

        'min'       => 1,
        'max'       => 20
    ),

    'course_id' => array(

        'min'       => 1,
        'max'       => 20
    )

));



if($validation->passed()) {
            $user = new User();
            $salt = Hash::salt(32);

            try {

            $user->create(array(
                'email' => Input::get('email'),
                'password' => Hash::make(Input::get('password'), $salt),
                'fullname' => Input::get('fullname'),
                'address'  => Input::get('address'),
                'telnumber'=> Input::get('telnumber'),
                'salt'     => $salt,
                'group_id' => Input::get('group_id'),
                'class_id' => Input::get('class_id'),
                'course_id'=> Input::get('course_id'),

                ));


                    echo "En ny användaren Har skapat";
                #Session::flash('home', 'you are registerd successfully');
                #Redirect::to('index.php');
            } catch (Exception $e) {
                die ($e->getMessage());
            }

        #Session::flash('success', 'You registered successfully');
        #header('Location: index.php');
   } else {
      foreach($validation->errors() as $error) {
        echo $error,'
';

      }
    }
}  
}

?>




    
    
        
        Email:
        

    

   
        Password:
        
    

    
        Re Enter your password:
        
    

   
        Full Name:
        
    

    
        Address:
        
    

    
        Tel. Number:
        
        
    

   
        Permission:
        
 

    
        Class:
        

    

    
        Course:

I added this code after $user->create so its look like that

$user->create(array(
                'email' => Input::get('email'),
                'password' => Hash::make(Input::get('password'), $salt),
                'fullname' => Input::get('fullname'),
                'address'  => Input::get('address'),
                'telnumber'=> Input::get('telnumber'),
                'salt'     => $salt,
                'group_id' => Input::get('group_id'),
                'class_id' => Input::get('class_id'),
                'course_id'=> Input::get('course_id'),

                ));

                $grad = DB::getInstance()->insert('grads', array(
                        'user_id' => Input::get(LAST_INSERT_ID()),
                        'course_id' => Input::get('course_id'),
                    ));

but i got this error : Fatal error: Call to undefined function LAST_INSERT_ID() in C:\xampp\htdocs\Jensenonline1\register.php on line 77

Barmar · Accepted Answer

Use the LAST_INSERT_ID() function in MySQL to get the ID that was assigned to the user that was just created. Create the new user with a query like this:

$stmt1 = $pdo->prepare("INSERT INTO users (username, pass, course_id) 
                        VALUES (:username, :pass, :course_id)");
$stmt1->execute(array(':username' => $_POST['username'],
                      ':pass' => $_POST['password'],
                      ':course_id' => $_POST['course_id']));

Then to add to grades, use:

$stmt2 = $pdo->prepare("INSERT INTO grades (user_id, course_id)
                        VALUES (LAST_INSERT_ID(), :course_id)");
$stmt2->execute(array(':course_id' => $_POST['course_id']));

Since you use the same $_POST['course_id'] when executing both queries, the same value will be put in both tables.

Insert into two diffrent tables a foregin key from third table in same form submit

Answers (1)

Related Questions