[SOLVED] need help with an array output seems odd

[stmosaic]

I'm getting strange output with the Cities section of this code. The first item in the array is not displaying, despite the fact that the other items and the <li> are. In fact, the <li> for the missing city is showing up last!  :-\

 

Other than this one issue, the code works fine. I've been working on this since yesterday and am quite frustrated. I'm just not seeing why this is happening. Please, any help would be appreciated.

 

///////PROBLEM AREA MARKED BELOW, concerns a for loop////////

//State  query 
$result = mysql_query("SELECT DISTINCT State FROM $DBTABLE WHERE Active='Y' ORDER BY State ASC");
  while($row = mysql_fetch_array($result))
{
//Echo out each State
 echo "<tr><td valign=\"top\" bgcolor=\"#FDD9BA\"> $row[state]</td></tr><tr><td>";
//City query	 
$sub = mysql_query("SELECT City, CLSfolder FROM $DBTABLE WHERE Active='Y' AND State='{$row['State']}'");     
     while($subrow = mysql_fetch_array($sub))
     {
//Section to echo out each City.
  //set the number of columns and determine the number of rows
   $columns = 3;
   $num_rows = mysql_num_rows($sub);
  if ($num_rows != 0 )
  {
    echo "<TABLE width=\"100%\" border=1 align=\"center\">\n";

    ///////////PROBLEM AREA///////////////

    //this for loop is so we can use the number of rows
    for($i = 0; $i < $num_rows; $i++) {
    $subrow = mysql_fetch_array($sub);
    if($i % $columns == 0) {
        //if there is no remainder, we want to start a new row
        echo "<TR>\n";
    }

echo
"<td width=\"25%\" align=\"left\" valign=\"top\"><a href=\"http://www.domainname.com/$subrow[CLSfolder]\" target=\"_blank\"><font face=\"Verdana\" size=2><li>$subrow[City]</li></font></a><br></td>";
    if(($i % $columns) == ($columns - 1) || ($i + 1) == $num_rows) {
        //if there is a remainder of 1, end the row
        //or if there is nothing left in our result set, end the row
        echo "</TR>\n";
    }
    }

///////////END PROBLEM AREA///////////////

echo "<tr><td colspan=3> </td></tr></TABLE>";
   }
     }
}


mysql_close($dbcnx);

[PFMaBiSmAd]

Your second query does not have an ORDER BY statement so the City names will not be in any particular order.

 

You can also just use a single query -

 

$sub = mysql_query("SELECT City, CLSfolder FROM $DBTABLE WHERE Active='Y' ORDER BY State, City");

 

And then just detect when there is a change in the State value to output a new state heading.

[stmosaic]

PFMaBiSmAd, I understand what you are saying about the order by, but it's not the order by that is the main problem, it's the fact that the first city in the array is not displaying whether I do an order by or not. I've tried it with & without order by.

 

For example, without the for loop for the table columns, the output is DOT Boise, DOT Syracuse. With the for loop the output is DOT Syracuse DOT.

 

No Boise and in a different order despite the fact that neither code has the order by clause.

 

 

[PFMaBiSmAd]

I thought the <li> and the city was showing up last.

 

Anyway, you are using a mysql_fetch_array() in the while() loop and inside of a for() loop, so the first row from the result set is being fetched when the while() loop is evaluated the first time but it is never being used.

 

You should only use the while() loop to fetch data from the result set and use a counter inside of the loop to determine when to start a new <tr>

[stmosaic]

You said "You should only use the while() loop to fetch data from the result set and use a counter inside of the loop to determine when to start a new <tr>"

 

I can see wht you're saying about the evaluation order. I'm a little confused about how to modify this code. Are you saying my WHILE loop should be inside the for loop? Isn't the FOR loop a counter?

 

I appreciate your help!

[PFMaBiSmAd]

Based on what you have posted, I would use the following -

<?php

$last_state = ''; // initialize variable to detect a change in the State. Set to a value that will never exist as data
$columns = 3; // number of columns in a table row
$count = 0; // counter to track current column in the table row

$sub = mysql_query("SELECT City, CLSfolder FROM $DBTABLE WHERE Active='Y' ORDER BY State, City");

echo "<table>\n"; // overall table (whatever you current have...)
while($row = mysql_fetch_array($sub)){
// check for a new State and output the heading when the state changes -
if($last_state != $row['State']){
	// if $count is not 0, finish any previous partial row/section of City data
	if($count != 0){
		// finish the current row
		while($count % $columns != 0){
			echo "<td> </td>";
			$count++;
		}
		// close the row/section
		echo "</tr>\n</table>\n";
	}
	// output the State heading
	echo "<tr><td valign=\"top\" bgcolor=\"#FDD9BA\"> {$row['State']}</td></tr>\n";
	$last_state = $row['State']; // remember the new State
	$count = 0; // reset the count (new set of rows)
}
// at this point, you have city data to display
// if $count == 0, need to start a new section for the city data
if($count == 0){
	echo "<tr><td><TABLE width=\"100%\" border=1 align=\"center\">\n<tr>";
} elseif($count % $columns == 0) {
	// if count is not 0 and $count % $columns == 0, need to finish previous row and start a new one
	echo "</tr>\n<tr>";
}
// display the actual City data -
echo  "<td width=\"25%\" align=\"left\" valign=\"top\"><a href=\"http://www.domainname.com/{$row['CLSfolder']}\" target=\"_blank\"><font face=\"Verdana\" size=2><li>{$row['City']}</li></font></a><br></td>";
$count++; // count one City
} // end of while loop
// if $count is not 0, finish any previous partial row/section of City data
if($count != 0){
while($count % $columns != 0){
	echo "<td> </td>";
	$count++;
}
// close the row/section
echo "</tr>\n</table>";
}
echo "</table>\n"; // close overall table
?>

[PFMaBiSmAd]

Edit to the above: The query would need to select the State column too -

 

$sub = mysql_query("SELECT State, City, CLSfolder FROM $DBTABLE WHERE Active='Y' ORDER BY State, City");

[PFMaBiSmAd]

There is also a missing trailing </td></tr> for the overall table where the row/section of the inner City table is being closed (2 places.)

[bundyxc]

Garbage in, garbage out.

[stmosaic]

PFMaBiSmAd, thank you for your help! Your solution works perfectly. I don't think I would have

worked it out from my previous code and especially not as elegantly. I now see what you meant by a counter. I've not done one of those before, so i see why it confused me. Great job!

Thank you again for you time and patience.