Need Help With Loop of Results After Join Query

[spock9458]

I am working on an online directory for a state organization.  Members are companies which have contact individuals, and some have branch locations.  I am working on this in steps, so that my questions can be specific.  My database has a "company" table with company name, address, etc., and a "contact" table with names, titles and email addresses of the individuals.  I have been working with a LEFT JOIN query that selects all companies in a particular county from the "company" table, and then finds matching contacts using the company_id field, which produces the intended results - I get a row for each company with the contact person appended, and where there are more than one contact at a company, each row has duplicate company info.

 

My first question is: as I am looping through my results to list (echo) using this: "while($row = mysql_fetch_array( $result ))" I cannot figure out how to list the company information once, but then list only each contact person's info from the rest of the rows for the same company.  If anyone can explain how to do this, I'm sure the same logic will work when I start the code for listing the branch locations.

 

Thanks!

[DavidAM]

Good old-fashioned "control break" processing

 

 

$lastCompany = ''; # Track the "last" company we processed
while ($row = ...) {
  if ($lastCompany != $row['Company']) { 
    // OK we have a new Company to output
    $lastCompany = $row['Company']; # Track the "last" company we processed
    # output company data
  }
  # output Contact Data
}

[spock9458]

OK, let me see if I can put this into operation - thank you.

[Barand]

Good old-fashioned "control break" processing

 

 

That's a term I haven't heard for a long time

[DavidAM]

That's a term I haven't heard for a long time

Yeah, every now and then I like to bring back some of the classic (meaning old) terminology.

 

@spock9458

I should have said, it is better to use something guaranteed to be unique (like the Company ID or primary key), otherwise, you might run into a problem with two companies having the same name in the database.

[spock9458]

OK, step 1 is successful... I have the desired results working with my limited sample database, you could view the listing here: http://www.nmlta.org/NewMemDir/testjoin.php

 

Some of the companies in my directory have branch offices, and the idea is to start listing the branch information immediately after the contact people listed for the main company.  I have a "branch" table with a "company_id" field that ties the branch to the correct company, and in my "contact" table there is a "branch_id" field to tie the contact person to the branch instead of the company record. So step two is to run a second query to select records from the branch table where the branch.company_id = the company_id of the record selected in my first query.  I have used this method of query before, but I think in a previous version of MySQL, and I don't think my syntax for it is quite correct.  Here is what I'm trying to use for my second query:

 

SELECT * FROM branch LEFT JOIN contact ON branch.branch_id = contact.branch_id WHERE branch.company_id = '".$row['company_id']."'

 

The last part is a single quote ' followed by double quotes " then a . and then the field reference from my first query, followed by a . then double quote " then single quote '

Like I said, this syntax worked in a previous edition of my directory, and right now it will run without any error, but it does not appear to be working because there is no data returned.  I would appreciate any help with this, as I am finding it hard to locate a resource online to show the syntax like I'm trying to use.  Thanks.

[spock9458]

OK, you can disregard my last post, I had some other coding problems that were preventing the query from working right.  Now I have worked past those issues, and I think my main query and sub-query are working correctly, but I'm having a major problem with the data output.  You can see what is happening here: http://www.nmlta.org/NewMemDir/testjoin2.php

 

My code is getting a bit complicated, so I am "briefing" it here as much as possible, while still presenting the "logic" here so someone may be able to point out my flaws.  Obviously something is not in the right place, but I have tried several different ways, and I can't get it to come out right.  The intended results should be, within each row of the table output, the main company, the contact people from the main company, the company branch location, the contact people at the branch.  Here are the sections of my code:

 

First section of code – run Main Query and output company data:

 

// MAIN QUERY - Retrieve 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());
// Set to track the "last" company we processed
$lastCompany = '';
// Start building the table for showing results using "while" loop
echo "<table border='1' cellpadding='10' cellspacing='0' bgcolor='#f0f0f0' style='margin-left:100px; margin-bottom:20px'>";
// Here is the "while" loop
while($row = mysql_fetch_array( $result )) {
    if ($lastCompany != $row['comp_name']) {
    // OK we have a new Company to output
      $lastCompany = $row['comp_name']; # Track the "last" company we processed

// Output of company data follows

 

} #ending bracket for $lastCompany "if" statement
      // This is the end of the Company info

 

// Output of contact data follows

 

Second section of code – run Sub-Query and output branch data:

//SUB-QUERY - Retrieve all Branch data from the branch table, matching company_id from MAIN QUERY
$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";
$result2 = mysql_query($query2)
or die(mysql_error());
// Set value for $lastBranch to track the branch in process
$lastBranch = '';
// Start the "while" loop for the sub-query results
while($row2 = mysql_fetch_array( $result2 )) {
    if ($lastBranch != $row2['br_street']) {
    // OK we have a new Branch to output
      $lastBranch = $row2['br_street']; # Track the "last" branch we processed
 

// Output of branch data follows

 

//This is the end of the Branch info
} #ending bracket for "lastBranch" if statement

 

//Output of branch contact data follows

 

} #ending bracket for second "While" loop
     
} #ending bracket for the first "While" loop

// Output of ending cell, row and table tags

 

Like I said earlier in this thread, up until the time I added my Sub-Query everything worked perfectly... and I know the problem is somewhere in my structure, but I just can't seem to figure out how to fix it.  I will greatly appreciate any help with this - Thanks!