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 classes database the teacher name is determined by the teachers ID in the users database.

Here is my database structure.

Classes Database
-----------------------------
ID       CLASS              TEACHER
1        product design     3

User Database
-----------------------------
ID       NAME
3        John Doe

So when listing my classes I need it to convert "3" into "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 just putting another mysql query there will lower performance and is not advised, so how would I look up the user database for every row without adding a query into the while loop.

Thank you.

This sounds like you need to pull more information in with your initial query, unless it's hierarchical data; then I'd say make 2 queries at the beginning, toss them into arrays, and reference both in your loop. — user610217
– user610217, Commented Jun 6, 2012 at 15:56

Vincent Ramdhanie · Accepted Answer · 2012-06-06 15:55:02Z

5

You may use a join query to get all the info that you need at once. Then in your application you can sort through it and display accordingly. e.g.

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

answered Jun 6, 2012 at 15:55

Vincent Ramdhanie

103k23 gold badges143 silver badges195 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

joshkrz Over a year ago

Thanks that has worked, Is this possible to do with three or more tables tables?

Josh Toth Over a year ago

You can join as many tables as you want, as long as they relate to each other in some way, just add a new join after the previous. Make sure that you pull them in order, though - the join you're typing should join with information pulled in the initial query or a previous join.

joshkrz Over a year ago

Yes, I have got it working, took a while to get my head around thank you everyone for your help.

Isaac Hildebrandt · Accepted Answer · 2012-06-06 15:55:21Z

1

You want to use a JOIN in mysql.

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

answered Jun 6, 2012 at 15:55

Isaac Hildebrandt

1,0265 silver badges16 bronze badges

Comments

nageeb · Accepted Answer · 2012-06-06 15:56:15Z

0

You could use a JOIN in your initial query.

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 Class, plus the matched teacher name column from User, all in one query.

answered Jun 6, 2012 at 15:56

nageeb

2,0421 gold badge13 silver badges26 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

3 Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Linked

Related