organize MySQL output into discrete sections

[rndilger]

I'm trying to organize MySQL output in an HTML table based on individual states. Here is how the static HTML is currently displayed: http://www.u-s-s-a.org/breeders.html. Here is my working document: http://www.u-s-s-a.org/breeders.php. As you can see on the HTML page, each db row is organized into individual states. I'm only concerned about the US states, and not the countries outside of the US.

 

Following is the code I've been working on (directoryoutput.php):

 

<?php
require '/home/srob/public_html/u-s-s-a.org/config.php';

$connect = mysql_connect ($dbhost, $dbuser, $dbpass) or die ('Cannot connect: '.mysql_error());
mysql_select_db ($dbname);

$query = "SELECT * FROM directory ORDER BY flockname";
$result = mysql_query($query) or die ('Error, query failed.');

echo "<tr><td width='33%' class='style9'><p align='left' class='style12'><a name='AR'></a>Arkansas</p></td><td width='34%'> </td><td width='33%' valign='top'><div 	align='right'><a href='#TOP'><img src='returnTOTOPGRAPHIC/RETURNTOTOP.gif' width='113' height='21' border='0' align='absmiddle'></a></div></td></tr>";

$cnt = 0;

while($row = mysql_fetch_array($result, MYSQL_ASSOC)){
if ($cnt == 0){
	echo "<tr><td valign='top' class='style9'><p align='center'><span class='style10'><strong>";
	if($row['flockname']){ echo $row['flockname']; }
	echo "</strong></span><strong></br>";
	if($row['firstname']){ echo $row['firstname']." ".$row['lastname']."</br>"; }
	if($row['address1']){ echo $row['address1']."</br>"; }
	if($row['address2']){ echo $row['address2']."</br>"; }
	if($row['city']){ echo $row['city'].", ".$row['state']." ".$row['zip']."</br>"; }
	if($row['email1']){ echo "<a href='mailto:".$row['email1']."'>".$row['email1']."</a></br>"; }
	if($row['email2']){ echo "<a href='mailto:".$row['email2']."'>".$row['email2']."</a></br>"; }	
	if($row['website1']){ echo "<a target='_blank' href='http://".$row['website1']."'>".$row['website1']."</a></br>"; }
	if($row['website2']){ echo "<a target='_blank' href='http://".$row['website2']."'>".$row['website2']."</a></br>"; }
	echo "</br></p></td>";
	$cnt++;
} elseif ($cnt == 1){
	echo "<td valign='top' class='style9'><p align='center'><span class='style10'><strong>";
	if($row['flockname']){ echo $row['flockname']; }
	echo "</strong></span><strong></br>";
	if($row['firstname']){ echo $row['firstname']." ".$row['lastname']."</br>"; }
	if($row['address1']){ echo $row['address1']."</br>"; }
	if($row['address2']){ echo $row['address2']."</br>"; }
	if($row['city']){ echo $row['city'].", ".$row['state']." ".$row['zip']."</br>"; }
	if($row['email1']){ echo "<a href='mailto:".$row['email1']."'>".$row['email1']."</a></br>"; }
	if($row['email2']){ echo "<a href='mailto:".$row['email2']."'>".$row['email2']."</a></br>"; }	
	if($row['website1']){ echo "<a target='_blank' href='http://".$row['website1']."'>".$row['website1']."</a></br>"; }
	if($row['website2']){ echo "<a target='_blank' href='http://".$row['website2']."'>".$row['website2']."</a></br>"; }
	echo "</br></p></td>";
	$cnt++;
} elseif ($cnt == 2){
	echo "<td valign='top' class='style9'><p align='center'><span class='style10'><strong>";
	if($row['flockname']){ echo $row['flockname']; }
	echo "</strong></span><strong></br>";
	if($row['firstname']){ echo $row['firstname']." ".$row['lastname']."</br>"; }
	if($row['address1']){ echo $row['address1']."</br>"; }
	if($row['address2']){ echo $row['address2']."</br>"; }
	if($row['city']){ echo $row['city'].", ".$row['state']." ".$row['zip']."</br>"; }
	if($row['email1']){ echo "<a href='mailto:".$row['email1']."'>".$row['email1']."</a></br>"; }
	if($row['email2']){ echo "<a href='mailto:".$row['email2']."'>".$row['email2']."</a></br>"; }	
	if($row['website1']){ echo "<a target='_blank' href='http://".$row['website1']."'>".$row['website1']."</a></br>"; }
	if($row['website2']){ echo "<a target='_blank' href='http://".$row['website2']."'>".$row['website2']."</a></br>"; }
	echo "</br></p></td></tr>";
	$cnt = $cnt - 2;
}
}

mysql_close($connect);

?>

 

I'd appreciate any help on this code, including a more efficient way to parse out the MySQL db rows into 3 columns in the HTML table.

 

Thanks in advance!

 

Ryan

[sasa]

try

<?php
require '/home/srob/public_html/u-s-s-a.org/config.php';
$connect = mysql_connect($dbhost, $dbuser, $dbpass) or die('Cannot connect: ' . mysql_error());
mysql_select_db($dbname);
$query = "SELECT * FROM directory ORDER BY state, flockname"; //add state in order by
$result = mysql_query($query) or die('Error, query failed.');
echo "<tr><td width='33%' class='style9'><p align='left' class='style12'><a name='AR'></a>Arkansas</p></td><td width='34%'> </td><td width='33%' valign='top'><div 	align='right'><a href='#TOP'><img src='returnTOTOPGRAPHIC/RETURNTOTOP.gif' width='113' height='21' border='0' align='absmiddle'></a></div></td></tr>";
$cnt = 0;
$state = ''; //declare state to empty
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
    if ($row['state'] != $state) { //check if is new state
        $state = $row['state'];
        if ($cnt > 0) echo '</tr>'; //check if open table row
        $cnt = 0; // set $cnt to zero and echo table row for new state
        echo '          <tr>
            <td width="33%" class="style9"><p align="left" class="style12">' . $state . '</p>
            <p> </p></td>
            <td width="34%"> </td>
            <td width="33%" valign="top"><div align="right"><a href="#TOP"><img src="returnTOTOPGRAPHIC/RETURNTOTOP.gif" width="113" height="21" border="0" align="absmiddle"></a></div></td>
          </tr>';
    }
    if ($cnt == 0) echo '<tr>';
    echo "<td valign='top' class='style9'><p align='center'><span class='style10'><strong>";
    if ($row['flockname']) echo $row['flockname'];
    echo "</strong></span><strong></br>";
    if ($row['firstname']) echo $row['firstname'] . " " . $row['lastname'] . "</br>";
    if ($row['address1']) echo $row['address1'] . "</br>";
    if ($row['address2']) echo $row['address2'] . "</br>";
    if ($row['city']) echo $row['city'] . ", " . $row['state'] . " " . $row['zip'] . "</br>";
    if ($row['email1']) echo "<a href='mailto:" . $row['email1'] . "'>" . $row['email1'] . "</a></br>";
    if ($row['email2']) echo "<a href='mailto:" . $row['email2'] . "'>" . $row['email2'] . "</a></br>";
    if ($row['website1']) echo "<a target='_blank' href='http://" . $row['website1'] . "'>" . $row['website1'] . "</a></br>";
    if ($row['website2']) echo "<a target='_blank' href='http://" . $row['website2'] . "'>" . $row['website2'] . "</a></br>";
    echo "</br></p></td>";
    $cnt++;
    if ($cnt == 3) {
        $cnt = 0;
        echo '</tr>';
    }
}

mysql_close($connect);
?> 

[rndilger]

Thanks! This code worked perfectly. I very much appreciate the input and would like to point out that my skills improve when I learn from contributions such as this.

 

All the best,

 

Ryan

[rndilger]

One more quick question. In the MySQL db, we used abbreviations for each state. I have an additional db that has the full state listing for each abbreviation. In my output, I'd like to display the full state name and I've already created the array ($state_array) in PHP for these purposes. Using the code provided by sasa, how do I go about identifying the individual state abbreviation and outputting the state name?

[rndilger]

Nevermind, I figured it out using a simpler php array solution.