Need Help With Logic

[spock9458]

I have read and learned a lot in the last couple of weeks, working on my membership directory listings.  I am close to the final solution, but can't seem to work out the right logic for the final product.  Here is what happens: my sample query selects all members from the 'company' table that match a certain county location or alphabet letter, and uses LEFT JOIN to attach all people associated with the company from the 'contact' table.  The results of the first query are then loaded into an array, called $company_array, for processing by "list functions" that I have created for the formatted output on the screen... If all I had were company and contact information, I would be done - but that's not the case.

 

Some (but not all) companies have one or more branch locations associated with them, and each branch location may have one or more people associated with them.  At some point in my listing process, I have to pause and run a second query to find matching entries from the 'branch' table (using a company_id key) and LEFT JOIN to attach all people associated with the branch from the 'contact' table, and load those results into another array, called $branch_array.  I am successful when running the queries, as I have tested a couple of different ways... but I can't get the final result I want, which I'll describe now.

 

The order of events should be: 1) start listing the first company info; 2) following the company info, list all contact info for the people associated with the company;3) check to see if there are one or more branches associated with the company, and if so, list each branch, followed by each contact person associated with the branch;  4) Repeat the process to go through all of the companies found in the first query.

 

My most recent version of the code is as follows:

// 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;
}

$lastCompany = '';
$lastBranch = '';

// Start building the table for showing results
echo "<table border='1' cellpadding='10' cellspacing='0' bgcolor='#f0f0f0' style='margin-left:100px; margin-bottom:20px'>";

// Begin the "foreach" loop for rows in the $company_array

foreach($company_array as $row) {
    // Check if this is a different company than the last one
    if ($lastCompany != $row['company_id']) { 
    // If this is a different company - change the $lastCompany variable   
    $lastCompany = $row['company_id'];
    
    echo "<tr><td><p><b>";
    // List the company info only if it is not the $lastCompany
    listCompany($row);
    }
    
    listContact($row);
    
    }

// Retrieve all the matching data from the "branch" table

$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());

// Check to see if there is a Branch to list
if (mysql_num_rows($result2) != 0); {

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

foreach($branch_array as $row2) {
    if ($lastBranch != $row2['branch_id']) {
	$lastBranch = $row2['branch_id'];
	
	listBranch($row2);
	
    }
    
    listBranchContact($row2);
}

    echo "</td></tr>";
}

echo "</table>";

mysql_close();

The result of running this code can be seen here: http://www.nmlta.org/NewMemDir/testlist2.php which, as you can see, does not get to the branch listing until the last company is listed... I can't seem to figure out where or how to insert my second query, or whether I should use some kind of if/else logic to get it to work the way I need.  Any suggestions will be appreciated... thanks.

[Barand]

What is your table structure?

[Psycho]

Based upon previous posts I cobbled the below code together. You will need to go through and make sure all field names are correct. Also, I added no logic for conditional display of data (e.g. don't display the "Email" label if a contact does not have a label). I'll leave that to you.

 

In the other threads you were advised to NOT run queries in loops. It is a bad practice and you shouldn't start now. You should run only two queries. One to get the company (and company contact) info. And a second to get the branch (and branch contact info). Then put all of that data into a multi-dimensional array. You should also include only the fields you needs as part of the SELECT statement.

 

Once you have that single array - the output becomes elementary. As I stated above, this was just cobbled together so I'm sure there may be a syntax error or two and I probably messed up many of the field names. But, this should solve your problem if you clean it up.

 

 

<?php

// Retrieve ALL the "company" specific data (including contacts)
$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))
{
    if(!isset($company_array[$row['company_id']]))
    {
        //Add the company specific data here
        $company_array[$row['company_id']] = array(
            'name'    => $row['comp_name'],
            'address' => $row['address'],
            'phone'   => $row['phone'],
            'fax'     => $row['fax'],
            'web'     => $row['web'],
            'email'   => $row['email']
        );
    }

    //Add the company contact info here
    $company_array[[$row['company_id']]['contacts'][] = array(
        'name' => $row['contact_name'],
        'title' => $row['title'],
        'email' => $row['email']
    );
}

//Run query to get branch info - for ALL companies
$query = "SELECT branch.*, contact.*
          FROM branch
          JOIN company ON branch.company_id = company.company_id
          LEFT JOIN contact
            ON branch.branch_id = contact.branch_id
          WHERE company.comp_county = 'BERNALILLO'
          ORDER BY branch.br_name, contact.cont_rank";
$result = mysql_query($query) or die(mysql_error());

//Add branch (and branch contacts) results to $company_array
while($row = mysql_fetch_assoc($result))
{
    $compID = $row['company_id'];
    $branchID = $row['branch_id'];
    if(!isset($company_array[]['branches'][$row['branch_id']))
    {
        $company_array[$compID]['branches'][$branchID] = array(
            'branch_name' => $row['branch_name'],
            'address'     => $row['address'],
            'phone'       => $row['phone'],
            'fax'         => $row['fax'],
            'web'         => $row['web'],
            'email'       => $row['email']
        );
    }

    $company_array[$compID]['branches'][$branchID]['contacts'][] = array(
        'name' => $row['contact_name'],
        'title' => $row['title'],
        'email' => $row['email']
    );
}


// Start building the table for showing results
echo "<table border='1' cellpadding='10' cellspacing='0' bgcolor='#f0f0f0' style='margin-left:100px; margin-bottom:20px'>";

// Begin the "foreach" loop for rows in the $company_array

foreach($company_array as $company)
{
    echo "<tr><td>\n";
    echo "<b>{$company['company_name']}</b><br>\n";
    echo "{$company['address']}<br>\n";
    echo "{$company['phone']} Fax: {$company['phone']}><br>\n";
    echo "Email: {$company['email']}<br>\n";
    foreach($company['contacts'] as $contact)
    {
        echo "<b>{$contact['contact_name']}</b>, {$contact['title']}<br>\n";
        echo "Email: {$contact['email']}<br>\n";
    }

    foreach($company['branches'] as $branch)
    {
        echo "<b>Branch {$branch['branch_name']}</b><br>\n";
        echo "{$branch['address']}<br>\n";
        echo "{$branch['phone']} Fax: {$branch['phone']}><br>\n";
        echo "Email: {$branch['email']}<br>\n";
        foreach($branch['contacts'] as $contact)
        {
            echo "<b>{$contact['contact_name']}</b>, {$contact['title']}<br>\n";
            echo "Email: {$contact['email']}<br>\n";
        }
    }
    echo "</td></tr>\n";
}
echo "</table>";

mysql_close();

?>

[Barand]

The structure and processes could be greatly simplified if every company had at least one branch ( where that single branch may be the head office in many instances)

 

Then it's simply

Company
    |
    +----< Branch
             |
             +----< Contact

Edited September 3, 2013 by Barand

[spock9458]

Thank you for the tips - I think I understand enough of your suggestion to give it a try.  I will do so and report whether I can make this work or not.  I appreciate your help!

[spock9458]

OK, this is very close to working, but there are a couple of errors and I can't seem to figure them out.  If you look at the results here: http://www.nmlta.org/NewMemDir/testlist3.php - you can see that I'm getting "invalid argument supplied" for lines 172 and 177 of my code.  The listing of the company information and the contact information is correct, but then it gives the error when trying to list the branch and branch contact info.  Then at the end of all of the company info, all of the branch information is listed together.  The data appears to be all there, but the output is not listed as desired.  Here is the complete code (minus my "list" functions) with the above line numbers indicated:

// 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))
{
    if(!isset($company_array[$row['company_id']]))
    
    {
	// Add company specific data here
	$company_array[$row['company_id']] = array(
	    'comp_name'	  => $row['comp_name'],
	    'comp_uw'	  => $row['comp_uw'],
	    'comp_street' => $row['comp_street'],
	    'comp_csz'	  => $row['comp_csz'],
	    'comp_ph'	  => $row['comp_ph'],
	    'comp_fx'	  => $row['comp_fx'],
	    'comp_email'  => $row['comp_email'],
	    'comp_web'	  => $row['comp_web']
	    );
    }
    
        //Add the company contact info here
        $company_array[$row['company_id']]['contacts'][] = array(
	    'cont_name' => $row['cont_name'],
	    'cont_title' => $row['cont_title'],
	    'cont_email' => $row['cont_email']
	    );
}

//Run query to get branch info - for ALL companies
$query = "SELECT branch.*, contact.*
          FROM branch
          JOIN company ON branch.company_id = company.company_id
          LEFT JOIN contact
            ON branch.branch_id = contact.branch_id
          WHERE company.comp_county = 'BERNALILLO'
          ORDER BY branch.br_name, contact.cont_rank";
	  
$result = mysql_query($query) or die(mysql_error());

//Add branch (and branch contacts) results to $company_array
while($row = mysql_fetch_assoc($result))
{
    $compID = $row['company_id'];
    $branchID = $row['branch_id'];
    if(!isset($company_array['branches'][$row['branch_id']]))
    {
        $company_array[$compID]['branches'][$branchID] = array(
            'br_name'	=> $row['br_name'],
            'br_street'	=> $row['br_street'],
	    'br_csz'	=> $row['br_csz'],
            'br_ph'     => $row['br_ph'],
            'br_fx'     => $row['br_fx'],
            );
    }

    $company_array[$compID]['branches'][$branchID]['contacts'][] = array(
        'cont_name'  => $row['cont_name'],
        'cont_title' => $row['cont_title'],
        'cont_email' => $row['cont_email']
    );
}

// Start building the table for showing results
echo "<table border='1' cellpadding='10' cellspacing='0' bgcolor='#f0f0f0' style='margin-left:100px; margin-bottom:20px'>";

// Begin the "foreach" loop for rows in the $company_array

foreach($company_array as $company)
{
    echo "<tr><td>\n";
    
    listCompany($company);
    
    foreach($company['contacts'] as $contact) #This is line 172
    {
        listContact($contact);
    }

    foreach($company['branches'] as $branch) #This is line 177
    {
        listBranch($branch);
	
        foreach($branch['contacts'] as $contact)
        {
            listBranchContact($contact);
        }
    }
    echo "</td></tr>\n";
}
echo "</table>";

Hopefully someone can point out what the errors are, I have been studying the code for over an hour, and I am stumped.  Thanks.