查询并将查询数据插入另一个表

Question

<?php 
$userinfo = mysql_query ("SELECT user_id FROM `users`");
while ($row=mysql_fetch_array($userinfo)) {
   sanitize_data($row);
   $user_id=$row['user_id'];
   if ($row['usertype']=='Teacher') {
          mysql_query("INSERT INTO teacher(teacher_id)
      values('{$user_id}')");
   } else {
      mysql_query("INSERT INTO student(student_id)
      values('{$user_id}')");
   }
}
?>

Can you tell me whats wrong with my code? All I want is to add user_id into the Teacher table in teacher_id if the user type is teacher and add the user_id into the Student table in the student_id if the usertype is student. Please help me identify whats wrong thanks.

</div>

Answer 1

There's no need to do this with a PHP loop, you can do it entirely in SQL:

INSERT INTO teacher (teacher_id)
SELECT user_id
FROM users
WHERE usertype = 'Teacher';

INSERT INTO student (student_id)
SELECT user_id
FROM users
WHERE usertype != 'Teacher';