Re: [PHP] Sorting table columns dynamically on normalized MySQL tables
On Wed, April 20, 2005 5:53 am, Leif Gregory said: So joining on four tables isn't considered bad practice? No, it's great practice. The only thing to watch for is just how *BIG* will the number of tuples grow? If you can multiply together the number of rows in all four tables and come up with 1,000,000 you're fine. If not, you have to start thinking about how you can get just the ones you want for each query, and do it in such a way the MySQL never has to cope with 1,000,000 rows at once. Actually MySQL is quite happy to do 1,000,000 rows -- It's your hardware that won't like it. :-) So it really depends on what's *IN* the data, rather than the actual number of tables. Be sure you always get your WHERE clauses right. One rule of thumb: Work your way through all tables being joined from left to right: FROM table1, table2, table3, table4, ... For each tableX, make sure that you are relating it back to a previous table, with an INDEXED key field in your WHERE clause with AND between them: WHERE table1.indexA = table2.indexB AND table2.indexC = table3.indexD AND table3.indexE = table4.indexE It doesn't matter on table3 if you have it tied to table2 (as above) or table 1 -- So long as the fields are indexed, and you can trace back from every tableX to table1 *somehow* But you wouldn't want just *this* WHERE table1.indexA = table2.indexB AND table3.indexC = table4.indexD You've got nothing to tie table3 and table4 back to table1, so every single row in the 3/4 combination is going to be listed with every single row with the 1/2 combination, in every possible permutation... Try it with some very small (number of rows) tables just to see what happens! -- 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
Re: [PHP] Sorting table columns dynamically on normalized MySQL tables [LONG]
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 = 'tha href=' . $_SERVER['PHP_SELF'] .
'?orderBy=nameCourse/a/ththOpen Seats/ththa href=' .
$_SERVER['PHP_SELF'] . '?orderBy=categoryIdCategory/a/ththa href=' .
$_SERVER['PHP_SELF'] . '?orderBy=courseDateDate/a/ththTime/ththa
href=' . $_SERVER['PHP_SELF'] . '?orderBy=roomIdLocation/a/ththa
href=' . $_SERVER['PHP_SELF'] . '?orderBy=instructorIdInstructor/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))
{
Re: [PHP] Sorting table columns dynamically on normalized MySQL tables [LONG]
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: I'm curious why you aren't joining the other tables? That will solve all your woes... thnx, Chris -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Sorting table columns dynamically on normalized MySQL tables [LONG]
[snip] 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 [/snip] If you had a JOIN to the location table you could order by the actual location. Here is an example... SELECT c.courseName, l.locationName FROM Course c, Location l WHERE c.locationID = l.locationID ORDER BY l.locationName [snip] Sorry that this has degenerated into a MySQL question rather than PHP. I was originally looking for how people handled it in PHP. [/snip] Actually it didn't degenerate, it was a SQL question all along. Unless you had a desire to build sortable arrays in PHP this type of sort should always be done on the SQL side because it would be much more efficient. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Sorting table columns dynamically on normalized MySQL tables [LONG]
[snip] Sorry that this has degenerated into a MySQL question rather than PHP. I was originally looking for how people handled it in PHP. --- Actually it didn't degenerate, it was a SQL question all along. Unless you had a desire to build sortable arrays in PHP this type of sort should always be done on the SQL side because it would be much more efficient. [/snip] I also should have said that it the joins that allow you to maintain normalized tables, for without joins you would be stuck with doing the magic in the programming language. Since most DB engines are optimized for handling joins leave that efficiency on that side of the equation. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Sorting table columns dynamically on normalized MySQL tables
Hello Kim, Tuesday, April 19, 2005, 6:44:58 PM, you wrote: K If you are still looking for tips and want to get K complicated/fancy, I have seen columns used called sort_order more K than a few times. This should not be too hard to maintain for K things like a few buildings, etc., where the lookup table does not K change much over time. You would then have another option for your K oder by clause. I understand what you mean, but I'm still in the same boat. There is no join between the four tables (mainly because I didn't think you were supposed to do joins on four tables. I've written a couple other replies which I think more clearly state where my problem is so I won't retype them here. Thanks though. -- 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
Re: [PHP] Sorting table columns dynamically on normalized MySQL tables
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 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Sorting table columns dynamically on normalized MySQL tables
Hello Chris, Tuesday, April 19, 2005, 1:23:53 PM, you wrote: C Firstly, what DB are you using? MySQL. C SELECT C course.name, C location.name, C instructor.name C FROM C course C INNER JOIN location ON location.id = course.locationID C INNER JOIN instructor ON instructor.id = course.instroctorID C ORDER BY instructor.name C (or location.name or course.name). C It should be as simple as that... So joining on four tables isn't considered bad practice? Technically it's going to be five tables because the whole HTML table layout changes to include enroll and disenroll buttons once they log in and based on if they are enrolled or not in a particular course which comes from the registrations table, which is simply their the id from the Students table and the id from the Course table. 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
Re: [PHP] Sorting table columns dynamically on normalized MySQL tables
R Just build a JOIN query and do one query. Doing a join on four tables is ok? (serious question, not being facetious). Yes. I've built a query before (for reporting purposes) that join 15 tables... Just make sure the tables are indexed properly. thnx, Chris -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Sorting table columns dynamically on normalized MySQL tables
[snip] Doing a join on four tables is ok? (serious question, not being facetious). [/snip] Yes, it is OK. Make sure that you have the tables properly indexed and you'll have no problems. [snip] 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.) [/snip] PHPMyAdmin uses an ORDER BY clause and it is initially on the primary key index for any given table. Then PHPMyAdmin uses relevant column data for the sort. SQL CAN and DOES spit out the records any way it wants when no sort is specified. You dod not typically see it because of query caching. (Ever notice that a second issue of the same query you just made is much faster? Query caching.) [snip] 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. [/snip] +1 on the bad idea for primary key re-use. [snip] 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. [/snip] Furthering our discussion of the ORDER BY clause...you can put more than one sort criteria there... ORDER BY l.locationName, c.courseName -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Sorting table columns dynamically on normalized MySQL tables
I can do that, but is there a better way? How do you all handle dynamic sorting of normalized DBs? Firstly, what DB are you using? Secondly, just select and sort on the columns. An example query would be: SELECT course.name, location.name, instructor.name FROM course INNER JOIN location ON location.id = course.locationID INNER JOIN instructor ON instructor.id = course.instroctorID ORDER BY instructor.name (or location.name or course.name). It should be as simple as that... thnx, Chris -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Sorting table columns dynamically on normalized MySQL tables
[snip] I can do that, but is there a better way? How do you all handle dynamic sorting of normalized DBs? [/snip] Normalization has no effect on sort order where any DB is concerned. Sort order is determined in the sort clause in the query. So if you want to sort by location and you are using MySQL your ORDER BY clause should be location(regardless of joins) SELECT foo FROM bar ORDER BY location returns course instructor location Science Bill Elementary School Basket Weaving Dave High School Math Dave Middle School Biology Jessica Middle School The results with course as the sort qualifier course instructor location Basket Weaving Dave High School Biology Jessica Middle School Math Dave Middle School Science Bill Elementary School You have over-compicated a simple issue -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Sorting table columns dynamically on normalized MySQL tables
Greetings, If you are still looking for tips and want to get complicated/fancy, I have seen columns used called sort_order more than a few times. This should not be too hard to maintain for things like a few buildings, etc., where the lookup table does not change much over time. You would then have another option for your oder by clause. cheers, KB On 4/19/05, Jay Blanchard [EMAIL PROTECTED] wrote: [snip] I can do that, but is there a better way? How do you all handle dynamic sorting of normalized DBs? [/snip] Normalization has no effect on sort order where any DB is concerned. Sort order is determined in the sort clause in the query. So if you want to sort by location and you are using MySQL your ORDER BY clause should be location(regardless of joins) SELECT foo FROM bar ORDER BY location returns course instructor location Science Bill Elementary School Basket Weaving Dave High School Math Dave Middle School Biology Jessica Middle School The results with course as the sort qualifier course instructor location Basket Weaving Dave High School Biology Jessica Middle School Math Dave Middle School Science Bill Elementary School You have over-compicated a simple issue -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Sorting table columns dynamically on normalized MySQL tables
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
