[PHP-DB] sql, problem with join and presentation
Currently I display a list of classes. Simplified SQL and display below: SELECT * FROM classes WHERE classCategory='$Category' AND classDeleted=0 ORDER BY $order $reorder The presentation is: +--+--++ | CLASS TITLE | LOCATION | CLASS CODE | +--+--++ | CLASS DESCRIPTION | | CLASS INSTRUCTOR | | CLASS TIME | +--+ Now, things are getting a little more complicated. Each class is going to have sections. So the display will be: CLASS TITLE CLASS DESCRIPTION CLASS CODE : CLASS SECTION .. LOCATION .. CLASS TIME .. INSTRUCTOR example (simplified) +-+ | INTRO TO AAA| +-+ | This is a really interesting | | | +-+-+++ |HT-111:A | NYC | 12:00-4:00 | Albert Alkin | |HT-111:B | JC | 2:00-6:00 | Bob Bailey | |HT-111:C | BX | 4:00-8:00 | Chris Cawley | +-+-+++ I'm having a really hard time coming up with the sql for this. I want to (pseudo) select * from classes and classSections where classDeleted=0 and group by classCode tables below CLASSES classID classDescription classTexts classCost classDeleted CLASSCODES classCodeID classID classCodeSection classDate classTime classLocation classInstructor I'm going nuts trying to get this. I must be missing something simple. (using mysql) thx for any clues Gil -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] sql, problem with join and presentation
i think the newer versions of MYSQL allow for subselects and I think that is what you want. see www.mysql.net -Original Message- From: mayo [mailto:[EMAIL PROTECTED] Sent: Sunday, February 15, 2004 11:56 PM To: php-db Subject: [PHP-DB] sql, problem with join and presentation Currently I display a list of classes. Simplified SQL and display below: SELECT * FROM classes WHERE classCategory='$Category' AND classDeleted=0 ORDER BY $order $reorder The presentation is: +--+--++ | CLASS TITLE | LOCATION | CLASS CODE | +--+--++ | CLASS DESCRIPTION | | CLASS INSTRUCTOR | | CLASS TIME | +--+ Now, things are getting a little more complicated. Each class is going to have sections. So the display will be: CLASS TITLE CLASS DESCRIPTION CLASS CODE : CLASS SECTION .. LOCATION .. CLASS TIME .. INSTRUCTOR example (simplified) +-+ | INTRO TO AAA| +-+ | This is a really interesting | | | +-+-+++ |HT-111:A | NYC | 12:00-4:00 | Albert Alkin | |HT-111:B | JC | 2:00-6:00 | Bob Bailey | |HT-111:C | BX | 4:00-8:00 | Chris Cawley | +-+-+++ I'm having a really hard time coming up with the sql for this. I want to (pseudo) select * from classes and classSections where classDeleted=0 and group by classCode tables below CLASSES classID classDescription classTexts classCost classDeleted CLASSCODES classCodeID classID classCodeSection classDate classTime classLocation classInstructor I'm going nuts trying to get this. I must be missing something simple. (using mysql) thx for any clues Gil -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php Disclaimer This e-mail transmission contains confidential information, which is the property of the sender. The information in this e-mail or attachments thereto is intended for the attention and use only of the addressee. Should you have received this e-mail in error, please delete and destroy it and any attachments thereto immediately. Under no circumstances will the Cape Technikon or the sender of this e-mail be liable to any party for any direct, indirect, special or other consequential damages for any use of this e-mail. For the detailed e-mail disclaimer please refer to http://www.ctech.ac.za/polic or call +27 (0)21 460 3911 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] sql, problem with join and presentation
There are two ways to do what you ask: 1 - run your first query (just the classes no sections) and before the loop to display the results open a new - different connection to the DB then in the loop where you are displaying the results after displaying each class run a query to find all of it's sections and display them. Possible weakness is many connections to the DB (not a big deal but may not scale well) and if there is a class that has no section this quarter then you would only find that out after you displayed the class (could be fixed in your first query or by checking for sections before you displayed the class) 2 - Select everything (classes and sections) in one query SELECT * FROM classes, CLASSCODES WHERE classCategory='$Category' AND classDeleted=0 AND CLASSCODES.classID = CLASSES classID ORDER BY $order $reorder <-- not sure what you are doing here but you will need to add classID at the end of this list. Now you will get back these columns: classID classDescription classTexts classCost classDeleted classCodeID classID classCodeSection classDate classTime classLocation classInstructor And the columns from the CLASSES table will be duplicated for each section (this is why you must sort by classID to keep them all together). So before the loop to display the results you set $thisClassID = 0; And first thing in the loop you check: if ($thisClassID != result["classID"]) { /*This is a new class, display it's info*/ echo result["classDescription"] /* don't forget to reset this */ $thisClassID = result["classID"]; } /* now display the section info... */ Good Luck, Frank On 2/17/04 9:49 AM, "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote: > From: mayo [mailto:[EMAIL PROTECTED] > Sent: Sunday, February 15, 2004 11:56 PM > To: php-db > Subject: [PHP-DB] sql, problem with join and presentation > > > Currently I display a list of classes. > Simplified SQL and display below: > > SELECT * > FROM classes > WHERE > classCategory='$Category' AND > classDeleted=0 > ORDER BY $order $reorder > > The presentation is: > > +--+--++ > | CLASS TITLE | LOCATION | CLASS CODE | > +--+--++ > | CLASS DESCRIPTION | > | CLASS INSTRUCTOR | > | CLASS TIME | > +--+ > > > Now, things are getting a little more complicated. Each class is going to > have sections. So the display will be: > > CLASS TITLE > CLASS DESCRIPTION > > CLASS CODE : CLASS SECTION .. LOCATION .. CLASS TIME .. INSTRUCTOR > > example (simplified) > > +-+ > | INTRO TO AAA| > +-+ > | This is a really interesting | > | | > +-+-+++ > |HT-111:A | NYC | 12:00-4:00 | Albert Alkin | > |HT-111:B | JC | 2:00-6:00 | Bob Bailey | > |HT-111:C | BX | 4:00-8:00 | Chris Cawley | > +-+-+++ > > > I'm having a really hard time coming up with the sql for this. > > I want to (pseudo) > > select * > from classes and classSections > where classDeleted=0 > and group by classCode > > tables below > > CLASSES > classID > classDescription > classTexts > classCost > classDeleted > > CLASSCODES > > classCodeID > classID > classCodeSection > classDate > classTime > classLocation > classInstructor > > I'm going nuts trying to get this. I must be missing something simple. > (using mysql) > > thx for any clues > > Gil -- Frank Flynn Poet, Artist & Mystic -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php