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, "php-db-digest-help@xxxxxxxxxxxxx"
<php-db-digest-help@xxxxxxxxxxxxx> wrote:
> From: mayo [mailto:mayo@xxxxxxxxxxxxxxxxxx]
> Sent: Sunday, February 15, 2004 11:56 PM
> To: php-db
> Subject: 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
