View Full Version : Help with selecting people's information and then filtering it

02-01-2007, 10:01 PM
I have a database that keeps information on students and their parents for each Chapter that we have (we have about 40 Chapters). Normally, we add students to the database and then add (associate) parents with them. Here's how the database is set up:

A "Person" table that has each person's (students and parents) information (first and last name, their birthday, SchoolID they are associated with, graduation from school date, email address, etc.

A "student_parents" table which holds the chapter_id, the parent_id (which equals the PersonID from the Person table, so we can grab each parent's Person info), and the student_id (which equals the PersonID of the student that this parent is associated with).

So, we have a roster which chooses every parent from the currently selected chapter. It prints out those parents just fine. But now, we need to be able to filter only parents of students who go to a certain school.

So I've got all the parent information stored in the $x query. Now, I do a query like this:

$supermanx = mysql_query("SELECT * FROM student_parents AS sp, Person AS p WHERE
sp.parent_id = '$PersonID' AND p.PersonID = sp.student_id AND p.SchoolID = '$school_filter'");

(the $PersonID is the parent's PersonID from the $x query, which grabs the PersonID from the Person table)

This grabs the students who go to a certain school who are a child of a parent from the currently selected Chapter. Both queries work fine, but I can't for the life of me figure out how to filter the first $x query with the second $supermanx query. I only want to show parents of students who go to that school, but the script is printing out info from the $x query...but I don't want ALL the info from the $x query...only the info from the parents who match the $supermanx query results. I hope that makes at least a tiny bit of sense. Let me know if I can explain better. Thanks so much.

02-02-2007, 01:58 AM
basically something like this:

select parentid
from parenttable
where childid IN
(select childid from childtable where schoolid = whatever)

the subquery will return the ids of the children for the particular schoolid.
the parent id will be called only when they have a child id that exists in the inner query.

02-02-2007, 03:06 AM
So I finally figured out how it's working. It grabs all the parents that are listed in the "student_parents" table, grabs the information from the "Person" table, and prints that out. Then, if the user clicks on "Filter by school," it then does a query to find the student_id numbers in the "student_parent" table, matches them with the "Person" rows of those students and pulls the SchoolID. Then, if the SchoolID matches the SchoolID that the user is searching for, it echoes that row. If the SchoolID doesn't match, it doesn't do anything. There's got to be an easier way of doing this! Also, I can't filter by anything else when it does this because the queries are pretty much canned. Any other ideas?