On Tue, April 19, 2005 12:10 pm, Leif Gregory said: > 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. Yes. > 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. Don't do that. Just build a JOIN query and do one query. The only time to do what you are doing is when your JOIN would be millions (literally) of records, and your hardware can't handle it, and you only want 10 rows at a time anyway. *THEN* you can break the rules and send 10 little queries instead of one big one that will bring your server to its knees. > Since the DB is normalized, it's sorting by the ID number which has no > relation to being sorted alphabetically. No, it is *NOT* sorting on the ID number. By definition, in SQL, if you don't specify a sort order (or in this case a second order) then the SQL engine can and will output the records in any order it feels like. In that case of MySQL and ISAM tables, that *HAPPENS* to be the ID order, because the under-lying SQL engine happens to find it convenient to have them in that order. If you *DELETE* an ID number, then put another one in, but force it to be the same ID number you'll probably see the records come out in a different order. It's usually a really Bad Idea to do that (forcing an ID to be re-used) but for the purposes of learning/demonstration you can do it. At any rate, MySQL is *NOT* sorting by ID number. It's not sorting *AT* *ALL* except for what you told it to sort. It just spews out the records in any old order at all after "location" is done -- Which happens to be ID order, but that's more like coincidence than plan [*]. [*] Technically, it's not coincidence at all, and has to do with the low-level MySQL ISAM code and how it works, but that's not a documented feature, per se. > 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. Why not? What *DO* they want, then? Do you want, perhaps, to have a DEFAULT sort order, which kicks in after their chosen ordering? Perhaps you could do (here's your magic bullet): <?php $default_sort_order = "course, instructor, location"; . . . $query .= "ORDER BY $_GET[order_by], $default_sort_order "; ?> So now, they click on the link with: ?order_by=location And their choice of "location" kicks in *FIRST*. After that, because of the ", $default_sort_order" you also sort (within location) by "course" Then by "instructor" Then, oddly enough, by "location" again, but that's kinda irrelevant. It won't *hurt* anything [**], mind you, it's just kinda silly, since you have already sorted by location in the very first place. But in this case, kinda silly makes your life really simple. [**] Technically, it's a little inefficient to have that extra bogus "location" in there at the end, but you're probably not sorting enough rows for it to make any measurable difference in your results... And MySQL might even be smart enough to optimize it out anyway. > 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: No, no, no. You did the right thing. :-) You just needed to go farther down the road you are on, instead of stopping partway. -- Like Music? http://l-i-e.com/artists.htm -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
