Jump to content

Kudos to Psycho - But One Baffling Question Remaining

spock9458

By spock9458
in PHP Coding Help

 Share
Go to solution Solved by kicken,

Recommended Posts

spock9458 Member

spock9458

Posted
Posted

I have to give credit to user "Psycho" here, who came up with the code I'm using for my Membership Directory.  I have even completed all of the code that will allow companies to update their own information in the database when they need to.  The last problem I'm having is with my "master" list of members. It is all working with the exception of - I can't get the County heading to show up when the actual County location in the database record is different from the last company.  I get the heading properly on the first Company, but after that, even though I know the County data is changing, because I'm echoing out the data as it goes (for testing), and I'm just baffled - I have tried every placement of the IF conditional that I can think of.  The current code results can be seen here: http://www.nmlta.org/MemberDirectory-New/all_agent_list-test2.php

 

I'm sure there is a big clue because my flag that says "Checking County Match" is all occurring before the companies even print out, but I can't find the right change to make in my code, which follows:


// Retrieve ALL the "company" specific data (including contacts)
$query = "SELECT company.*,
			co_contact.cont_name,
			co_contact.cont_title,
			co_contact.cont_email
          FROM company
          LEFT JOIN co_contact
            ON company.company_id = co_contact.company_id
          WHERE company.comp_type = 'Agent'
          ORDER BY company.comp_county, company.comp_name, co_contact.cont_rank";

$result = $mysqli->query($query) or die($mysqli->error.__LINE__);

// Build the $company_array
$company_array = array();
while($row = $result->fetch_assoc())
{
    if(!isset($company_array[$row['company_id']]))
    {
        //Add the company specific data here
        $company_array[$row['company_id']] = array(
            'name'    => $row['comp_name'],
            'uw'	=>	$row['comp_uw'],
            'street' => $row['comp_street'],
            'pobox'	=>	$row['comp_pobox'],
            'csz'	=>	$row['comp_csz'],
            'phone'   => $row['comp_ph'],
            'fax'     => $row['comp_fx'],
            'web'     => $row['comp_web'],
            'email'   => $row['comp_email'],
            'county'	=>	$row['comp_county'],
            'branches'		=>	array(),
            'br_contacts'	=>	array()
            );
    }

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

//Run query to get branch info - for ALL companies
$query = "SELECT branch.*, br_contact.cont_name, br_contact.cont_title, br_contact.cont_email
          FROM branch
          JOIN company ON branch.company_id = company.company_id
          LEFT JOIN br_contact
            ON branch.branch_id = br_contact.branch_id
          WHERE company.comp_type = 'Agent'
          ORDER BY company.comp_county, branch.br_street, br_contact.cont_rank";

$result = $mysqli->query($query) or die($mysqli->error.__LINE__);

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

    $company_array[$compID]['branches'][$branchID]['br_contacts'][] = array(
        'name' => $row['cont_name'],
        'title' => $row['cont_title'],
        'email' => $row['cont_email']
    );
}
echo "<span class='heading'>";
echo "2014-15 Agent Members of the New Mexico Land Title Association<br><br>";
echo "</span>";

$current_county = 'xxx';

// Start building the table for showing results
echo "<table border='1' cellpadding='10' cellspacing='0' style='width:750px; margin-left:100px; margin-bottom:20px'>";
// Begin the "foreach" loop to check for County change
foreach($company_array as $company) {
	echo "<font color='red'>Checking County Match</font><br>\n";
	// Enter County IF Statement Here
	if ($company['county'] != $current_county) {
	echo "<tr><td><b><font class='county'>";
	echo $company['county']," County</font><br><hr></b></td></tr>";
	}
		
// Begin the "foreach" loop for rows in the $company_array
foreach($company_array as $company)
{
    echo "<tr><td>\n";
    $current_county = $company['county'];
    echo "This company is in $current_county County<br>\n";
    echo "<b>{$company['name']}</b><br>\n";
    if(!empty($company['uw'])) {
	echo "<b>{$company['uw']}</b><br>\n";}
	if(!empty($company['street'])) {
    echo "{$company['street']}<br>\n";}
    if(!empty($company['pobox'])) {
	echo "{$company['pobox']}<br>\n";}
    echo "{$company['csz']}<br>\n";
    echo "{$company['phone']} Fax: {$company['fax']}<br>\n";
    if(!empty($company['web'])) {
    echo "<a href='http://{$company['web']}' target='_blank'>{$company['web']}</a><br>\n";}
    if(!empty($company['email'])) {
	echo "Email: <a href='mailto:{$company['email']}'>{$company['email']}</a><br>\n";}
	// if(!empty($company['county'])) {
	// echo "Serving <font color='green'>{$company['county']} </font>County<br>\n";}
	echo "<br>";
    
    foreach($company['co_contacts'] as $co_contact)
    {
        if(!empty($co_contact['name'])) {
        echo "&nbsp&nbsp&nbsp <b>{$co_contact['name']}</b>, {$co_contact['title']}<br>\n";}
     	if(!empty($co_contact['email'])) {
		echo "&nbsp&nbsp&nbsp Email: <a href='mailto:{$co_contact['email']}'>{$co_contact['email']}</a><br>\n";}
		echo "<br>";
	}
    
    foreach($company['branches'] as $branch)
    {
        echo "&nbsp&nbsp&nbsp <b>{$branch['branch_name']}</b><br>\n";
        echo "&nbsp&nbsp&nbsp {$branch['street']}<br>\n";
        echo "&nbsp&nbsp&nbsp {$branch['csz']}<br>\n";
        echo "&nbsp&nbsp&nbsp {$branch['phone']} Fax: {$branch['fax']}><br>\n";
        if(!empty($branch['email'])) {
		echo "&nbsp&nbsp&nbsp Email: <a href='mailto:{$branch['email']}'>{$branch['email']}</a><br>\n";}
		echo "<br>";
        foreach($branch['br_contacts'] as $br_contact)
        {
            if(!empty($br_contact['name'])) {			
            echo "&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp <b>{$br_contact['name']}</b>, {$br_contact['title']}<br>\n";}
            if(!empty($br_contact['email'])) {
			echo "&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp Email: <a href='mailto:{$br_contact['email']}'>{$br_contact['email']}</a><br>\n";}
			echo "<br>";
        }
    }
}
    echo "</td></tr>\n";
  
}

echo "</table>";


?>

I will greatly appreciate someone pointing out my mistake, which I'm sure is obvious to code gurus - I am just baffled.  Thanks!

Link to comment
Share on other sites
spock9458 Member

spock9458

Posted
  • Author
Posted (edited)

This is probably a better attempt, it does change the heading, but only one company prints out for each County - it does not print all of the companies in each County:


// Retrieve ALL the "company" specific data (including contacts)
$query = "SELECT company.*,
			co_contact.cont_name,
			co_contact.cont_title,
			co_contact.cont_email
          FROM company
          LEFT JOIN co_contact
            ON company.company_id = co_contact.company_id
          WHERE company.comp_type = 'Agent'
          ORDER BY company.comp_county, company.comp_name, co_contact.cont_rank";

$result = $mysqli->query($query) or die($mysqli->error.__LINE__);

// Build the $company_array
$company_array = array();
while($row = $result->fetch_assoc())
{
    if(!isset($company_array[$row['company_id']]))
    {
        //Add the company specific data here
        $company_array[$row['company_id']] = array(
            'name'    => $row['comp_name'],
            'uw'	=>	$row['comp_uw'],
            'street' => $row['comp_street'],
            'pobox'	=>	$row['comp_pobox'],
            'csz'	=>	$row['comp_csz'],
            'phone'   => $row['comp_ph'],
            'fax'     => $row['comp_fx'],
            'web'     => $row['comp_web'],
            'email'   => $row['comp_email'],
            'county'	=>	$row['comp_county'],
            'branches'		=>	array(),
            'br_contacts'	=>	array()
            );
    }

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

//Run query to get branch info - for ALL companies
$query = "SELECT branch.*, br_contact.cont_name, br_contact.cont_title, br_contact.cont_email
          FROM branch
          JOIN company ON branch.company_id = company.company_id
          LEFT JOIN br_contact
            ON branch.branch_id = br_contact.branch_id
          WHERE company.comp_type = 'Agent'
          ORDER BY company.comp_county, branch.br_street, br_contact.cont_rank";

$result = $mysqli->query($query) or die($mysqli->error.__LINE__);

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

    $company_array[$compID]['branches'][$branchID]['br_contacts'][] = array(
        'name' => $row['cont_name'],
        'title' => $row['cont_title'],
        'email' => $row['cont_email']
    );
}
echo "<span class='heading'>";
echo "2014-15 Agent Members of the New Mexico Land Title Association<br><br>";
echo "</span>";

$current_county = 'xxx';

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

		
// Begin the "foreach" loop for rows in the $company_array
foreach($company_array as $company)
{
	echo "<font color='red'>Checking County Match</font><br>\n";
	// Enter County IF Statement Here
	if ($company['county'] != $current_county) {
	echo "<tr><td><b><font class='county'>";
	echo $company['county']," County</font><br><hr></b></td></tr>";
		

    echo "<tr><td>\n";
    $current_county = $company['county'];
    echo "This company is in $current_county County<br>\n";
    echo "<b>{$company['name']}</b><br>\n";
    if(!empty($company['uw'])) {
	echo "<b>{$company['uw']}</b><br>\n";}
	if(!empty($company['street'])) {
    echo "{$company['street']}<br>\n";}
    if(!empty($company['pobox'])) {
	echo "{$company['pobox']}<br>\n";}
    echo "{$company['csz']}<br>\n";
    echo "{$company['phone']} Fax: {$company['fax']}<br>\n";
    if(!empty($company['web'])) {
    echo "<a href='http://{$company['web']}' target='_blank'>{$company['web']}</a><br>\n";}
    if(!empty($company['email'])) {
	echo "Email: <a href='mailto:{$company['email']}'>{$company['email']}</a><br>\n";}
	// if(!empty($company['county'])) {
	// echo "Serving <font color='green'>{$company['county']} </font>County<br>\n";}
	echo "<br>";
    
    foreach($company['co_contacts'] as $co_contact)
    {
        if(!empty($co_contact['name'])) {
        echo "&nbsp&nbsp&nbsp <b>{$co_contact['name']}</b>, {$co_contact['title']}<br>\n";}
     	if(!empty($co_contact['email'])) {
		echo "&nbsp&nbsp&nbsp Email: <a href='mailto:{$co_contact['email']}'>{$co_contact['email']}</a><br>\n";}
		echo "<br>";
	}
    
    foreach($company['branches'] as $branch)
    {
        echo "&nbsp&nbsp&nbsp <b>{$branch['branch_name']}</b><br>\n";
        echo "&nbsp&nbsp&nbsp {$branch['street']}<br>\n";
        echo "&nbsp&nbsp&nbsp {$branch['csz']}<br>\n";
        echo "&nbsp&nbsp&nbsp {$branch['phone']} Fax: {$branch['fax']}><br>\n";
        if(!empty($branch['email'])) {
		echo "&nbsp&nbsp&nbsp Email: <a href='mailto:{$branch['email']}'>{$branch['email']}</a><br>\n";}
		echo "<br>";
        foreach($branch['br_contacts'] as $br_contact)
        {
            if(!empty($br_contact['name'])) {			
            echo "&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp <b>{$br_contact['name']}</b>, {$br_contact['title']}<br>\n";}
            if(!empty($br_contact['email'])) {
			echo "&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp Email: <a href='mailto:{$br_contact['email']}'>{$br_contact['email']}</a><br>\n";}
			echo "<br>";
        }
    }
}
    echo "</td></tr>\n";

}

echo "</table>";

I know I'm getting close, but I've been trying for the entire weekend, so I'm hoping someone can spot my error quicker... thanks again.

 

Forgot to add - the results of this code are here: http://www.nmlta.org/MemberDirectory-New/all_agent_list-test3.php

Edited by spock9458
Link to comment
Share on other sites
spock9458 Member

spock9458

Posted
  • Author
Posted

I apologize for the three sets of code, but this one is really close... It appears to be checking for the County change at the right moment, and the heading is changed at the right record, but it is still only printing the first company information in each County... you can check the results here: http://www.nmlta.org/MemberDirectory-New/all_agent_list-test4.php

 

And the new code is here:


// Retrieve ALL the "company" specific data (including contacts)
$query = "SELECT company.*,
			co_contact.cont_name,
			co_contact.cont_title,
			co_contact.cont_email
          FROM company
          LEFT JOIN co_contact
            ON company.company_id = co_contact.company_id
          WHERE company.comp_type = 'Agent'
          ORDER BY company.comp_county, company.comp_name, co_contact.cont_rank";

$result = $mysqli->query($query) or die($mysqli->error.__LINE__);

// Build the $company_array
$company_array = array();
while($row = $result->fetch_assoc())
{
    if(!isset($company_array[$row['company_id']]))
    {
        //Add the company specific data here
        $company_array[$row['company_id']] = array(
            'name'    => $row['comp_name'],
            'uw'	=>	$row['comp_uw'],
            'street' => $row['comp_street'],
            'pobox'	=>	$row['comp_pobox'],
            'csz'	=>	$row['comp_csz'],
            'phone'   => $row['comp_ph'],
            'fax'     => $row['comp_fx'],
            'web'     => $row['comp_web'],
            'email'   => $row['comp_email'],
            'county'	=>	$row['comp_county'],
            'branches'		=>	array(),
            'br_contacts'	=>	array()
            );
    }

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

//Run query to get branch info - for ALL companies
$query = "SELECT branch.*, br_contact.cont_name, br_contact.cont_title, br_contact.cont_email
          FROM branch
          JOIN company ON branch.company_id = company.company_id
          LEFT JOIN br_contact
            ON branch.branch_id = br_contact.branch_id
          WHERE company.comp_type = 'Agent'
          ORDER BY company.comp_county, branch.br_street, br_contact.cont_rank";

$result = $mysqli->query($query) or die($mysqli->error.__LINE__);

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

    $company_array[$compID]['branches'][$branchID]['br_contacts'][] = array(
        'name' => $row['cont_name'],
        'title' => $row['cont_title'],
        'email' => $row['cont_email']
    );
}
echo "<span class='heading'>";
echo "2014-15 Agent Members of the New Mexico Land Title Association<br><br>";
echo "</span>";

$current_county = 'xxx';

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

		
// Begin the "foreach" loop for rows in the $company_array
foreach($company_array as $company)
{
	echo "<font color='red'>Checking County Match</font><br>\n";
	// Enter County IF Statement Here
	if ($company['county'] != $current_county) {
	echo "<tr><td><b><font class='county'>";
	echo $company['county']," County</font><br><hr></b></td></tr>";
		

    echo "<tr><td>\n";
    $current_county = $company['county'];
    echo "This company is in $current_county County<br>\n";
    echo "<b>{$company['name']}</b><br>\n";
    if(!empty($company['uw'])) {
	echo "<b>{$company['uw']}</b><br>\n";}
	if(!empty($company['street'])) {
    echo "{$company['street']}<br>\n";}
    if(!empty($company['pobox'])) {
	echo "{$company['pobox']}<br>\n";}
    echo "{$company['csz']}<br>\n";
    echo "{$company['phone']} Fax: {$company['fax']}<br>\n";
    if(!empty($company['web'])) {
    echo "<a href='http://{$company['web']}' target='_blank'>{$company['web']}</a><br>\n";}
    if(!empty($company['email'])) {
	echo "Email: <a href='mailto:{$company['email']}'>{$company['email']}</a><br>\n";}
	// if(!empty($company['county'])) {
	// echo "Serving <font color='green'>{$company['county']} </font>County<br>\n";}
	echo "<br>";
    
    foreach($company['co_contacts'] as $co_contact)
    {
        if(!empty($co_contact['name'])) {
        echo "&nbsp&nbsp&nbsp <b>{$co_contact['name']}</b>, {$co_contact['title']}<br>\n";}
     	if(!empty($co_contact['email'])) {
		echo "&nbsp&nbsp&nbsp Email: <a href='mailto:{$co_contact['email']}'>{$co_contact['email']}</a><br>\n";}
		echo "<br>";
	}
    
    foreach($company['branches'] as $branch)
    {
        echo "&nbsp&nbsp&nbsp <b>{$branch['branch_name']}</b><br>\n";
        echo "&nbsp&nbsp&nbsp {$branch['street']}<br>\n";
        echo "&nbsp&nbsp&nbsp {$branch['csz']}<br>\n";
        echo "&nbsp&nbsp&nbsp {$branch['phone']} Fax: {$branch['fax']}><br>\n";
        if(!empty($branch['email'])) {
		echo "&nbsp&nbsp&nbsp Email: <a href='mailto:{$branch['email']}'>{$branch['email']}</a><br>\n";}
		echo "<br>";
        foreach($branch['br_contacts'] as $br_contact)
        {
            if(!empty($br_contact['name'])) {			
            echo "&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp <b>{$br_contact['name']}</b>, {$br_contact['title']}<br>\n";}
            if(!empty($br_contact['email'])) {
			echo "&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp Email: <a href='mailto:{$br_contact['email']}'>{$br_contact['email']}</a><br>\n";}
			echo "<br>";
        }
    }
}
}
    echo "</td></tr>\n";




echo "</table>";


?>

I think the only change I made was the location of the closing brace(s)... again - I appreciate the help.

Link to comment
Share on other sites
spock9458 Member

spock9458

Posted
  • Author
Posted

I have apparently solved the problem... I have replaced my "if" conditional with an "if/else" conditional.  On the "if" conditional, if the company being processed is in a different County than the last company, the header row of the table is printed, the $current_county variable is updated, and then the company information is listed.  for the "else" conditional, the header is omitted, but I included the entire code again that updates the $current_county variable and lists the company information.  This correct output can now be seen here: http://www.nmlta.org/MemberDirectory-New/all_agent_list-test5.php

 

Unless someone has a shorter, more preferable solution, I'll just go with this one... Thanks again.

Link to comment
Share on other sites
  • Solution
kicken Prolific Member

kicken

Posted
  • Solution
Posted

Unless someone has a shorter, more preferable solution, I'll just go with this one... Thanks again.

You don't have to have an else branch with duplicated code. All you need to do is make the code listing the company details unconditional

foreach($company_array as $company)
{
    if ($company['county'] != $current_county){
    {
       ///print header
       $current_county=$company_county;
    }

    //Print company information
}
Only the header is wrapped in the if condition so it will only print when the county changes. The rest of the code to print the company details is unconditional and executes on each loop.
Link to comment
Share on other sites
spock9458 Member

spock9458

Posted
  • Author
Posted

@kicken - Right on the money!! I thought I had tried that but I must not have had it quite right.  Thanks for sorting it out for me.

 

Bonus Question.... Is there a way to direct the output of my code to a Word doc, so that it keeps my formatting and everything? I have tried "copy and paste" from my browser, but the indentations in the output (which I set using <span> tags) don't stick, along with a couple of other spacing quirks.  I have it looking really good here:

 

http://www.nmlta.org/MemberDirectory-New/all_agent_list1.php

 

Thanks to all for help!

Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.