Posted by Leif Gregory on 04/19/05 22:10

Hello php-general,

I've been wrestling with this one for a bit and wanted to see if
anyone had a nice magic bullet (yeah right) to do this.

Let's just keep it simple (my DB is a bit more complex).

We have a DB called Courses with three tables.

Instructors -
id
name

Locations
id
name

Course -
id
name
instructorID - From Instructors table
locationID - From Locations table


So let's put in some data

Instructors -

id name
1 Bill
2 Dave
3 Jessica


Locations -

id name
1 Middle School
2 High School
3 Elementary School


Course -

id name instructorID locationID
1 Basket Weaving 2 2
2 Math 2 1
3 Science 1 3
4 Biology 3 1


Just in case, I'm actually dealing with three more tables, so I don't
think doing weird joins will work, but I'm trying to keep this simple.
Further, the instructors table actually has six fields, the Locations
table has four fields. Those extra fields are descriptive pieces for
each, i.e. phone numbers, e-mail address, office number, address etc
for each instructor.

Now, I want to display all the courses and have the names instead of
id numbers show up, so I'd select from Courses and output that.

course instructor location
Basket Weaving Dave High School
Math Dave Middle School
Science Bill Elementary School
Biology Jessica Middle School


I've done this by building arrays previous to doing the select on
Course and in the While loop to list the courses pull the name from
the instructors array and locations array based on the matching id.

It works fine. No problem. I then got a requirement that stated they
wanted to be able to sort alphabetically ascending on the following
columns: course, instructor, location

So I built in the ability to do that using a self referencing
hyperlink on the column name with a variable for the column name they
wanted to sort on, and then used that in my SELECT statement to ORDER
BY on the column they chose ASC.

This works just fine too. Here's where the problem is.

Since the DB is normalized, it's sorting by the ID number which has no
relation to being sorted alphabetically.

i.e. If they sort on Location, they get the records back like this

course instructor location
Math Dave Middle School
Biology Jessica Middle School
Basket Weaving Dave High School
Science Bill Elementary School

Because Middle is id 1, High is id 2, and Elementary is id 3.

That's not what they want obviously.

So far the only thing I've come up with is to build an array of course
records in which I replace the ids with the corresponding names, then
sort the array based on the sort order the user wants, then loop
through the array to show the courses to them.

I can do that, but is there a better way? How do you all handle
dynamic sorting of normalized DBs?

How I got into this mess was by trying to do the right thing and
normalize my DB. It wasn't until they threw the sorting deal at me
that I realized I probably should have used the instructor name and
location name in the Course.instructorID and Course.locationID fields.
It would have saved me some grief, but part of the problem is that the
location name can be something like this:

Rio Bravo Elementary School

and it didn't seem right to me at the time to use that as the id in
the Course table for Location.

At any rate, just looking for some ideas.


Thanks.


Tagline of the day:
Small town sign: "Speed Limit 15 MPH: Our kids can't run any faster."



--
Leif Gregory
Development Supervisor
Licensing, Regulation and Small Projects Section
Application Development and Support Bureau
Information Technology Services Division
Runnels Building S3407
V: 505.827.2748
F: 505.827.2695

The Information Technology Services Division leads
the State of New Mexico in customer-focused IT services
as it supports the Department of Health in building
a healthy New Mexico.

[Back to original message]