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
[PHP] Sorting table columns dynamically on normalized MySQL tables
Hello php-general, 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. Let's just keep it simple (my DB is a bit more complex). We have a DB called Courses with three tables. Instructors - id name Locations id name Course - id name instructorID - From Instructors table locationID - From Locations table So let's put in some data Instructors - id name 1Bill 2Dave 3Jessica Locations - id name 1Middle School 2High School 3Elementary School Course - id name instructorID locationID 1Basket Weaving2 2 2Math 2 1 3Science 1 3 4Biology 3 1 Just in case, I'm actually dealing with three more tables, so I don't think doing weird joins will work, but I'm trying to keep this simple. Further, the instructors table actually has six fields, the Locations table has four fields. Those extra fields are descriptive pieces for each, i.e. phone numbers, e-mail address, office number, address etc for each instructor. 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. It works fine. No problem. I then got a requirement that stated they wanted to be able to sort alphabetically ascending on the following columns: course, instructor, location So I built in the ability to do that using a self referencing hyperlink on the column name with a variable for the column name they wanted to sort on, and then used that in my SELECT statement to ORDER BY on the column they chose ASC. This works just fine too. Here's where the problem is. Since the DB is normalized, it's sorting by the ID number which has no relation to being sorted alphabetically. 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. So far the only thing I've come up with is to build an array of course records in which I replace the ids with the corresponding names, then sort the array based on the sort order the user wants, then loop through the array to show the courses to them. I can do that, but is there a better way? How do you all handle dynamic sorting of normalized DBs? 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: Rio Bravo Elementary School and it didn't seem right to me at the time to use that as the id in the Course table for Location. At any rate, just looking for some ideas. Thanks. Tagline of the day: Small town sign: Speed Limit 15 MPH: Our kids can't run any faster. -- Leif Gregory Development Supervisor Licensing, Regulation and Small Projects Section Application Development and Support Bureau Information Technology Services Division Runnels Building S3407 V: 505.827.2748 F: 505.827.2695 The Information Technology Services Division leads the State of New Mexico in customer-focused IT services as it supports the Department of Health in building a healthy New Mexico. -- 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