php-general | 2001092

[PHP] Re: Troublesome complex fetching from database From: Justin Garrett (usjgarrett <email protected>)
Date: 09/30/01

Try the following to organize your results. You'll have to check the table
names and typos etc.

$sql = "SELECT schooltypes.schooltypes,
                           schools.schoolname,
                           teachers.lastname,
                           teachers.tuserid
            FROM schooltypes,
                          schools,
                          teachers,
                          teachers_school
           WHERE schooltypes.typeid = schools.typeid
           AND schools.schoolid = teachers_schools.schoolid
           AND teacher_schools.tuserid = teachers.tuserid";

$result = /* perform query */;

while($row = mysql_fetch_object($result)){

    $data[$row->schooltypes][$row->schoolname][] = "$row->tuserid
$row->lastname";

}

/* organize */

for(reset($data); $school_type = key($data); next($data)){
    echo "$school_type<br><br>";
    $schools = $data[$school_type];
    for(reset($schools); $school_name = key($schools); next($schools)){
        echo "$school_name<br><br>";
        $teachers = $data[$school_type][$school_name];
        $count = count($teachers);
        for($i = 0; $i < $count; $i++){
            echo "$teachers[$i]<br>";
        }
    }
} 

--
Justin Garrett

"Kath" <kath <email protected>> wrote in message
news:002601c149f3$7dec4000$82e53181 <email protected>
> Quite an interesting quandry I have.
>
> What I have is this:
>
> Table schooltypes:
> (typeid is a unique# iding the type, schooltypes is the type of school,
say
> "Elementary School" or "High School")
>
> | typeid | schooltypes |
>
> Table schools:
> (typeid is the type the school falls under, schoolid is the unique# iding
> the school, schoolname is the general name like "Park Shore High School")
>
> | typeid | schoolid | schoolname |
>
> Table teachers:
> (tuserid is their unique id, salutation is the Mr/Mrs/Miss/Ms/Dr, last
name
> is well, duh :))
>
> | tuserid | salutation | last name |
>
> Table teachers_schools:
> (tuserid is the teacher's id, next to the school they are assigned to.
Note
> the possiblity for a teacher assigned to more than one school (hello,
> cutbacks and skeleton budgets)).
>
> | tuserid | schoolid |
>
> What I need to be able to do is list all this information as such:
>
> School type:
>
>     + School 1
>             - Teacher and their Teacher ID
>             - Teacher and their Teacher ID
>
>     + School 2
>             - Teacher and their Teacher ID
>             - Teacher and their Teacher ID
>             - Teacher and their Teacher ID
>
>     + School 3
>             - Teacher and their Teacher ID
>             - Teacher and their Teacher ID
>
> Another School Type:
>
>     + School 5
>             - Teacher and their Teacher ID
>             - Teacher and their Teacher ID
>
>     + School 7
>             - Teacher and their Teacher ID
>             - Teacher and their Teacher ID
>
> Currently, I AM able to list the school type and the school correctly.
> However, when I come to listing the teachers in the school, every teacher
is
> listed under every school, some more than one time.
>
> Any ideas/code snips on how to do this?
>
> In what direction should be I solving this?
>
> Going from type->school->teachers assigned->teacher info?
>
> Or teachers assigned->teacher info->schools->type?
>
> Right now I am doing the first, sorta.
>
> - k
>

-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, e-mail: php-general-unsubscribe <email protected>
For additional commands, e-mail: php-general-help <email protected>
To contact the list administrators, e-mail: php-list-admin <email protected>