Only one of my SQL dynamic drop downs works per page?

Question

I have a webpage (in php) where the first section of PHP code gets the relevant records.

<?php
    session_start();

    require_once('Connections/default.php');

    if (isset($_POST['command'])){
        $_SESSION['Username'] = '';
        $_SESSION['Password'] = '';
        session_destroy();
    } else {
    }

    if (is_null($_POST['Username'])){
    } else {
        $_SESSION['Username'] = $_POST['Username'];
    }

    if (is_null($_POST['Password'])){
    } else {
        $_SESSION['Password'] = md5($_POST['Password']);
    }

    $sql = "SELECT * FROM Users WHERE Username LIKE '".$_SESSION['Username']."'     AND Password LIKE '".$_SESSION['Password']."'";
    $newdefectquery = "SELECT * FROM Defects WHERE Defect_Status LIKE 'New'";
    $selectallusers = "SELECT * FROM Users";

    $result = $conn->query($sql);
    $defects_new = $conn->query($newdefectquery);
    $allusers = $conn->query($selectallusers);
?>

This section of the page works correctly and pulls the results (I can see other data on the page.) I then have the following section of code, which contains a drop down menu of all the site's users (So I can assign defects to each user), but only one of the dynamic drop down menus works? It displays every user. (Where I have more than 1 defect, I would like to be able to assign any of them.)

<?php if ($defects_new->num_rows > 0){
    while ($defect = $defects_new->fetch_assoc()){ ?>
        <table border="1" width="90%">
            <tr>
                <td colspan="2">
                    <h2 style="margin:0px; font-size:25px; line-height:25px;">#<?php echo $defect['Defect_ID'] ?> - <?php echo $defect['Title'] ?> - <?php echo $defect['Found_By'] ?></h2>
                </td>
            </tr>
            <tr>
                <td width="80%">
                    <?php echo $defect['Information'] ?>
                    <br />
                    <br />
                </td>
                <td>
                    <form name="assign<?php echo  $defect['Defect_ID'] ?>" method="post" action="index.php">
                        <select name="Owner">
                        <?php while ($users = $allusers->fetch_assoc()){ ?>
                            <option value="<?php echo $users['Username']?>"><?php echo $users['Username'] ?></option>
                        <?php } ?>
                        </select>
                    </form>
                </td>
                </tr>
            </table>
            <br />
            <br />
<?php } 
} else {
    echo "There are no new defects. Good job! :)";
}
?>

Here's a picture of what I see. (I've expanded the working drop down)

The second drop down does not expand. It also does not show any options on inspect element.

Please help. Thanks.

If you see yourself coding an **empty** IF and using the ELSE just reverse the test in the IF and use just the IF — RiggsFolly, May 23 '17 at 14:11
Store the result of the USER dropdown in a variable. Then re-use the variable as often as you like. Reason you have a problem..... Once you have processed a resultset it is empty. Process it once, save the results in a variable, use that variable as often as you like in your code — RiggsFolly, May 23 '17 at 14:15
MD5 is not sufficient for password hashing. Use [`password_hash()`](http://us3.php.net/manual/en/function.password-hash.php) and [`password_verify()`](http://us3.php.net/manual/en/function.password-verify.php) instead. — Alex Howansky, May 23 '17 at 14:58
Also, don't use `LIKE` for queries where you want an exact match, use `=`. — Alex Howansky, May 23 '17 at 15:00
Your code is vulnerable to [**SQL injection**](https://en.wikipedia.org/wiki/SQL_injection) attacks. You should use [**mysqli**](https://secure.php.net/manual/en/mysqli.prepare.php) or [**PDO**](https://secure.php.net/manual/en/pdo.prepared-statements.php) prepared statements with bound parameters as described in [**this post**](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). — Alex Howansky, May 23 '17 at 15:00

score 0 · Accepted Answer · answered May 23 '17 at 14:29

<?php if ($defects_new->num_rows > 0){

$users = $allusers->fetch_assoc();

while ($defect = $defects_new->fetch_assoc()){ ?>
    <table border="1" width="90%">
        <tr>
            <td colspan="2">
                <h2 style="margin:0px; font-size:25px; line-height:25px;">#<?php echo $defect['Defect_ID'] ?> - <?php echo $defect['Title'] ?> - <?php echo $defect['Found_By'] ?></h2>
            </td>
        </tr>
        <tr>
            <td width="80%">
                <?php echo $defect['Information'] ?>
                <br />
                <br />
            </td>
            <td>
                <form name="assign<?php echo  $defect['Defect_ID'] ?>" method="post" action="index.php">
                    <select name="Owner">
                    <?php foreach($users as $user){ ?>
                        <option value="<?php echo $user['Username']?>"><?php echo $user['Username'] ?></option>
                    <?php } ?>
                    </select>
                </form>
            </td>
            </tr>
        </table>
        <br />
        <br />
<?php } 
} else {
    echo "There are no new defects. Good job! :)";
}
 ?>

Paras Pitroda · Answer 2 · 2017-05-23T14:57:44.627

remove user query execution part from first code part:

<?php
    session_start();

    require_once('Connections/default.php');

    if (isset($_POST['command'])){
        $_SESSION['Username'] = '';
        $_SESSION['Password'] = '';
        session_destroy();
    } else {
    }

    if (is_null($_POST['Username'])){
    } else {
        $_SESSION['Username'] = $_POST['Username'];
    }

    if (is_null($_POST['Password'])){
    } else {
        $_SESSION['Password'] = md5($_POST['Password']);
    }

    $sql = "SELECT * FROM Users WHERE Username LIKE '".$_SESSION['Username']."'     AND Password LIKE '".$_SESSION['Password']."'";
    $newdefectquery = "SELECT * FROM Defects WHERE Defect_Status LIKE 'New'";
    $selectallusers = "SELECT * FROM Users";

    $result = $conn->query($sql);
    $defects_new = $conn->query($newdefectquery);
    //$allusers = $conn->query($selectallusers);
?>

run query before you popup data:

<?php if ($defects_new->num_rows > 0){



while ($defect = $defects_new->fetch_assoc()){ ?>
    <table border="1" width="90%">
        <tr>
            <td colspan="2">
                <h2 style="margin:0px; font-size:25px; line-height:25px;">#<?php echo $defect['Defect_ID'] ?> - <?php echo $defect['Title'] ?> - <?php echo $defect['Found_By'] ?></h2>
            </td>
        </tr>
        <tr>
            <td width="80%">
                <?php echo $defect['Information'] ?>
                <br />
                <br />
            </td>
            <td>
                <form name="assign<?php echo  $defect['Defect_ID'] ?>" method="post" action="index.php">
                    <select name="Owner">
                    <?php $allusers = $conn->query($selectallusers); 
                          $users = $allusers->fetch_assoc();//here
                         foreach($users as $user){ ?>
                        <option value="<?php echo $user['Username']?>"><?php echo $user['Username'] ?></option>
                    <?php } ?>
                    </select>
                </form>
            </td>
            </tr>
        </table>
        <br />
        <br />
<?php } 
} else {
    echo "There are no new defects. Good job! :)";
}
 ?>

once query list all record, it will return null as there is nothing to show. You can get all user list in array in your first code part and you can loop that user array so it will execute one time.

if you move the query like this it will do a database query for every loop in the while loop that is unnecessary database querys casuing performance issues — Anthony, May 23 '17 at 14:53
Yes and thats why i also mentioned to use array in my last line — Paras Pitroda, May 23 '17 at 14:55

Only one of my SQL dynamic drop downs works per page?

2 Answers2