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.
Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Super Moderator JohnDubya's Avatar
    Join Date
    Nov 2006
    Location
    Missouri
    Posts
    634
    Thanks
    12
    Thanked 18 Times in 18 Posts

    Need to do a SELECT and then another SELECT from those results

    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.
    Last edited by JohnDubya; 02-02-2007 at 08:23 PM.

  • #2
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by JohnDubya View Post
    I hope that makes at least a tiny bit of sense. Let me know if I can explain better. Thanks so much.
    sorry, but it doesn't makes a lott of sense to me.
    can you post the actual script you use (only the relevant parts of your script please) and maybe an example with actual studen/parent data and the expected result for that data.

    i think you need some sort of subselect so you might want to also tell us what version of MySQL you are running.
    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

  • #3
    Super Moderator JohnDubya's Avatar
    Join Date
    Nov 2006
    Location
    Missouri
    Posts
    634
    Thanks
    12
    Thanked 18 Times in 18 Posts
    Well, here goes nothing! lol

    FYI, when a Parent is added, they are first added to the Person table which holds their names, email address, etc. (see screen of Person table structure below). This creates a PersonID for them. Then, the parent's PersonID is added to the ChapterPerson table, which is done so the person is associated with only one chapter. And lastly, if the parent is to be associated with a child/children, the parent's PersonID is added to the student_parents table. This also puts in the parent's child's PersonID.

    Ok, now on to the code. Here's the main query to grab the Parents' information:

    PHP Code:
    $x mysql_query("SELECT * FROM Person AS p, ChapterPerson AS cp WHERE cp.ChapterID = '7' 
    AND cp.PersonID = p.PersonID AND p.PersonType = '3' ORDER BY LastName ASC LIMIT 0, 30"
    ); 
    And here's the code that makes the table to show the Parents' information:

    PHP Code:
    <form name="roster" id="roster" method="post" action="<?=$_SERVER['PHP_SELF']?>?limit=<?=$limit?>&amp;ShowPage=<?=$ShowPage?>&amp;OrderBy=<?=$OrderBy?>&amp;SortOrder=<?=$SortOrder?>&amp;PersonType=<?=$PersonType?>&amp;pagination=<?=$_GET['pagination']?>" style="display: inline;">

        <table id="data_list" class="seven_col">
            <tr>
                <th>Name:</th>
                <th>Address:</th>
                <th>Contact Info:</th>
                <th>Children:</td>
                <th class="actions">Actions</th>
                <th class="checkbox"> </th>
            </tr>

    <?php
    // get general info about the students related to this chapter
            
    $RowCount 0;
            
    $superman_stat 0;
            
    $Color1 '#EDF3FE';
            
    $Color2 '#ffffff';

    // get info from the join statement above

    if(mysql_num_rows($x) == 0){
        echo(
    '<tr><td colspan="7" style="text-align:center;font-weight: bold;">There are currently no records to display.</td></tr>');
    } else {
        
    $no_record '';
            
        while(
    $y mysql_fetch_array($x)) {
            
    $PersonID $y['PersonID'];
            
    $salutation $y['salutation'];
            
    $first_name $y['FirstName'];
            
    $last_name $y['LastName'];
            
    $Email $y['Email'];
            
    $AddressID $y['AddressID'];
            
    $home_phone_number $y['Phone'];
            
    $cell_phone_number $y['cell_phone'];
            
    $work_phone_number $y['work_phone'];                                        
    // get into the address table for their address
            
    $xxx = @mysql_query("SELECT * FROM Address WHERE person_id='$PersonID'");
                
            while(
    $yyy mysql_fetch_array($xxx)) {
                
    $street_one $yyy['Street'];
                
    $street_two $yyy['Street2'];
                
    $city $yyy['City'];
                
    $State $yyy['State_Abbr'];
                
    $zipcode $yyy['Zipcode'];
            }
                
            
    $RowColor = ($RowCount 2) ? $Color1 $Color2;

    //filter for school
            
    if($_SESSION['filter']['school'] == 'filter_by_school') {
                
    $superman_x = @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'");
                if(@
    mysql_num_rows($superman_x) == 0) {
                    
    $superman_stat 1;
                }
            }

    if (
    $superman_stat == 0) {
        
    ?> 

        <tr>
            <td bgcolor="<?=$RowColor?>"><?=$salutation?> <?=$first_name.' '.$last_name?></td>
            
            <td bgcolor="<?=$RowColor?>" class="address"><?=$street_one?><br />
    <?php if ($street_two != NULL) { echo "$street_two <br />"; } ?>
    <?php 
    if ($city == '' || $State == '') { 
                    
    $comma '';
                } else {
                    
    $comma ', ';
                } echo 
    "$city$comma $State"
    ?> 
            <?=$zipcode?></td>
            <td bgcolor="<?=$RowColor?>">Home Phone: <?=$home_phone_number?><br/>Cell Phone: <?=$cell_phone_number?><br/>Email: <?=$Email?></td>
            <td bgcolor="<?=$RowColor?>">
                <?php
                
                $kids_x 
    = @mysql_query("SELECT p.FirstName, p.LastName FROM student_parents AS sp, Person AS p, Student AS s WHERE sp.parent_id = '$PersonID' AND s.PersonID = sp.student_id AND p.PersonID = sp.student_id");
                
                while (
    $kids_y = @mysql_fetch_array($kids_x)) {
                    echo (
    $kids_y['FirstName'].' '.$kids_y['LastName'].'<br/>');
                }
                
                
    ?>
            </td>
    <?php
    // end of superman_stat if/then
                
                
    $RowCount++;
    ?>
    So, as you can see, the first query ($x) grabs the parent information and displays it in the table. Then, if the user clicks to filter by parents who have children in a certain school (if $_SESSION['filter']['school'] is set to "filter_by_school"), then the query $superman_x is done. That query looks for students of the school that the user chooses who are children of parents in a certain Chapter. Then, if the result of $superman_x matches the result from $x, it prints it. If not, it does nothing and goes to the next row.

    This just seems too complicated. I would like to do this with one query if possible. Here's a screen(with sensitive data erased) of what the roster looks like when it grabs the information. And here are screens of the structure of the Person, ChapterPerson, and student_parents tables.

    I'm running MySQL version 3.23.58-16.FC2.1.

    Thanks for your help!
    Last edited by JohnDubya; 02-02-2007 at 02:53 PM.

  • #4
    Super Moderator JohnDubya's Avatar
    Join Date
    Nov 2006
    Location
    Missouri
    Posts
    634
    Thanks
    12
    Thanked 18 Times in 18 Posts
    I checked out a tutorial on joins, and it looks like subselects are what I need, eh? I just put in a request to get our MySQL version updated to 4.2.x. Any leads as to the best approach to do this query in the most efficient way?

  • #5
    teh Moderatorinator
    Join Date
    Sep 2004
    Location
    USA
    Posts
    2,472
    Thanks
    4
    Thanked 40 Times in 40 Posts
    Meh, I'm trying to follow....

    So you want to display all the parents who have children that go to a certain school?

  • #6
    Super Moderator JohnDubya's Avatar
    Join Date
    Nov 2006
    Location
    Missouri
    Posts
    634
    Thanks
    12
    Thanked 18 Times in 18 Posts
    Exactly.

    Your "I'm trying to follow" statement is exactly what I'm doing! lol It's sooo hard for me to wrap my head around these complex issues. From what I'm reading about this, looks like I'm going to have to wait until we can upgrade to MySQL 4.2 and start using subselects. *shrug*

  • #7
    teh Moderatorinator
    Join Date
    Sep 2004
    Location
    USA
    Posts
    2,472
    Thanks
    4
    Thanked 40 Times in 40 Posts
    Give me a bit and i'll put something together, what values are coming from user input?

    This code looks familiar!

  • #8
    Super Moderator JohnDubya's Avatar
    Join Date
    Nov 2006
    Location
    Missouri
    Posts
    634
    Thanks
    12
    Thanked 18 Times in 18 Posts
    haha, you are so not even kidding! Same dude did this code as well. *sigh*

    I'm needing to get the Parent's info from the Person table like this:

    PHP Code:
    while($y mysql_fetch_array($x)) {
        
    $PersonID $y['PersonID'];
        
    $salutation $y['salutation'];
        
    $first_name $y['FirstName'];
        
    $last_name $y['LastName'];
        
    $Email $y['Email'];
        
    $AddressID $y['AddressID'];
        
    $home_phone_number $y['Phone'];
        
    $cell_phone_number $y['cell_phone'];
        
    $work_phone_number $y['work_phone'];

    And the Parent's address from the Address table (which has the PersonID of the parent):

    PHP Code:
    while($yyy mysql_fetch_array($xxx)) {
        
    $street_one $yyy['Street'];
        
    $street_two $yyy['Street2'];
        
    $city $yyy['City'];
        
    $State $yyy['State_Abbr'];
        
    $zipcode $yyy['Zipcode'];

    And then I just need to select the first and last name of the student that is found from the search for students of parents with a SchoolID of "insert school ID here."

    Clear as mud?

    [EDIT] Just realized you're probably looking for the info that the user will give to the query...if that's the case, the user gives the ChapterID (so the query only finds parents in the Chapter that they use, and the SchoolID of the school they are searching for.
    Last edited by JohnDubya; 02-02-2007 at 08:55 PM.

  • #9
    teh Moderatorinator
    Join Date
    Sep 2004
    Location
    USA
    Posts
    2,472
    Thanks
    4
    Thanked 40 Times in 40 Posts
    I think your first query is OK, just add the address info into it:
    PHP Code:
    $parentQuery "SELECT p.FirstName, p.LastName, p.PersonID, a.address FROM Person AS p, Chapter_Person AS cp, address a WHERE cp.ChapterID = $chapterID AND cp.PersonID = p.PersonID AND p.PersonType = '3' and p.addressID = a.addressID ORDER BY LastName ASC LIMIT 0, 30"
    Fix the address table reference accordingly....then when you loop through that, and run another query for the joined values to the student (note a subquery would be ideal here, you could return the parentID's first then do the other query, but...the query in the loop may look like this, (you should fill in my gaps, may have a table name wrong or something), here's kind of what I'm trying to get at:
    PHP Code:
    // some chapter and school ids
    $chapterID 1;
    $schoolID 1;

    // this is pretty much the query you had except i added in the address...fix those table references as they are probably off
    $parentQuery "SELECT p.FirstName, p.LastName, p.PersonID, a.address FROM Person AS p, Chapter_Person AS cp, address a WHERE cp.ChapterID = $chapterID AND cp.PersonID = p.PersonID AND p.PersonType = '3' and p.addressID = a.addressID ORDER BY LastName ASC LIMIT 0, 30";

    $result mysql_query($parentQuery) or die(mysql_error());
    while(
    $row mysql_fetch_assoc($result))
    {
        
        echo 
    $row['FirstName'] . ' , ' $row['LastName'] . ' - ' $row['address'] . "<BR>";
        
        
    // this sort of looks like the query you already had going on...
        
    $query 'select * from person p, student_parents sp where sp.student_id = p.personID and sp.parent_id = ' $row['PersonID'] . ' and p.schoolID = ' $schoolID;
        
    $result2 mysql_query($query) or die(mysql_error());
        while(
    $row2 mysql_fetch_assoc($result2))
        {
            echo 
    ' --- ' $row2['FirstName'] . ' , ' $row2['LastName'] . "<BR>";
        }

    Obviously you can fix the select *'s with the column names you want etc..this is pretty much psuedo of what i'm trying to say

  • #10
    Super Moderator JohnDubya's Avatar
    Join Date
    Nov 2006
    Location
    Missouri
    Posts
    634
    Thanks
    12
    Thanked 18 Times in 18 Posts
    Thanks for that code. You're helping me understand things better, so thanks!

    Here's the continued problem...if the student is not associated with the schoolID that the user specifies, it needs to also not echo the parent info that the student is associated with.

    In other words, I need to show ONLY the parents of ONLY children who go to school A. If the children go to school B, it shouldn't echo the parent information either.

    Ok, we just got our MySQL upgraded to 4.2, so we can now use subqueries. This will be the first time I've done subqueries, so some pointers would be awesome.

    Again, Brando, thank you so much. The second time you've been extremely helpful!

  • #11
    teh Moderatorinator
    Join Date
    Sep 2004
    Location
    USA
    Posts
    2,472
    Thanks
    4
    Thanked 40 Times in 40 Posts
    Ah good then, that other way is nasty but is pretty much the route you have to take with older versions.

    Give me some more time again...

  • #12
    teh Moderatorinator
    Join Date
    Sep 2004
    Location
    USA
    Posts
    2,472
    Thanks
    4
    Thanked 40 Times in 40 Posts
    Ok, try a query like this, substitute the chapter id and school id, I just put in 1 for each of them:
    Code:
    SELECT parentRec.FirstName, parentRec.LastName, parentRec.address, p.FirstName as 'StudentFirstName', p.LastName as 'StudentLastName' from 
    (
        SELECT 
          p.FirstName, p.LastName, p.PersonID, a.address 
        FROM 
          Person AS p, Chapter_Person AS cp, address a 
        WHERE 
          cp.ChapterID = 1
          AND cp.PersonID = p.PersonID 
          AND p.PersonType = '3' 
          AND p.addressID = a.addressID 
        ORDER BY 
          LastName ASC LIMIT 0, 30
    ) parentRec, person p, student_parents sp 
    WHERE sp.student_id = p.personID AND sp.parent_id = parentRec.PersonID AND p.schoolID = 1

  • #13
    Super Moderator JohnDubya's Avatar
    Join Date
    Nov 2006
    Location
    Missouri
    Posts
    634
    Thanks
    12
    Thanked 18 Times in 18 Posts
    Awesome, that gives me a guide to work with. I'm gonna work with that when I come back on Monday. Thanks so much again, Brando!

  • #14
    Super Moderator JohnDubya's Avatar
    Join Date
    Nov 2006
    Location
    Missouri
    Posts
    634
    Thanks
    12
    Thanked 18 Times in 18 Posts
    Quote Originally Posted by Brandoe85 View Post
    Ok, try a query like this, substitute the chapter id and school id, I just put in 1 for each of them:
    Code:
    SELECT parentRec.FirstName, parentRec.LastName, parentRec.address, p.FirstName as 'StudentFirstName', p.LastName as 'StudentLastName' from 
    (
        SELECT 
          p.FirstName, p.LastName, p.PersonID, a.address 
        FROM 
          Person AS p, Chapter_Person AS cp, address a 
        WHERE 
          cp.ChapterID = 1
          AND cp.PersonID = p.PersonID 
          AND p.PersonType = '3' 
          AND p.addressID = a.addressID 
        ORDER BY 
          LastName ASC LIMIT 0, 30
    ) parentRec, person p, student_parents sp 
    WHERE sp.student_id = p.personID AND sp.parent_id = parentRec.PersonID AND p.schoolID = 1
    *cries* So, I thought I would understand how to do this, but I'm still getting confused. I tried to modify your query, but it wasn't working for me. I tried to write my own with a subquery, but I'm not completely straight on how they work yet. I'm not sure where I should and can use them in queries yet, so I can't get it to work how I want it. The logic I want to use for the query is this:

    SELECT "the parent's information"
    FROM Person AS p, ChapterPerson AS cp, student_parents AS sp
    WHERE cp.ChapterID = 7
    AND cp.PersonID = p.PersonID
    AND sp.parent_id = p.PersonID
    AND p.PersonType = '3'
    AND "the sp.student_id from each selected parent in the sp table" = (any student in the Person table with the PersonID from the sp.student_id whose SchoolID = '82')

    I'm trying to figure out where the select needs to go to find any student_id in the Person table where that row has the SchoolID of 82. I know there is at least one student that fits this description, but I've tried a couple queries based on this logic, and it comes back in phpMyAdmin with an error:

    #1241 - Operand should contain 1 column(s)

    One time, I did the query like this:

    PHP Code:
    $x = @mysql_query("SELECT * 
                FROM Person AS p, ChapterPerson AS cp, student_parents AS sp 
                WHERE cp.ChapterID = '$SelectedChapterID' 
                AND cp.PersonID = p.PersonID 
                AND sp.parent_id = p.PersonID 
                AND p.PersonType = '3' 
                AND sp.student_id = 
                    (SELECT * FROM Person WHERE PersonID = sp.student_id AND SchoolID = '$school_filter')
                ORDER BY LastName ASC 
                LIMIT 0, 30 "
    ); 
    and it pulled the same results as the original $x query...not sure why. Any ideas to try to run through this thick head of mine?

  • #15
    teh Moderatorinator
    Join Date
    Sep 2004
    Location
    USA
    Posts
    2,472
    Thanks
    4
    Thanked 40 Times in 40 Posts
    Did you try to put in my query into phpmyadmin and put in a chapterid and school id?

    I thought it returned something to what you want, but I can't quite remember. The logic in it is, select all the parent id's where the chapter id's match. That should give you the parent query, then from that result set, select all the children where their parent id is equal to the parent id returned from the first query, and where the school id is whatever.

    What results do you get when you run that?


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

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