Sorting table columns dynamically on normalized MySQL tables

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.

-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php


[Index of Archives]     [PHP Home]     [Apache Users]     [PHP on Windows]     [Kernel Newbies]     [PHP Install]     [PHP Classes]     [Pear]     [Postgresql]     [Postgresql PHP]     [PHP on Windows]     [PHP Database Programming]     [PHP SOAP]

  Powered by Linux