[RESOLVED] Anyone got a better Solution

[Nvenom]

Working on a website for a patch collector and i have the navigation for the states blank out when there is no patches in the mysql DB the way i have it set up just seems really slow.

<?php $rc = mysql_query("SELECT * FROM patch_table WHERE p_state = 'AL'"); $rc_rows = mysql_num_rows($rc); if ($rc_rows > 0) {?>
<a class="statelink" href="show.php?loc=AL" >Alabama</a><br/><?php } else { ?><a style="color: #c3c3c3;" >Alabama</a><br/><?php } ?>
<?php $rc = mysql_query("SELECT * FROM patch_table WHERE p_state = 'AK'"); $rc_rows = mysql_num_rows($rc); if ($rc_rows > 0) {?>
<a class="statelink" href="show.php?loc=AK" >Alaska</a><br/><?php } else { ?><a style="color: #c3c3c3;" >Alaska</a><br/><?php } ?>
<?php $rc = mysql_query("SELECT * FROM patch_table WHERE p_state = 'AZ'"); $rc_rows = mysql_num_rows($rc); if ($rc_rows > 0) {?>
<a class="statelink" href="show.php?loc=AZ" >Arizona</a><br/><?php } else { ?><a style="color: #c3c3c3;" >Arizona</a><br/><?php } ?>
<?php $rc = mysql_query("SELECT * FROM patch_table WHERE p_state = 'AR'"); $rc_rows = mysql_num_rows($rc); if ($rc_rows > 0) {?>
<a class="statelink" href="show.php?loc=AR" >Arkansas</a><br/><?php } else { ?><a style="color: #c3c3c3;" >Arkansas</a><br/><?php } ?>
<?php $rc = mysql_query("SELECT * FROM patch_table WHERE p_state = 'CA'"); $rc_rows = mysql_num_rows($rc); if ($rc_rows > 0) {?>
<a class="statelink" href="show.php?loc=CA" >California</a><br/><?php } else { ?><a style="color: #c3c3c3;" >California</a><br/><?php } ?>
<?php $rc = mysql_query("SELECT * FROM patch_table WHERE p_state = 'CO'"); $rc_rows = mysql_num_rows($rc); if ($rc_rows > 0) {?>
<a class="statelink" href="show.php?loc=CO" >Colorado</a><br/><?php } else { ?><a style="color: #c3c3c3;" >Colorado</a><br/><?php }?>

goes on like that for quite a while. Anyone got a shorter way of doing it?

[deller]

does this help any?

<?php
    $stateAbbrs=array('AL','AZ',....etc);
    $states=array('Alabama','Arizona',....etc);
    for($i=0; $i<sizeof($states);$i++)
    {
        $result = mysql_query("SELECT * FROM patch_table WHERE p_state='".$stateAbbrs[$i]."'");
        if(mysql_num_rows($result)>0)
        {
            echo '<a class="statelink" href="show.php?loc='.$stateAbbrs[$i].'" >'.$states[$i].'</a><br/>';
        }
        else
        {
            echo '<a style="color: #c3c3c3;" >'.$states[$i].'</a><br/>';
        }
    }
}
?>

[NOT TESTED]

[Nvenom]

Worked perfectly, Thanks.

[NoasIT]

Just personal preferance but I would have done it as such:

$states = array( 'AL' => 'Alabama','AK' => 'Arkansas',...);
foreach( $states as $abbr => $name ) {
   $result = mysql_query("SELECT * FROM patch_table WHERE p_state='".$abbr."'");
   if(mysql_num_rows($result)>0) {
      echo '<a class="statelink" href="show.php?loc='.$abbr.'" >'.$name.'</a><br/>';
   } else {
      echo '<a style="color: #c3c3c3;" >'.$name.'</a><br/>';
   }
} 

[Nvenom]

Great Solution, Most likely going to use Yours. Thanks