Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    Super Moderator JohnDubya's Avatar
    Join Date
    Nov 2006
    Location
    Missouri
    Posts
    634
    Thanks
    12
    Thanked 18 Times in 18 Posts

    Help with selecting people's information and then filtering it

    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:

    PHP Code:
    $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.

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    basically something like this:

    Code:
    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.

  • #3
    Super Moderator JohnDubya's Avatar
    Join Date
    Nov 2006
    Location
    Missouri
    Posts
    634
    Thanks
    12
    Thanked 18 Times in 18 Posts
    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?


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •