Hello Jay,
Tuesday, April 19, 2005, 1:28:25 PM, you wrote:
J> Normalization has no effect on sort order where any DB is concerned.
J> Sort order is determined in the sort clause in the query. So if you want
J> to sort by location and you are using MySQL your ORDER BY clause should
J> be location....(regardless of joins)
J> SELECT foo
J> FROM bar
J> ORDER BY location
J> returns
J> course instructor location
J> Science Bill Elementary School
J> Basket Weaving Dave High School
J> Math Dave Middle School
J> Biology Jessica Middle School
Maybe I wasn't clear.
SELECT *
FROM Course
ORDER BY locationID ASC
Now the ORDER BY can be any one of five things based on the variable
passed by the hyperlink for the column they clicked on (location,
course, date, category, and instructor
Because locationID is an integer which directly relates to the
Instructors table id field (also an integer), there is no way in heck
that it can sort that any other way than numerically. There is no join
between the Course, Locations, and Instructors tables. All the Course
table knows is that there is an integer in the instructorID,
categoryID, and locationID field. Therefore it gets sorted
numerically, not alphabetically.
As I said, I was trying to keep the example simple. I actually have
the following tables in the DB.
Courses
Instructors
Locations
Categories
Students
Registrations
Supervisors
Courses contains information pertinent to a course.
- id
- name
- courseDate
- courseTime
- ampm (morning or afternoon)
- capacity (number of seats available)
- locationID (corresponding id from the Locations table)
- instructorID (corresponding id from the Instructors table)
- seatsLeft (counter for the number of available seats)
- description (what the course is about)
- categoryID (corresponding id from the Categories table)
Instructors
- id
- firstName
- lastName
- email
- phone
- image
- about
Locations
- id
- name
- seatingCapacity (How many true seats there are in the room)
- address
- directions
Categories
- id
- name
Students
- id
- firstName
- lastName
- email
- phone
- password
- username
- divisionOrFacility
- programArea
- supervisorID
Supervisors
- id
- firstName
- lastName
- email
- phone
Registrations
- id
- courseID
- studentID
- attended
So... For me to list the upcoming courses on the main page, I have the
following column in the table:
Course Name - From Course Table
Open Seats - From Course Table
Category - From Course Table (match id in Categories table)
Date - From Course Table
Time - From Course Table
Location - From Course Table (match id in Locations table)
Instructor - From Course Table (match id in Instructors table)
Category, Location, and Instructor are integers. What I did to get the
names respective to the integer value is that beforehand I built an
array for those three tables then I match the id from the Course Table
for each of the three with their respective array, and then display
the name.
Since the SELECT statement is based on the Course table, and the
Course table contains only integers, it's sorting by the integer value
of the categoryID, instructorID, or locationID in the Course table.
The actual SELECT statement is as follows:
$sqlCourses="SELECT * FROM " . $tbl_courses . " WHERE courseDate > '" . date("Y-m-d") . "' ORDER BY " . $orderBy . " ASC";
the $orderBy variable is set via $_GET['orderBy'] which is sent by the
table headers as below:
$tblHeaders = '<th><a href="' . $_SERVER['PHP_SELF'] . '?orderBy=name">Course</a></th><th>Open Seats</th><th><a href="' . $_SERVER['PHP_SELF'] . '?orderBy=categoryId">Category</a></th><th><a href="' . $_SERVER['PHP_SELF'] . '?orderBy=courseDate">Date</a></th><th>Time</th><th><a href="' . $_SERVER['PHP_SELF'] . '?orderBy=roomId">Location</a></th><th><a href="' . $_SERVER['PHP_SELF'] . '?orderBy=instructorId">Instructor</a></th>';
The sorting works just fine, but it's numerical ASC based off the
integer value (instructorID, categoryID, or locationID). It is *not*
alphabetical, which is what the customer wants.
If they sort by Location, the location column should be alphabetical
ASC, if by course name, then alphabetical ASC by the course name.
Is that a bit clearer now?
Sorry that this has degenerated into a MySQL question rather than PHP.
I was originally looking for how people handled it in PHP.
BTW, just for clarity sake on the arrays used to build the lookups to
display the list of courses, I'm doing an include with the following:
****************************************************************************
<?php
//Get instructors --------------------------------------------------------------
$sqlInstructors="SELECT * FROM " . $tbl_instructors;
$resultInstructors=mysql_query($sqlInstructors) or die('Failed to get instructors. Please contact ' . $maintainer);
while ($rInstructors=mysql_fetch_assoc($resultInstructors))
{
$arrInstructors[$rInstructors['id']][name] = $rInstructors['firstName'] . " " . $rInstructors['lastName'];
$arrInstructors[$rInstructors['id']][email] = $rInstructors['email'];
$arrInstructors[$rInstructors['id']][phone] = $rInstructors['phone'];
}
//------------------------------------------------------------------------------
//Get training locations -------------------------------------------------------
$sqlRooms="SELECT * FROM " . $tbl_rooms;
$resultRooms=mysql_query($sqlRooms) or die('Failed to get rooms. Please contact ' . $maintainer);
while ($rRooms=mysql_fetch_assoc($resultRooms))
{
$arrRooms[$rRooms['id']][name] = $rRooms['name'];
$arrRooms[$rRooms['id']][address] = $rRooms['address'];
$arrRooms[$rRooms['id']][seatingCapacity] = $rRooms['seatingCapacity'];
}
//------------------------------------------------------------------------------
//Get Categories ---------------------------------------------------------------
$sqlCategories="SELECT * FROM " . $tbl_category;
$resultCategories=mysql_query($sqlCategories) or die('Failed to get categories. Please contact ' . $maintainer);
while ($rCategories=mysql_fetch_assoc($resultCategories))
{
$arrCategories[$rCategories['id']][name] = $rCategories['name'];
}
//------------------------------------------------------------------------------
?>
****************************************************************************
Which I then use in the listing while loop for Course table records as
follows:
****************************************************************************
echo '<td>' . $arrCategories[$rCourses['categoryId']]['name'] . '</td>';
echo '<td><a href="view_location.php?id=' . $rCourses['roomId'] . '"
title="Location Information">' .
$arrRooms[$rCourses['roomId']]['name'] . '</a></td>';
echo '<td><a href="view_instructor.php?id=' . $rCourses['instructorId'] . '"
title="Instructor Information">' .
$arrInstructors[$rCourses['instructorId']]['name'] . '</a></td>';
echo '</tr>';
****************************************************************************
Basically what happens is that the lookup builder creates an array
like this:
****************************************************************************
Array
(
[1] => Array
(
[name] => Leif Gregory
[email] => abc@xxxxxxxxxxx
[phone] => 505-123-4567
)
[2] => Array
(
[name] => Jenny Craig
[email] => jcraig@xxxxxxxxxxx
[phone] => 505-222-2222
)
[6] => Array
(
[name] => Billy horton
[email] => billy@xxxxxxxxxxx
[phone] => 505-839-1234
)
****************************************************************************
So. $arrInstructors[$rCourses['instructorId']]['name']
If the instructorId from the Course table was '2', then the above
would give me: "Jenny Craig".
You can probably figure that out yourself, but I'm trying to make sure
everyone completely understands what's going on.
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
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
