While loop not outputting data from database..

[WebDevGuy32145]

Hey everyone,

So for some reason my while loop wont populate my select/option field with data in my database. I'm trying to get the values stored in database to show as well as return already submitted data. Here is where my while loop is:

<select class="form-control" name="permissions">
          <option value=""<?=(($permissions == '')?' selected':'');?>></option>
        <?php  while($U = mysqli_fetch_assoc($userResults)): ?>
          <option value="<?=$U['id']; ?>"<?=(($permissions = $U['id'])?' selected="selected"':'');?>><?=$U['permissions'];?></option>
        <?php endwhile; ?>

</select>

Here is my full code of this file (from around lines 104 - 110)

<?php
  require_once '../core/init.php';
  if (!is_logged_in()) {
    login_error_redirect();
  }
  if (!has_permission('admin')) {
    permission_error_redirect('index.php');
  }
  include 'includes/head.php';
  include 'includes/navigation.php';
$name = ((isset($_POST['name']) && $_POST['name'] != '')?sanitize($_POST['name']):'');
  if (isset($_GET['delete'])) {
    $delete_id = sanitize($_GET['delete']);
    $db->query("DELETE FROM users WHERE id = '$delete_id'");
    $_SESSION['success_flash'] = 'User has been deleted!';
    header('Location: users.php');
  }
  if (isset($_GET['add']) || isset($_GET['edit'])) {
    $name = ((isset($_POST['name']) && $_POST['name'] != '')?sanitize($_POST['name']):'');
    $email = ((isset($_POST['email']) && !empty($_POST['email']))?sanitize($_POST['email']):'');
    $password = ((isset($_POST['password']) && !empty($_POST['password']))?sanitize($_POST['password']):'');
    $confirm = ((isset($_POST['confirm']) && !empty($_POST['confirm']))?sanitize($_POST['confirm']):'');
    $permissions = ((isset($_POST['permissions']))?sanitize($_POST['permissions']):'');
    $errors = array();
if (isset($_GET['edit'])) {
  $edit_id = (int)$_GET['edit'];
  $userResults = $db->query("SELECT * FROM users WHERE id = '$edit_id'");
  $User = mysqli_fetch_assoc($userResults);
  $name = ((isset($_POST['name']) && $_POST['name'] != '')?sanitize($_POST['name']):$User['full_name']);
  $email = ((isset($_POST['email']) && $_POST['email'] != '')?sanitize($_POST['email']):$User['email']);
  $password = ((isset($_POST['password']) && $_POST['password'] != '')?sanitize($_POST['password']):$User['password']);
  $confirm = ((isset($_POST['confirm']) && $_POST['confirm'] != '')?sanitize($_POST['confirm']):$User['password']);
  $permissions = ((isset($_POST['permissions']) && $_POST['permissions'] != '')?sanitize($_POST['permissions']):$User['permissions']);
}
    if ($_POST) {
      $emailQuery = $db->query("SELECT * FROM users WHERE email = '$email'");
      $emailCount = mysqli_num_rows($emailQuery);

      if ($emailCount != 0 && $_GET['add']) {
        $errors[] = 'That email already exists in our database.';
      }

      $required = array('name','email','password','confirm','permissions');
      foreach ($required as $f) {
        if (empty($_POST[$f])) {
          $errors[] = 'You must fill out all fields.';
          break;
        }
      }
      if (strlen($password) < 6) {
        $errors[] = 'Your password must be at least 6 characters.';
      }

      if ($password != $confirm) {
        $errors[] = 'Your passwords do not match.';
      }

      if (!filter_var($email,FILTER_VALIDATE_EMAIL)) {
        $errors[] = 'You must enter a valid email';
      }

      if (!empty($errors)) {
        echo display_errors($errors);
      }else {
        // add user to database
        $hashed = password_hash($password,PASSWORD_DEFAULT);
        $sql = "INSERT INTO users (full_name,email,password,permissions) VALUES ('$name','$email','$hashed','$permissions'";
          if (isset($_GET['edit'])) {
            $sql = "UPDATE users SET full_name = '$name', email = '$email', password = '$hashed', permissions = '$permissions' WHERE id = '$edit_id'";
          }
        if ($_GET['add']) {
          $_SESSION['success_flash'] = 'User has been added!';
          $db->query($sql);
          header('Location: users.php');
        }
        if ($_GET['edit']) {
          $_SESSION['success_flash'] = "User has been updated!";
          $db->query($sql);
          header('Location: users.php');
        }
      }
    }
    ?>
    <h2 class="text-center"><?=((isset($_GET['edit']))?"Edit":"Add A New");?> User</h2><hr>
    <form action="users.php?<?=((isset($_GET['edit']))?'edit='.$edit_id:'add=1'); ?>" method="post">
      <div class="form-group col-md-6">
        <label for="name">Full Name:</label>
        <input type="text" name="name" id="name" class="form-control" value="<?=$name;?>">
      </div>
      <div class="form-group col-md-6">
        <label for="email">Email:</label>
        <input type="text" name="email" id="email" class="form-control" value="<?=$email;?>">
      </div>
      <div class="form-group col-md-6">
        <label for="password">Password:</label>
        <input type="password" name="password" id="password" class="form-control" value="<?=$password;?>">
      </div>
      <div class="form-group col-md-6">
        <label for="confirm">Confirm Password:</label>
        <input type="password" name="confirm" id="confirm" class="form-control" value="<?=$confirm;?>">
      </div>
      <div class="form-group col-md-6">
        <label for="permissions">Permissions:</label>
        <select class="form-control" name="permissions">
          <option value=""<?=(($permissions == '')?' selected':'');?>></option>
        <?php  while($U = mysqli_fetch_assoc($userResults)): ?>
          <option value="<?=$U['id']; ?>"<?=(($permissions = $U['id'])?' selected="selected"':'');?>><?=$U['permissions'];?></option>
        <?php endwhile; ?>

        </select>
      </div>
      <div class="form-group col-md-6 text-right" style="margin-top:25px;">
        <a href="users.php" class="btn btn-default">Cancel</a>
        <input type="submit" value="<?=((isset($_GET['edit']))?"Edit":"Add A New");?> User" class="btn btn-primary">
      </div>
    </form>
    <?php
  }else {

  $userQuery = $db->query("SELECT * FROM users ORDER BY full_name");
 ?>
<h2 class="text-center">Users</h2><hr>
<a href="users.php?add=1" class="btn btn-success pull-right" id="add-users-btn">Add New User</a><div class="clearfix"></div>

<table class="table table-bordered table-striped table-condensed">
  <thead><th></th><th>Name</th><th>Email</th><th>Join Date</th><th>Last Login</th><th>Permissions</th></thead>
  <tbody>
    <?php while($user = mysqli_fetch_assoc($userQuery)): ?>
      <tr>
        <td>
          <?php if($user['id'] != $user_data['id']): ?>
            <a href="users.php?edit=<?=$user['id'];?>" class="btn btn-default btn-xs" ><span class="glyphicon glyphicon-pencil"></span></a>
            <a href="users.php?delete=<?=$user['id'];?>" class="btn btn-default btn-xs" ><span class="glyphicon glyphicon-remove-sign"></span></a>
          <?php endif; ?>
        </td>
        <td><?=$user['full_name'];?></td>
        <td><?=$user['email'];?></td>
        <td><?=pretty_date($user['join_date']);?></td>
        <td><?=(($user['last_login'] == '0000-00-00 00:00:00')?'Never':pretty_date($user['last_login']));?></td>
        <td><?=$user['permissions'];?></td>
      </tr>
    <?php endwhile; ?>
</tbody>
</table>
<?php } include 'includes/footer.php'; ?>

I've probably made a dumb mistake somewhere, but I've been trying to figure this out all day, and haven't gotten anywhere.. Any help would be greatly appreciated. Thank you!

[cereal]

Hi,

you are trying to iterate the same result set two times: on line 28 and 106. On line 28 you get the first row, so if the query returns only one, you don't get anything when you call mysqli_fetch_assoc() again on line 106. You can use $User otherwise, if you need to loop again, insert data_seek() at line 105:

mysqli_data_seek($userResults, 0);

This will rewind the result set. Documentation:

• http://php.net/manual/en/mysqli-result.data-seek.php

[WebDevGuy32145]

Forgive me for not understanding the logic within the while loop to loop through the data in the database..I understand what you're saying, however I don't quite understand what I need as my conditions within my while loop to work again to loop and populate my select options. I want to my select options to output as follows:

<select class="form-control" name="permissions">
    <option value='editor>Editor</option>
    <option value='admin,editor>Admin</option>
</select>

..populating the values, output and selected output.

In my database, I have a row with a specific 'id', in the 'permissions' field, the data output should be either, 'editor' or 'admin,editor'. I want my select form to output those options as well as output the already selected option upon editing a user's permissions. Does that makes sense?

Do i just output both options in hard code html and then pass variables within each option? Such as..

<select class="form-control" name="permissions">
    <option value="editor"<?=(($permissions == 'editor')?' selected':'');?>>Editor</option>
    <option value="admin,editor"<?=(($permissions == 'admin,editor')?' selected':'');?>><?=$User['permissions'];?></option>
</select>

[WebDevGuy32145]

So, I solved my issue, or at least my code works for the functionality that I wanted to achieve.. Turns out I just needed to do this:

<select class="form-control" name="permissions">
    <option value=""<?=(($permissions == '')?' selected':'');?>></option>
    <option value="editor"<?=(($permissions == 'editor')?' selected':'');?>>Editor</option>
    <option value="admin,editor"<?=(($permissions == 'admin,editor')?' selected':'');?>>Admin</option>
    <?php while($U = mysqli_fetch_assoc($userResults)): ?>
        <option value="<?=$U['id'];?>"<?=(($permissions == $U['id'])?' selected':''); ?>><?=$U['permissions'];?></option>
    <?php endwhile; ?>
</select>

Thank you cereal for your input!

[cereal]

// EDIT
I just saw your reply, I'm glad you solved!

// Old answer
Hmm,

as far $permissions is defined by the input and defaulting to the database and not like an array:

$permissions = ((isset($_POST['permissions']) && $_POST['permissions'] != '')?sanitize($_POST['permissions']):$User['permissions']);

you can hardcode the options in the select tag, and then just compare which is set:

<select name="permissions">
    <option value="editor" <?php echo (0 == strcasecmp($permissions, 'editor')) ? ' selected="selected"' : ''; ?>>Editor</option>
    <option value="admin,editor" <?php echo (0 == strcasecmp($permissions, 'admin,editor')) ? ' selected="selected"' : ''; ?>>Admin,Editor</option>
</select>

For the sanitazation you could also write:

$permissions = filter_input(INPUT_POST, 'permissions', FILTER_SANITIZE_STRING) ? : $User['permissions'];

filter_input() will fail to FALSE or NULL if the filter fails or the input is not set, in both cases will fallback to $User['permission'], instead, it will return a string on success.

I usually manage selects through two functions:

if ( ! function_exists('_form_select'))
{
    /**
     * Create <select>
     *
     * $array_options format:
     *
     *  'option value' => 'text'
     * 
     * @param  string $name
     * @param  string $label
     * @param  array  $array_options
     * @param  string $selected
     * @return string
     */
    function _form_select($name, $label, $array_options, $selected = FALSE)
    {
        $template = '
        <label for="%1$s">%2$s</label>
        <select name="%1$s" id="%1$s">
            %3$s
        </select>
        ';

        $options = '';

        foreach($array_options as $key => $value)
            $options .= _form_options($key, $value, $selected);        

        return sprintf($template, $name, $label, $options);
    }
}

if ( ! function_exists('_form_options'))
{
    /**
     * Create <option>
     * 
     * @param  string  $key
     * @param  string  $value
     * @param  boolean $selected
     * @return string
     */
    function _form_options($key, $value, $selected = FALSE)
    {
        if(0 == strcasecmp($key, $selected))
            $opt = "<option value=\"$key\" selected>$value</option>";

        else
            $opt = "<option value=\"$key\">$value</option>";

        return $opt . PHP_EOL;
    }
}

Then I run:

<?php

$option_values = ['editor' => 'Editor', 'admin,editor' => 'Admin,editor'];
echo _form_select('permissions', 'Permissions', $option_values, $permissions);

This allows to change the option values dynamically, for example, you could have a database table with a range of permissions to set.