Need Help Controlling Output from Query and Sub-Query

[spock9458]

Still working on my membership directory listing, and my brain can't figure how to control this.  My membership consists of company, branch, and contact data in three separate tables.  The main queries that will be used on my website are to search for companies by a county location or alphabetically - no problem, I know how to do all that.  I have gotten some help from this forum with using a JOIN query to "attach" people from the contact table to their appropriate company, or their appropriate branch - the queries are working fine.  Now I'll try to explain my challenge:

 

Step one was to do a company list with contacts, so the query looks like this:

$query = "SELECT * FROM company LEFT JOIN contact ON company.company_id = contact.company_id WHERE comp_county = 'BERNALILLO' ORDER BY company.comp_name, contact.cont_rank";

$result = mysql_query($query)
or die(mysql_error());

 

Then we start the output, using a $lastCompany variable to keep track of the company name:

$lastCompany = '';
while($row = mysql_fetch_array( $result )) {
    if ($lastCompany != $row['comp_name']) {
    // OK we have a new Company to output
    echo "<tr><td><p><b>";
    $lastCompany = $row['comp_name']; # Track the "last" company we processed

    // The Company data is listed next

   } #ending of the "if" statement

   // The data for the contact people is listed next - If the company has more than one contact person, then this repeats for each person

} # ending of the "while" loop

 

This first part works perfectly, as can be seen here: http://www.nmlta.org/NewMemDir/testjoin.php

 

The data in my sample county contains five companies, each with one or more contact people, and this part of the listing works great.  The problem is that some of the companies also have branch offices, which need to be listed immediately after the contact people at the main company - and each branch may have one or more contact people also.  Thus the need for my Sub-Query as follows:

 

$query2 = "SELECT * FROM branch LEFT JOIN contact ON branch.branch_id = contact.branch_id WHERE branch.company_id = '".$row['company_id']."' ORDER BY branch.br_street, contact.cont_rank";

 

I have tried to use a $lastBranch variable, similar to the $lastCompany above, to track and list all of the contact people at a certain branch, and I think this will work, but the problem is I can't seem to structure everything so that the main company and contact data process first, and then the branch and branch-contact data process second, before moving on to the next company from the original query.

 

If someone has an idea how I can control the flow of this data, I am becoming more confused with everything I have tried... nothing seems to work correctly.  For anyone interested, I have attached the complete code for "testjoin2.php" which is my work in progress, which does not work the way I need it to.  Any help will be greatly appreciated - Thanks.

testjoin2.php

[mac_gyver]

your pseudo logic would need to be -

$lastCompany = '';
while($row = mysql_fetch_array( $result )) {
    if ($lastCompany != $row['company_id']) {
        // the company changed, is there a previous company section to close
        if($lastCompany != ''){
            // close out the previous company section here...
            
            // your code to output branch infomration for the previous company, in $lastCompany, would go here...
            
        }
        $lastCompany = $row['company_id']; # Track the "last" company we processed

        // start a new company section here...

    }
    // output the data under each company section here...

}

// close out any previous/last company section here...

i cannot think a a good (clear, straightforward code) way of just joining all the tables in one query, so i would recommend, to avoid running any queries inside of loops, that you pre-proecess the data from the first query (storing it into an array, using the company_id as the main array key.) you can then get all the company_id's out of that array and run one query to get all the branch information at one time. pre-process this branch information and store it into another array using the company_id as the main array key.)

 

in the pseudo code, the while(){} loop would become a foreach(){} loop over the first pre-processed array of data - foreach($company_array as $row) {

 

at the point of needed to loop over the branch information, you would take the correct company_id and use it to test if there is any branch information in the second pre-processed array and then to access and loop over that branch information.

[mac_gyver]

as a continuation of the above reply, since you will be using the code that produces the correctly formatted output in two different places, inside the loop and a final time after the end of the loop, you should probably make it a function. once you have this function, you will notice how similar it is to your main block of code and it would only take a little work to convert it into a recursive function and use it in place of the main block of code.

[spock9458]

Thanks for the tips, mac_gyver.  So, again I am working through step by step, and I have created functions to print out each of my list types: listCompany(), listBranch() and listContact(), and I'm trying to reconstruct my original working code that successfully lists just the company and contact info first.  The code appears to work partially, but I think I have a syntax problem with referring to my field names from the array results. 

 

This line of code creates an array of my query results, called $row, correct?  while($row = mysql_fetch_array( $result )).  So before, in my old loop I just echoed each field using $row['field_name'].  But now using my functions to echo the results, I'm calling listCompany($row), so I think I can't use the $row identifier again in my function code.  Here is the code for my function:

 

function listCompany() {
    echo $row['comp_name'],"<br />";
    if (empty($row['comp_uw'])) {
    // do nothing
    } else {
    echo $row['comp_uw'],"<br />";}
    echo "</b>",$row['comp_street'],"<br />";
    if (empty($row['comp_pobox'])) {
    // do nothing
    } else {
    echo $row['comp_pobox'],"<br />";}
    echo $row['comp_csz'],"<br />";
    echo $row['comp_ph'],"&nbsp&nbsp Fax: ",$row['comp_fx'],"<br />";
    if (empty($row['comp_tfree'])) {
    // do nothing
    } else {
    echo "Toll Free: ",$row['comp_tfree'],"<br />";}
    if (empty($row['comp_email'])) {
    // do nothing
    } else {
    echo "Email: <a href='mailto:",$row['comp_email'],"'>",$row['comp_email'],"</a><br />";}
    if (empty($row['comp_web'])) {
    // do nothing
    } else {
    echo "<a href='http://",$row['comp_web'],"' target='_blank'>",$row['comp_web'],"</a><br />";}
    echo "</p>";

}

 

So how do I change my function code to remove the $row identifier here, since I'm calling it with the function.  Does this make sense?  I'll attach my entire code file in case it helps.  Thanks.

testlist.php

[jcbones]

I bet if you post your database structure, and your two queries (in the MySQL section), those guru's in the MySQL section would be able to give you a very efficient way of handling this all in 1 query.

[spock9458]

Moving along with my project, I am ready to get to this step suggested by mac_gyver above:

 

"to avoid running any queries inside of loops, that you pre-process the data from the first query (storing it into an array, using the company_id as the main array key.) you can then get all the company_id's out of that array and run one query to get all the branch information at one time. pre-process this branch information and store it into another array using the company_id as the main array key."

 

I have successfully stored the results of my first query in a $company_array by the following code:

 

// Retrieve all the data from the "company" table
$query = "SELECT * FROM company LEFT JOIN contact ON company.company_id = contact.company_id WHERE comp_county = 'BERNALILLO' ORDER BY company.comp_name, contact.cont_rank";

$result = mysql_query($query)
or die(mysql_error());

// Build the $company_array
$company_array = array();
while($row = mysql_fetch_assoc($result)) {
    $company_array[] = $row;
}
 

I now have a two-part question: 1) How do I build that array "using the company_id as the main array key"? and 2) How do I "get all the company_id's out of that array and run one query to get all the branch information at one time"?  Remember that not all companies have branches, but the branch table does have a company_id field that matches the company_id field in the company table, whenever there is a branch for the company.  I don't understand how to run the second query so that it goes through each row of the $company_array to look for the matches.  Please help - thanks.

[spock9458]

I have been working on the logic for question 1 in my last post above, and I think I might be on the right track with this:

 

// Retrieve all the data from the "company" table
$query = "SELECT * FROM company LEFT JOIN contact ON company.company_id = contact.company_id WHERE comp_county = 'BERNALILLO' ORDER BY company.comp_name, contact.cont_rank";

$result = mysql_query($query)
or die(mysql_error());

// Build the $company_array
$company_array = array();
while($row = mysql_fetch_assoc($result)) {
    $company_array[] = $row;
}

// Retrieve all the matching data from the "branch" table
foreach($company_array as $row) {

$query2 = "SELECT * FROM branch LEFT JOIN contact ON branch.branch_id = contact.branch_id WHERE branch.company_id = '".$row['company_id']."' ORDER BY branch.br_name, contact.cont_rank";
    
$result2 = mysql_query($query2)
or die(mysql_error());
}

// Build the $branch_array
$branch_array = array();
while($row2 = mysql_fetch_assoc($result2)) {
    $branch_array[] = $row2;
}
 

I can't verify this because something is wrong later in my code, which I will address after I find out if the above logic would appear to be correct for pre-loading the two arrays I need to deal with.  Thanks.