Specific loops through SQL data?

Apologies if this has been asked before, but since I’m an extreme novice, I don’t know what I’m doing. I have googled my problem but I don’t understand how to implement anything in regards to my specific needs. Plus it is for work, so it is time-sensitive.

I have a database table with a list of members of Congress. There are fields for their name as well as what state they represent. What I want is to list all the people under their corresponding state, and I tried to do this using repeating regions (I am working in Dreamweaver CS5). Here is the MySQL:

mysql_select_db($database_getcaughtreading, $getcaughtreading); $query_rsPosters = "SELECT posters.firstname, posters.lastname, posters.image, posters.reading, posters.type, posters.`state` FROM posters WHERE posters.reading = '1' AND posters.type = 'congress' ORDER BY posters.lastname ASC"; $rsPosters = mysql_query($query_rsPosters, $getcaughtreading) or die(mysql_error()); $row_rsPosters = mysql_fetch_assoc($rsPosters); $totalRows_rsPosters = mysql_num_rows($rsPosters); ?>
And here is the code I implemented thus far:

<h3>Alabama</h3> <ul> <?php do { ?> <?php if ($row_rsPosters['state']=="Alabama") { ?> <li><a href="images/posters-congress/<?php echo $row_rsPosters['image']; ?>" target="_blank"><?php if ($row_rsPosters['firstname']<>"") { ?> <?php echo $row_rsPosters['firstname']; ?> <? }?> <?php echo $row_rsPosters['lastname']; ?></a></li><? } ?> <?php } while ($row_rsPosters = mysql_fetch_assoc($rsPosters)); ?> </ul>
That code works—but only for one state. For every state after that, no data appears. Just the h3 tag with the state name, which I entered manually and is not part of the repeating region. I tried some other loops (in place of the if) but the results have either been infinite data displaying, showing every state’s result…basically just not working. Can someone please help me? Thank you!

This:

while ($row_rsPosters = mysql_fetch_assoc($rsPosters));

is fetching one row at a time from the last query until none are left to be fetched. Then when it goes to the next loop (the next state state) all the records have already been fetched by the first loop so it has nothing to do and skips.

You could instead store the rows in an array and the loop through the array each time.

Something like this (untested code):

<?php

$rows = array();
while ($row_rsPosters = mysql_fetch_assoc($rsPosters))
	$rows[] = $row_rsPosters;
?>

<h3>Alabama</h3>
    <ul>
      <?php foreach($rows as $row_rsPosters) { ?>
       <?php if ($row_rsPosters['state']=="Alabama") { ?>
        <li><a href="images/posters-congress/<?php echo $row_rsPosters['image']; ?>" target="_blank"><?php if ($row_rsPosters['firstname']<>"") { ?>
          <?php echo $row_rsPosters['firstname']; ?>
          <? }?>
          <?php echo $row_rsPosters['lastname']; ?></a></li><? } ?>
        <?php } ?>
    </ul>

Thank you so so much! That code works.

Sponsor our Newsletter | Privacy Policy | Terms of Service