|
|
Posted by Leif Gregory on 04/20/05 15:41
Hello Richard,
Tuesday, April 19, 2005, 9:12:14 PM, you wrote:
R> Just build a JOIN query and do one query.
Doing a join on four tables is ok? (serious question, not being
facetious).
R> No, it is *NOT* sorting on the ID number.
I can definitely say it is sorting on locationID, categoryID,
or instructorID. It's not alphabetical. When I view the listing after
a sort on say locationID, it's not alphabetical but ordered by which
class has the lowest numerical value in the locationID field and then
ASC from there.
R> By definition, in SQL, if you don't specify a sort order (or in
R> this case a second order) then the SQL engine can and will output
R> the records in any order it feels like.
Really? I didn't know that. I thought it started at record 0 and then
output them in the order they appeared in the table data view (using
something like PHPMyAdmin.)
R> In that case of MySQL and ISAM tables, that *HAPPENS* to be the ID
R> order, because the under-lying SQL engine happens to find it
R> convenient to have them in that order.
Ahhh. Ok, there we go.
R> If you *DELETE* an ID number, then put another one in, but force it
R> to be the same ID number you'll probably see the records come out
R> in a different order. It's usually a really Bad Idea to do that
R> (forcing an ID to be re-used) but for the purposes of
R> learning/demonstration you can do it.
I'll give it a shot sometime to see. It'll be interesting to find out.
R> At any rate, MySQL is *NOT* sorting by ID number. It's not sorting
R> *AT* *ALL* except for what you told it to sort. It just spews out
R> the records in any old order at all after "location" is done --
R> Which happens to be ID order, but that's more like coincidence than
R> plan [*].
Maybe I confused you with the "ID" nomenclature. I mean to say it's
sorting by locationID, categoryID, instructorID (whichever column I
clicked on), and since those are integer values they aren't sorted
alphabetically).
>> That's not what they want obviously.
R> Why not?
R> What *DO* they want, then?
If they sort by location, they want the course records to show up in
alphabetical order based on location. Right now it does sort by
location, but it's not alphabetical because the Course.locationID,
Course.instructorID, and Course.categoryID are integers which relate
to three other respective tables. There is no join, and I didn't think
you were "supposed" to do a join on four tables.
R> Do you want, perhaps, to have a DEFAULT sort order, which kicks in
R> after their chosen ordering?
By default it sorts by Course date.
R> Perhaps you could do (here's your magic bullet):
R> <?php
R> $default_sort_order = "course, instructor, location";
R> .
R> .
R> .
R> $query .= "ORDER BY $_GET[order_by], $default_sort_order ";
?>>
I'm kinda doing that already as:
if (isset($_GET['orderBy']))
$orderBy = $_GET['orderBy'];
else
$orderBy = 'courseDate';
R> Then, oddly enough, by "location" again, but that's kinda
R> irrelevant. It won't *hurt* anything [**], mind you, it's just
R> kinda silly, since you have already sorted by location in the very
R> first place.
Got it.
R> [**] Technically, it's a little inefficient to have that extra
R> bogus "location" in there at the end, but you're probably not
R> sorting enough rows for it to make any measurable difference in
R> your results... And MySQL might even be smart enough to optimize it
R> out anyway.
True enough. I'm guessing they'll be maybe 30 to 50 records at any one
time.
R> You did the right thing. :-)
<grin> Tell me that again once I get the sorting working right! <grin>
R> You just needed to go farther down the road you are on, instead of
R> stopping partway.
Only stopped because I got stuck... :-)
Thanks.
--
Leif (TB lists moderator and fellow end user).
Using The Bat! 3.0.9.17 Return under Windows XP 5.1
Build 2600 Service Pack 2 on a Pentium 4 2GHz with 512MB
Navigation:
[Reply to this message]
|