With a foreign key, how to print out a column of a primary key that the foreign key is referencing to?

Question

I have two table, a class table and a professor table. Using mySQL,

describe class
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(11)      | NO   | PRI | NULL    | auto_increment |
| class       | varchar(225) | NO   |     | NULL    |                |
| description | varchar(225) | NO   |     | NULL    |                |
| prof_id     | int(11)      | NO   | MUL | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+

The prof_id is the foreign key, and the other table

describe professor
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int(11)      | NO   | PRI | NULL    | auto_increment |
| name     | varchar(255) | NO   |     | NULL    |                |
| office   | varchar(20)  | NO   |     | NULL    |                |
| phone    | varchar(50)  | NO   |     | NULL    |                |
| email    | varchar(50)  | NO   |     | NULL    |                |
| username | varchar(100) | NO   |     | NULL    |                |
| password | varchar(100) | NO   |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+

id is the primary key that class.prof_id is referencing to. I gotta web app, and I want to print out professor.name based on the given prof_id. (ie: if a professor named John Doe id = 1, and if 1 is in prof_id, I want John Doe to be printed. Here is my php code:

<?php
require_once('connect.php');
session_start();

$resultQuery = mysqli_query($mysqli, "SELECT * FROM professor");
?>

------SKIPPING OVER USELESS HTML/CSS-------

 <table>
    <tr>
        <u><th>Name</th> <th>Class</th> <th>Description</th> <th>Professor</th> <th>Update</th></u>
    </tr>
    <?php
    while($user_data = mysqli_fetch_array($resultQuery))
    {
        echo "<tr>";
        echo "<td>".$user_data['class']."</td>";
        echo "<td>".$user_data['description']."</td>";
        echo "<td>".$user_data['prof_id']."</td>";
        echo "<td><button><a href='editClasses.php?id=$user_data[id]'>Edit</a></button><button><a href='deleteClasses.php?id=$user_data[id]'>Delete</a></button></td></tr>";
    }
    ?>
</table>

It looks like you want to [`JOIN`](https://dev.mysql.com/doc/refman/8.0/en/join.html), but from your description I am not quite sure what exactly you would like to do. — Dharman, May 09 '19 at 20:26
Your code doesn't really match up with what you're saying. In fact, the code doesn't even match up with itself. You are selecting from `professor` but using columns from `class`. What is the starting point for what you are trying to achieve? Do you start with a professor's ID or a class' ID? — Patrick Q, May 09 '19 at 20:26
@Dharman I am trying to print professor.name based on what class.prof_id is pointing. — Kyle Welch, May 09 '19 at 20:28
@Dharman Yes but I want to print that in my php code in the ".$user_data['prof_id']." line is most likely. I can't get rid of my $resultQuery. — Kyle Welch, May 09 '19 at 20:36
@PatrickQ I need to keep my $resultQuery because I need it to print that class entries. However, I don't to print prof_id. I want to print professor.name based on what prof_id is (prof_id is the foreign key). So if prof_id is equal to 1, and John Doe has id = 1, I want it to print John Doe based on the code I have above. — Kyle Welch, May 09 '19 at 20:38
@KyleWelch But what you are currently SELECTing is _not_ the class list, it is the professor list. — Patrick Q, May 09 '19 at 21:29

score 0 · Answer 1 · answered May 09 '19 at 20:44

You can use JOIN to join 2 tables by the foreign key. If you want to keep the name of the ID column you can alias it e.g SELECT professor.name AS prof_id. Then your prof_id column will hold the actual name instead of the id.

It is always a good idea is to list your columns explicitly.

$sql = "SELECT class, description, professor.name AS prof_id 
    FROM class 
    JOIN professor ON professor.id=class.prof_id";
$resultQuery = mysqli_query($mysqli, $sql);

With a foreign key, how to print out a column of a primary key that the foreign key is referencing to?

1 Answers1