Querying and Inserting Queried Data into another Table

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.

@NoLiver92 Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\GradingExpress\admin\updateuser.php on line 4 this is the error message — Albert Saludaga, Jan 12 '15 at 11:50
your `$userinfo = mysql_query ("SELECT user_id FROM `users`");` query has failed. — vaso123, Jan 12 '15 at 11:52
Did you actually do any research on this error? 1st result on google tells you exactly: http://stackoverflow.com/questions/2973202/mysql-fetch-array-expects-parameter-1-to-be-resource-or-mysqli-result-boole — NoLiver92, Jan 12 '15 at 11:53
just found the answer thanks forgot to select database. @NoLiver92 Im sorry just a beginner. — Albert Saludaga, Jan 12 '15 at 12:37
For futture, do a google search first, most of the time thse answers are already on SO — NoLiver92, Jan 12 '15 at 12:39
@NoLiver92 can i ask you something? or should i open up another Question? — Albert Saludaga, Jan 12 '15 at 12:41
My reputation is Low Cant Enter PHP room. @NoLiver92 can you chat me? — Albert Saludaga, Jan 12 '15 at 12:44
@NoLiver92 i have an error whenever i go to this php code. it says "Duplicate entry '1' for key 'PRIMARY'" meaning i need to put an argument that would only allow the adding of data if the teacher_id does not exist.Can you help me? — Albert Saludaga, Jan 12 '15 at 12:49
INSERT IGNORE is what you want. If you use the IGNORE keyword, errors that occur while executing the INSERT statement are ignored. For example, without IGNORE, a row that duplicates an existing UNIQUE index or PRIMARY KEY value in the table causes a duplicate-key error and the statement is aborted. With IGNORE, the row still is not inserted, but no error occurs. Ignored errors may generate warnings instead, although duplicate-key errors do not. Also a simple ggogle search would have provided the answer — NoLiver92, Jan 12 '15 at 12:54
@NoLiver92 so i dont need to make any function or php code? Whats the format of ignore is it like this? mysql_query("INSERT IGNORE INTO student(student_id).. ? — Albert Saludaga, Jan 12 '15 at 12:57

score 1 · Answer 1 · answered Jan 12 '15 at 11:56

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';

answered Jan 12 '15 at 11:56

Barmar

741,623
53
500
612

Querying and Inserting Queried Data into another Table

1 Answers1