Why would MySQL leave out the first row when retrieving from a database?

Question

For some reason I'm having a problem retrieving data from my database. It leaves off the first item being listed.

Connection:

mysql_select_db($database_my_db, $my_db);

    $query_rs_jobboards11 = "SELECT * FROM jobBoardsSpecTypes ORDER BY type";
    $rs_jobboards11 = mysql_query($query_rs_jobboards11, $my_db) or die(mysql_error());
    $row_rs_jobboards11 = mysql_fetch_assoc($rs_jobboards11);
    $totalRows_rs_jobboards11 = mysql_num_rows($rs_jobboards11);

Output:

 <form action="" method="get">
        <select name="specialty">

         <?php do { ?>

    <option value="<?php echo $row_rs_cms11['type']; ?>"><?php echo $row_rs_cms11['type']; ?></option>


    <?php } while ($row_rs_cms11 = mysql_fetch_assoc($rs_jobboards11)); ?> 


        </select>

<input type="submit" value="Go" />

</form>

It keeps leaving out the first entry in the type column. If I add more entries, then the one that was left out previously is shown, and the new entry is hidden.

What am I doing wrong? The database works perfectly fine for everything but this page.

thanks

Answer 1

Because you are using do-while instead of while. At your first iteration $row_rs_cms11 is undefined.

Answer 2

You are using $row_rs_cms11 where you really meant $row_rs_jobboards11 ($row_rs_cms11 is not defined the first time around).

    <?php do { ?>

<option value="<?php echo $row_rs_jobboards11['type']; ?>"><?php echo $row_rs_jobboards11['type']; ?></option>


<?php } while ($row_rs_jobboards11 = mysql_fetch_assoc($rs_jobboards11)) ?> 


    </select>

The error would have been more evident if you had used while() instead of do {} while().

If you change the do {} while() to while() as has been suggested, you need to remove the part of your code where you fetch $row_rs_jobboards11 for the first time.

Answer 3

On the very first iteration of your do/while loop, the $row_rs_cms11 is not set. It is set on the next iteration by the while code, but the first time through it is blank. I believe you have simply mis-named a variable.

Change this:

$row_rs_jobboards11 = mysql_fetch_assoc($rs_jobboards11);

To this:

$row_rs_cms11 = mysql_fetch_assoc($rs_jobboards11);

Better yet, don't do that first mysql_fetc_assoc() call at all, and simply change your do/while loop to a while loop only, and the first row will be read there instead.

Answer 4

You already assigned the first row to $row_rs_jobboards11, so when you call $row_rs_cms11 = mysql_fetch_assoc($rs_jobboards11) in your do/while loop, it's starting at the second row in the table.

Using a do/while instead of a while doesn't make any difference, but using different variable names does make a difference.

Update, to explain the difference between while, do/while, and, making sure your variable names are correct.

While loop, which works:

while ($row_rs_cms11 = mysql_fetch_assoc($rs_jobboards11)) {
    echo '<tr>';
    //stuff here
    echo '</tr>';
}

do/while loop, which also works

$row_rs_cms11 = mysql_fetch_assoc($rs_jobboards11);
do {
    echo '<tr>';
    //stuff here
    echo '</tr>';
} while ($row_rs_cms11 = mysql_fetch_assoc($rs_jobboards11));

Note that the $row variable matches. If you leave out the first call, it will still work, but you'll get an extra (empty) before any content is outputted. If you set the variable to a different name (and use a mysql_fetch_* call), the internal database pointer is set to the second row in the table. Consequently, you miss the first row in the table, because it's set to a different (unused) variable.

Moral of the story:

You can use either a while or a do/while loop, you just have to make sure that

• If you are using a do/while, the row variable must be created prior to entering the loop. And,
• Make sure your variable names match!