How could I avoid using a MySQL query in a While loop in PHP

Question

I have a while loop that outputs a list of classes. In the class database, the name of the teacher is determined by the identifier of the teachers in the user database.

Here is my database structure.

Classes Database ----------------------------- ID CLASS TEACHER 1 product design 3 User Database ----------------------------- ID NAME 3 John Doe 

Therefore, when listing my classes, I need to convert “3” to “John Doe”.

This is my current code:

 <?php $classdetails = mysql_query("SELECT * FROM class"); while($class = mysql_fetch_array($classdetails)) { $marklist_class = $class['class']; $marklist_teacher = $class['teacher']; //This is a userid //------Somewhere here i need to get the userid and look it up in the user database if($marklist_class=="") { } else { echo $marklist_class . ' ' . $marklist_teacher;} } } ?> 

I understand that simply adding another mysql query will decrease performance and is not recommended, since I could search for a user database for each row without adding the query to the while loop.

Thanks.

Answer 1

You can use the connection request to get all the information you need immediately. Then in your application you can sort it and display it accordingly. eg.

 SELECT Classes.class, Users.Name FROM Classes JOIN Users on Classes.Teacher = Users.ID 

Answer 2

You want to use JOIN in mysql.

 SELECT * FROM class c JOIN user u ON u.ID = c.TEACHER 

Answer 3

You can use JOIN in your initial request.

 Select c.id, c.class, c.teacher, u.name from class c join user u on u.id = c.teacher 

this will return all the columns from the class, as well as the column of the mapped teacher name from the user, all in one query.