Join results from multiple MySQL tables and output with PHP

Question

Using PHP, I'm trying to populate an HTML list with data from two different tables in a MySQL database. The structure for each table is as follows:

Table: "students"
+------------+------------+-----------+---------------+-------+
| student_id | first_name | last_name | city          | state |
+------------+------------+-----------+---------------+-------+
| 1          | Tobias     | Funke     | Newport Beach | CA    |
+------------+------------+-----------+---------------+-------+
| 2          | Bob        | Loblaw    | Laguna Beach  | CA    |
+------------+------------+-----------+---------------+-------+
| 3          | Ann        | Veal      | Bland         | CA    |
+------------+------------+-----------+---------------+-------+


Table: "students_current"
+------------+------------+---------------+
| student_id | school_id  | current_class |
+------------+------------+---------------+
| 1          | umass      | Sr            |
+------------+------------+---------------+
| 2          | ucla       | Jr            |
+------------+------------+---------------+
| 3          | ucla       | Fr            |
+------------+------------+---------------+

I'd like to populate the list with only with records that match a specific school_id.

For example, if I want the list only to contain students whose school_id is "ucla", the resulting HTML would be as follows:

<li>
    <span class="first_name">Bob</span>
    <span class="last_name">Loblaw</span>
    <span class="city">Laguna Beach</span>
    <span class="state">CA</span>
    <span class="current_class">Jr</span>
</li>

 <li>
    <span class="first_name">Ann</span>
    <span class="last_name">Veal</span>
    <span class="city">Bland</span>
    <span class="state">CA</span>
    <span class="current_class">Fr</span>
</li>

Each <li> item would be tied to a specific student_id value from the database. How do I write the PHP that will select/join the appropriate records from the database?

+1 For including Bob Loblaw in SO question example data

Michael Berkowski
– Michael Berkowski

2011-09-25 21:56:59 +00:00
Commented Sep 25, 2011 at 21:56 — Michael Berkowski
– Michael Berkowski, Commented Sep 25, 2011 at 21:56

Rusty Fausak · Accepted Answer · 2011-09-25 21:57:38Z

1

Using a LEFT JOIN:

SELECT *
FROM `students` s
    LEFT JOIN `students_current` sc ON s.`student_id` = sc.`student_id`
WHERE `school_id` = 'ucla'

answered Sep 25, 2011 at 21:57

Rusty Fausak

7,5541 gold badge30 silver badges39 bronze badges

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

1 Comment

40 Degree Day Over a year ago

Ah, I was very unfamiliar with the different types of joins and when to use them. I had been trying to use an INNER JOIN and wasn't even structuring that properly.

Collectives™ on Stack Overflow

Join results from multiple MySQL tables and output with PHP

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related