Dropdown list only appears in the first table row

Question

Here is my query:

$sql = "SELECT * FROM employee";
$query = "SELECT * FROM employeerole ORDER BY role_id";

$result = mysqli_query($link, $sql);
$result1 = mysqli_query($link, $query);
<?php
    while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
    ?>
    <tr>
        <td><?php echo $row["id"]; ?></td>
        <td><?php echo $row["first_name"]; ?></td>
        <td><?php echo $row["last_name"]; ?></td>
        <td><?php echo $row["username"]; ?></td>
        <td><?php echo $row["role"]; ?></td>
        <td>

Here is my code that includes a dropdown box in a table cell however, the data in the dropdown box only appears in the first row of the table (as shown in screenshot below).

Has this got something to do with the loop?

            <form action ="change.php">
                <select id="role" class="" onchange="this.form.submit()">
                    <?php while ($line = mysqli_fetch_array($result1, MYSQL_ASSOC)) { ?>
                    <option value="<?php echo $line['role_id']; ?>"><?php echo $line['role_name']; ?></option>
                    <?php } ?>
                </select>
            </form>
        </td>
        <td><input type="button" onclick="ConfirmDelete()" value="Delete account"</td>
    </tr>
    <?php } ?>

Screen shot of the table:

mysqli_fetch_array reached the end, Next loop it won't have a next. You need to save data to an array and then loop it multiple times. — FirstOne, Dec 28 '15 at 17:46
Possible duplicate of [How to go through mysql result twice?](http://stackoverflow.com/questions/6439230/how-to-go-through-mysql-result-twice) — FirstOne, Dec 28 '15 at 17:48
@FirstOne they're aleady using `MYSQLI_ASSOC` in `mysqli_fetch_array($result, MYSQLI_ASSOC)` I have a feeling they're using a LIMIT that they're not showing. I could be wrong though. — Funk Forty Niner, Dec 28 '15 at 17:50
@Fred-ii- The second code seem to be inside the loop from the top code. This means that it's calling fetch multiple times after it has reach the end, right? — FirstOne, Dec 28 '15 at 17:51
@FirstOne could be. but this is failing `mysqli_fetch_array($result1, MYSQL_ASSOC)` missing the `I`. It should read as `MYSQLI_ASSOC`. OP is mixing APIs and could be the reason. — Funk Forty Niner, Dec 28 '15 at 17:52
Plus, it's hard to say because you're not showing us your full query and if there's a LIMIT in there, or which columns you've chosen. — Funk Forty Niner, Dec 28 '15 at 18:00
@Fred-ii- just edited the question with my full query in it. — Annie Png, Dec 28 '15 at 18:05

Rajdeep Paul · Accepted Answer · 2015-12-28T18:14:01.797

The problem is, the result set $result1 is exhausted in the first row itself. You have to adjust the result pointer to point to the beginning of the result set so that you could iterate through it again. Make use of mysqli_data_seek() function for this.

Add this line mysqli_data_seek($result1, 0); just after this line ..."this.form.submit()"> line.

So your code should be like:

// your code

        <form action ="change.php">
            <select id="role" class="" onchange="this.form.submit()">
                <?php 
                    mysqli_data_seek($result1, 0);
                    while ($line = mysqli_fetch_array($result1, MYSQLI_ASSOC)) { 
                ?>
                <option value="<?php echo $line['role_id']; ?>"><?php echo $line['role_name']; ?></option>
                <?php } ?>
            </select>
        </form>
    </td>
    <td><input type="button" onclick="ConfirmDelete()" value="Delete account"</td>
</tr>
<?php } ?>

// your code

You didn't read the [comments](http://stackoverflow.com/questions/34498160/dropdown-list-only-appears-in-the-first-table-row#comment56738082_34498160), right? ^^ — FirstOne, Dec 28 '15 at 18:12

Dropdown list only appears in the first table row

1 Answers1