HELP! Sorting Names If They Are Foreign-Keys OR Number-IDs From Another Table?

[beer]

Hi, can you pls. help me with my relational database? I have 2 tables that are linked together:

Table1: students

+----+------------+---------------+

|id---- first_name---- last_name--+

|------+-----------+--------------+

|1------- jose---------- rizal

|2------- lito----------- lapid

|3------- tom---------- cruise

|4------- brad----------- pitt

|------+-----------+---------------+

Table2: classes - student_id - is the linking field to table students above

+---------+-----------+------+-------------+

|id----- student_id -----class

+---------+-----------+------+-------------+

|1--------3-------------- philosophy

|2------- 1-------------- home economics

|3------- 2--------------- math

|4------- 4----------------english

+----------+-----------+-------+-----------+

 

So I did this:

$class_set = "SELECT * FROM classes";

while( $class = mysql_fetch_array( $class_set ) ) {

echo $class["id"]. " " .$class["student_id"]. " " .$class["class"];

}

 

Here's the result:

+---------+-----------+------+-------------+

|id----- student_id -----class

+---------+-----------+------+-------------+

|1--------3-------------- philosophy

|2------- 1-------------- home economics

|3------- 2--------------- math

|4------- 4----------------english

+----------+-----------+-------+-----------+

Did you notice that NUMBERS are displayed as the Student's Names with student_id so you don't know them? I want to diplay their Full Name so:

$class_set = "SELECT * FROM classes";

while( $class = mysql_fetch_array( $class_set ) ) {

$student = find_student_by_id ( $class["student_id"] );

echo "Full Name: " .$student["last_name"]. ", " .$student["first_name"];

}

 

function find_student_by_id( $student_id ) {

$student_set = "SELECT * FROM students WHERE id = $student_id";

$student = mysql_fetch_array( $student_set );

return $student;

}

 

Finally, here's the output, no problems so far... (I didn't include html in the example codes to make them simple)

 

+------+---------------------+----------------------+

|--id-----Full Name---+-------Subjects-------+

+------+---------------------+-----------------------+

|--1--------cruise, tom-------------philosophy-------+

|--2--------rizal, jose-------------home economics--+

|--3--------lapid, lito---------------math--------------+

|--4--------pitt, brad-------------english------------+

+------+----------------------+------------------------+

 

BUT.... Did you notice that the Full Names, are not in alphabetical order?

 

Bottom line: How can I display the students LAST NAME in alphabetical order in the classes table If what's linking them from the Students Table are numbers or id's?

[Philip]

You can still sort it by ORDER BY last_name ASC

SELECT * FROM students WHERE id = $student_id ORDER BY last_name ASC

[beer]

SELECT * FROM students WHERE id = $student_id ORDER BY last_name ASC

 

Thank You! So I'll just have to put this in my classes table, right?

 

One of the greatest lessons I've learned by using a Content-Mangement-System. You should create a functions.php and these functions are essential:

 

1. A function that GETS ALL ROWS in table 1.

2. A function that FINDS 1 ROW in table 1. (do this by mysql_fetch_array)

 

3. A function that GETS ALL ROWS in table 2.

4. A function that FINDS 1 ROW in table 2. (do this by mysql_fetch_array)

 

Repeat as necessary. Then use While-Loops to retrieve data as necessary.