|
|
Posted by Richard Lynch on 04/20/05 06:12
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";
Navigation:
[Reply to this message]
|