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 br/br/ | | CLASS INSTRUCTOR br/br/ | | 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 br/br/ |
| CLASS INSTRUCTOR br/br/ |
| 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
[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 br/br/ | | CLASS INSTRUCTOR br/br/ | | 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
