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
