First Row of Array Not Listing Properly

[spock9458]

I have an ongoing mission to tighten up my code for a directory listing for a non-profit organization. Brief background is - there are multiple "company" members in each County, and usually multiple "contact people" at each company. Also, some companies have "branch" offices, but not all, and most of the branches also have "contact people." I have successfully segregated my code, so that the "listing" work is handled by separate functions. I am trying to work with JOIN queries, and in my first step I am treating the "company" and "branch" listings with separate test code for now, and the branch output is missing the first row of the array that I am creating.

 

To demonstrate what is happening, I am going to attache a screen shot of the correct results from my query, which starts with a branch called "Louisiana Branch". To see the results of my code, click on this link: http://www.nmlta.org/NewMemDir/new_br_test2.php

 

And finally, I am including the working portions of my code below... leaving out the connection details, which are not an issue. Just to be clear, I have the exact code structure for a query of the company table, with a similar JOIN, and the listing results of my code do not skip the first row of that array. I must be missing something that should be obvious, but I can't see the problem.  I would appreciate any and all help... thanks - Rob

 

My Code:

<?php

function listCompany($row) {
    echo $row['comp_name'],"<br />";
	if (!empty($row['comp_uw'])) {
	echo $row['comp_uw'],"<br />";}
	echo "</b>",$row['comp_street'],"<br />";
	if (!empty($row['comp_pobox'])) {
	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'])) {
	echo "Toll Free: ",$row['comp_tfree'],"<br />";}
	if (!empty($row['comp_email'])) {
	echo "Email: <a href='mailto:",$row['comp_email'],"'>",$row['comp_email'],"</a><br />";}
	if (!empty($row['comp_web'])) {
	echo "<a href='http://",$row['comp_web'],"' target='_blank'>",$row['comp_web'],"</a><br />";}
	echo "</p>";
}

function listBranch($row2) {
	if (!empty($row2['br_name'])) {
	echo "<p>&nbsp&nbsp&nbsp&nbsp <b>",$row2['branch_name'],"</b><br />";}
	echo "&nbsp&nbsp&nbsp&nbsp ",$row2['br_street'],"<br />";
	echo "&nbsp&nbsp&nbsp&nbsp ",$row2['br_csz'],"<br />";
	echo "&nbsp&nbsp&nbsp&nbsp ",$row2['br_ph'],"&nbsp&nbsp ",$row2['br_fx'],"<br /></p>";
}

function listContact($row) {
    if (!empty($row['cont_name'])) {
	echo "<p>&nbsp&nbsp&nbsp&nbsp <b>",$row['cont_name'],"</b>, ",$row['cont_title'],"<br />";}
	if (!empty($row['cont_email'])) {
	echo "&nbsp&nbsp&nbsp&nbsp Email: <a href='mailto:",$row['cont_email'],"'>",$row['cont_email'],"</a><br />";}
	echo "</p>";
}

function listBranchContact($row2) {
    if (!empty($row2['cont_name'])) {
	echo "<p>&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp <b>",$row2['cont_name'],"</b>, ",$row2['cont_title'],"<br />";}
	if (!empty($row2['cont_email'])) {
	echo "&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp Email: <a href='mailto:",$row2['cont_email'],"'>",$row2['cont_email'],"</a><br />";}
	echo "</p>";
}
// Connection to DB


// Retrieve all the data from the "company" table
$query2 = "SELECT * FROM branch LEFT JOIN br_contact ON branch.branch_id = br_contact.branch_id WHERE br_county = 'BERNALILLO' ORDER BY branch.comp_name, branch.br_street, br_contact.cont_rank"; 

$result2 = $mysqli->query($query2) or die($mysqli->error.__LINE__);
// Build the $company_array
$branch_array = array();
while($row2 = $result2->fetch_assoc()) {
    $branch_array[] = $row2;
}

$lastBranch = ''; //Initialize $lastBranch variable 

// Start building the table to show 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 $branch_array
foreach($branch_array as $row2) { // Branch Loop
   // Check if this is a different branch than the last one
    if ($lastBranch != $row2['branch_id']) { // Loop to check if New Branch
    // If this is a different branch - change the $lastBranch variable   
    $lastBranch = $row2['branch_id'];
    
    echo "<tr><td>";
    // List the branch info only if it is not the $lastBranch
    listBranch($row2);
	
    } // End check if New Company loop
    
	// List all Contacts in the Branch
	
	listBranchContact($row2);
		
	} // End Branch Loop
	
	echo "</td></tr>";

// Free result set
mysqli_free_result($result2);

echo "</table>";

mysqli_close($mysqli);

?>

[ginerjm]

When I see a post mentioning "losing the first row" I immediately see someone with a fetch followed by a loop doing more fetches.  That first fetch is killing you!  So - check how you process all your query results until you find that early-bird fetch.

[spock9458]

OK, I really need to understand this, and I apologize in advance for my ignorance.  In my code I have:

$branch_array = array();

while ($row2 = $result2->fetch_assoc()) {
     $branch_array[] = $row2;
     }

This "while loop" should load all rows from my result into the array $branch_array, I think.  Then right after that I start the "foreach loop" to iterate through each row.  Are you saying I should run the "while loop" after the "foreach loop"?  I understand I'm doing it incorrectly, but I don't get how to change it.  I really need some help, maybe a sample, so I can understand.  Thanks.

[CroNiX]

You don't need a loop at all.  Just grab all results.

http://php.net/manual/en/mysqli-result.fetch-all.php

[mac_gyver]

are you 100% sure that the fetched array contains this 'missing' row? 

 

there are two possibilities -

 

1) you query is not matching that row, possibly because the where clause is false

 

2) your php code, doing things like testing for empty() values is skipping the display of that row, because an element of that row or of the join with the contact information resulted in empty or null values.

 

if that row is in your fetched array, what does using var_dump() on that one row show?

[spock9458]

Changed my code a bit, to try and make use of the mysqli_fetch_all() function, but the following code results in a fatal error: "Fatal error: Call to undefined function mysqli_fetch_all()"

// Retrieve all the data from the "branch" table
$query2 = "SELECT * FROM branch LEFT JOIN br_contact ON branch.branch_id = br_contact.branch_id WHERE br_county = 'BERNALILLO' ORDER BY branch.comp_name, branch.br_street, br_contact.cont_rank"; 

$result2 = $mysqli->query($query2) or die($mysqli->error.__LINE__);
// Fetch the branch array

$rows = mysqli_fetch_all($result2, MYSQLI_ASSOC);

$num_rows = mysqli_num_rows($result2);

	echo "There are $num_rows rows in the array.<br />";

$lastBranch = ''; //Initialize $lastBranch variable 

// Start building the table to show 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 $branch_array
foreach($rows as $row2) { // Branch Loop
   // Check if this is a different branch than the last one
    if ($lastBranch != $row2['branch_id']) { // Loop to check if New Branch
    // If this is a different branch - change the $lastBranch variable   
    $lastBranch = $row2['branch_id'];
    
    echo "<tr><td>";
    // List the branch info only if it is not the $lastBranch
    listBranch($row2);
	
    } // End check if New Company loop
    
	// List all Contacts in the Branch
	
	listBranchContact($row2);
	
	
	} // End Branch Loop

I'm trying to find other help using Google, but still need assistance - I really appreciate the help.  Rob

[mikosiko]

$rows = $result2->fetch_all(MYSQL_ASSOC);

but that IS NOT what is causing your issue ... Mac_Gyver already told you the possibles causes... you best course of action is DEBUG your code... a simple var_dump($rows) ... or var_dump($branch_array), according to your original code, should give you the starting point to analyze further.

[mac_gyver]

the reason for the error on the mysqli_fetch_all() is because it's another one of php's screw-ups that is only present in a specific case that you won't generally have any control over.

[ginerjm]

You had the right idea to start with -just bad coding.  When you get your query results simply use them to process your data instead of moving them to a local array as you did and then loop thru that.

 

 

while ($row = $qrslts->fetch())
{
  (process each row)
}

 

No need for the branch_array at all - you already have an array of sorts from the query (actually it's called a 'resource') that you can loop thru with the fetch call.

 

Now - if you are not already doing a fetch before all of your loops then my initial guess was incorrect.  At this point Mac_gyver may have given you your answer in that your own code is not processing that first row. 

 

Change your code to do just ONE loop using fetch() and let's see the new code.

[spock9458]

The var_dump($branch_array) pointed out the flaw - in the first row of the results the 'branch_id' field showed up as NULL, even though I can see a number there in the database (??)  Anyway, the way my foreach loop works, if the $lastBranch variable matches the 'branch_id' in the row, the branch name and address are skipped.  When I changed my initialization to 'xxx' instead of '' at the beginning, now the missing branch shows up.  I'm going to try and clean up the code a bit more, and then move on to my next step of combining the company and branch listings.  I'm sure I'll run into difficulties, but if so I will start a new thread.  Thanks for all the great help!!

[spock9458]

OK, I think I still have a problem with the code after trying to bypass loading the $branch_array.  The following code produces this error: "
Warning: mysqli_result::fetch_assoc() [mysqli-result.fetch-assoc]: Couldn't fetch mysqli_result"

// Retrieve all the data from the "branch" table
$query2 = "SELECT * FROM branch LEFT JOIN br_contact ON branch.branch_id = br_contact.branch_id WHERE br_county = 'BERNALILLO' ORDER BY branch.comp_name, branch.br_street, br_contact.cont_rank"; 

$result2 = $mysqli->query($query2) or die($mysqli->error.__LINE__);
// Fetch the branch array

$branch_array = array();

while($rows = $result2->fetch_assoc()) {

$lastBranch = 'xxx'; //Initialize $lastBranch variable 

// Start building the table to show 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 $branch_array
foreach($rows as $row2) { // Branch Loop
   // Check if this is a different branch than the last one
    if ($lastBranch != $row2['branch_id']) { // Loop to check if New Branch
    // If this is a different branch - change the $lastBranch variable   
    $lastBranch = $row2['branch_id'];
    
    echo "<tr><td>";
    // List the branch info only if it is not the $lastBranch
    listBranch($row2);
	
    } // End check if New Company loop
    
	// List all Contacts in the Branch
	
	listBranchContact($row2);
	
	
	} // End Branch Loop
	
	
	echo "</td></tr>";

// Free result set
mysqli_free_result($result2);
}
echo "</table>";

mysqli_close($mysqli);

[ginerjm]

fetch_assoc returns an array of ONE row.  This is how you process query results - grab a row and process it, then loop thru and thru until all rows are handled.

 

Other things - you don't want to start a table inside the while loop, do you?  An html table for each row of the results?

Also - you don't want to set lastbranch to 'xxx' inside the loop either.

[spock9458]

Yes!! Thank you all - @ginerjm I will mark yours as Best Answer, even though others were very helpful.  I appreciate it!

[spock9458]

Here is the working code:

// Retrieve all the data from the "branch" table
$query2 = "SELECT * FROM branch LEFT JOIN br_contact ON branch.branch_id = br_contact.branch_id WHERE br_county = 'BERNALILLO' ORDER BY branch.comp_name, branch.br_street, br_contact.cont_rank"; 

$result2 = $mysqli->query($query2) or die($mysqli->error.__LINE__);
// Fetch the branch array

$lastBranch = 'xxx'; //Initialize $lastBranch variable 

// Start building the table to show 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 $branch_array
while($row2 = $result2->fetch_assoc()) {
   // Check if this is a different branch than the last one
    if ($lastBranch != $row2['branch_id']) { // Loop to check if New Branch
    // If this is a different branch - change the $lastBranch variable   
    $lastBranch = $row2['branch_id'];
    
    echo "<tr><td>";
    // List the branch info only if it is not the $lastBranch
    listBranch($row2);
	
    } // End check if New Company loop
    
	// List all Contacts in the Branch
	
	listBranchContact($row2);
	
	
	} // End Branch Loop
	
	
	echo "</td></tr>";

// Free result set
mysqli_free_result($result2);

echo "</table>";

[ginerjm]

HTH!!

 

Although - I'm not sure it's working correctly since I see a single tr / td start tag inside the loop and then a tr / tr end tag after the loop.  Where are the other row and element tags created?  Makes for some hard reading - especially when you come back next month and want to remember how it works.  Does the listBranch function called from the loop finish the started td element and row element?  Does the listbranchcontact function create its own row element completely?  Might be better to include that in each of the functions so the reader can make better sense.