Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 2 down vote favorite

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.

share|improve this question
 
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. –  Jeremy Holovacs Jun 6 '12 at 15:56

3 Answers

up vote 4 down vote accepted

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
share|improve this answer
 
Thanks that has worked, Is this possible to do with three or more tables tables? –  joshkrz Jun 6 '12 at 16:39
 
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. –  Josh Toth Jun 6 '12 at 16:53
 
Yes, I have got it working, took a while to get my head around thank you everyone for your help. –  joshkrz Jun 6 '12 at 17:16
up vote 1 down vote

You want to use a JOIN in mysql. 

SELECT * FROM class c JOIN user u ON u.ID = c.TEACHER
share|improve this answer
up vote 0 down vote

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.