spock9458 Posted September 15, 2014 Share Posted September 15, 2014 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 "    <b>{$co_contact['name']}</b>, {$co_contact['title']}<br>\n";} if(!empty($co_contact['email'])) { echo "    Email: <a href='mailto:{$co_contact['email']}'>{$co_contact['email']}</a><br>\n";} echo "<br>"; } foreach($company['branches'] as $branch) { echo "    <b>{$branch['branch_name']}</b><br>\n"; echo "    {$branch['street']}<br>\n"; echo "    {$branch['csz']}<br>\n"; echo "    {$branch['phone']} Fax: {$branch['fax']}><br>\n"; if(!empty($branch['email'])) { echo "    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 "        <b>{$br_contact['name']}</b>, {$br_contact['title']}<br>\n";} if(!empty($br_contact['email'])) { echo "        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! Quote Link to comment https://forums.phpfreaks.com/topic/291081-kudos-to-psycho-but-one-baffling-question-remaining/ Share on other sites More sharing options...
spock9458 Posted September 15, 2014 Author Share Posted September 15, 2014 (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 "    <b>{$co_contact['name']}</b>, {$co_contact['title']}<br>\n";} if(!empty($co_contact['email'])) { echo "    Email: <a href='mailto:{$co_contact['email']}'>{$co_contact['email']}</a><br>\n";} echo "<br>"; } foreach($company['branches'] as $branch) { echo "    <b>{$branch['branch_name']}</b><br>\n"; echo "    {$branch['street']}<br>\n"; echo "    {$branch['csz']}<br>\n"; echo "    {$branch['phone']} Fax: {$branch['fax']}><br>\n"; if(!empty($branch['email'])) { echo "    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 "        <b>{$br_contact['name']}</b>, {$br_contact['title']}<br>\n";} if(!empty($br_contact['email'])) { echo "        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 September 15, 2014 by spock9458 Quote Link to comment https://forums.phpfreaks.com/topic/291081-kudos-to-psycho-but-one-baffling-question-remaining/#findComment-1491168 Share on other sites More sharing options...
spock9458 Posted September 15, 2014 Author Share Posted September 15, 2014 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 "    <b>{$co_contact['name']}</b>, {$co_contact['title']}<br>\n";} if(!empty($co_contact['email'])) { echo "    Email: <a href='mailto:{$co_contact['email']}'>{$co_contact['email']}</a><br>\n";} echo "<br>"; } foreach($company['branches'] as $branch) { echo "    <b>{$branch['branch_name']}</b><br>\n"; echo "    {$branch['street']}<br>\n"; echo "    {$branch['csz']}<br>\n"; echo "    {$branch['phone']} Fax: {$branch['fax']}><br>\n"; if(!empty($branch['email'])) { echo "    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 "        <b>{$br_contact['name']}</b>, {$br_contact['title']}<br>\n";} if(!empty($br_contact['email'])) { echo "        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. Quote Link to comment https://forums.phpfreaks.com/topic/291081-kudos-to-psycho-but-one-baffling-question-remaining/#findComment-1491171 Share on other sites More sharing options...
spock9458 Posted September 15, 2014 Author Share Posted September 15, 2014 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. Quote Link to comment https://forums.phpfreaks.com/topic/291081-kudos-to-psycho-but-one-baffling-question-remaining/#findComment-1491190 Share on other sites More sharing options...
Solution kicken Posted September 15, 2014 Solution Share Posted September 15, 2014 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. Quote Link to comment https://forums.phpfreaks.com/topic/291081-kudos-to-psycho-but-one-baffling-question-remaining/#findComment-1491193 Share on other sites More sharing options...
spock9458 Posted September 15, 2014 Author Share Posted September 15, 2014 @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! Quote Link to comment https://forums.phpfreaks.com/topic/291081-kudos-to-psycho-but-one-baffling-question-remaining/#findComment-1491201 Share on other sites More sharing options...
Barand Posted September 15, 2014 Share Posted September 15, 2014 Writing the output to a .html file then opening that file in Word should work Quote Link to comment https://forums.phpfreaks.com/topic/291081-kudos-to-psycho-but-one-baffling-question-remaining/#findComment-1491205 Share on other sites More sharing options...
spock9458 Posted September 15, 2014 Author Share Posted September 15, 2014 @Barand - That works! You have the best answer for Bonus Question... Quote Link to comment https://forums.phpfreaks.com/topic/291081-kudos-to-psycho-but-one-baffling-question-remaining/#findComment-1491214 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.