how to populate dropdown list values from mysql

Question

I'm trying to add a user into my users table, the form asks for user name and asks the user to select a manager from a dropdown list. I'd like to populate the existing managers from the manager_id column in the managers table. However I have no idea how to do that. this is my code so far:

<?php

if (isset($_POST['add'])) {
require_once 'login.php'; 

  $OK = false;

  $conn = new mysqli ($host, $user, $password, $database) or die("Connection Failed");

  $stmt = $conn->stmt_init();

  $sql = 'INSERT INTO users (user_name, user_manager, user_creation_date)
          VALUES(?, ?, NOW())';
  if ($stmt->prepare($sql)) {

    $stmt->bind_param('ss', $_POST['user_name'], $_POST['user_manager']);

    $stmt->execute();
    if ($stmt->affected_rows > 0) {
      $OK = true;
    }
  }

  if ($OK) {
    header('Location: add_user_confirm.php');
    exit;
  } else {
    $error = $stmt->error;
  }
}
?>
<!DOCTYPE HTML>
<html>
<head>

<title>Add new user</title>
</head>

<body>
<h1>Add new user</h1>
<?php if (isset($error)) {
  echo "<p>Error: $error</p>";
} ?>
<form id="form1" method="post" action="">
  <p>
    <label for="user_name">user name:</label>
    <input name="user_name" type="text" class="widebox" id="user_name">
  </p>
  <p>
    User manager: <select name="user_manager" size "1">

  </p>
  <p>
    <input type="submit" name="add" value="add_user" id="add">
  </p>
</form>
</body>
</html>

My 'managers' table looks like this: manager_id | manager_name | manager_dept

Could someone please help? THANKS in advance

@DoSparKot My 'managers' table looks like this: manager_id | manager_name | manager_dept — elmify
– elmify, Commented Feb 10, 2013 at 21:46

Achrome · Accepted Answer · 2013-02-10 21:59:31Z

2

Something like this should work

<p>
    User manager: <select name="user_manager" size "1">
    <?php $stmt->close();
    $stmt2 = $conn->stmt_init();
    $selectQ = 'select * from managers';
    $stmt2->prepare($selectQ);
    $stmt2->execute();
    $result = $stmt2->get_result();
    while($resultRow = $result->fetch_array(MYSQLI_ASSOC))
        echo '<option value="$resultRow[manager_id]">$resultRow[manager_name]</option>';
    $result->close();
    $stmt2->close();
    ?>
</p>

edited Feb 10, 2013 at 21:59

answered Feb 10, 2013 at 21:52

Achrome

7,82114 gold badges38 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

elmify Over a year ago

thanks for this, but when I add this to my code it gives an error saying : syntax error, unexpected $end in C:\xampp\htdocs\users\add_users.php on line 49 -> this is where i have my </html> tag

Achrome Over a year ago

Sorry about that. I left the while block open. Fixed

elmify Over a year ago

Thanks a lot, that fixed the problem however it's not showing anydata even though I have some.

elmify Over a year ago

it's just a blank dropdownlist

Achrome Over a year ago

You can do a var_dump on the $resultRow. There should be some data there.

|

Mark · Accepted Answer · 2013-02-10 22:17:07Z

Below is an example of adding a user.

   /**
 * Storing new user
 * returns user details
 */
public function storeUser($name, $email, $password) {
    $uuid = uniqid('', true);
    $hash = $this->hashSSHA($password);
    $encrypted_password = $hash["encrypted"]; // encrypted password
    $salt = $hash["salt"]; // salt
    $result = mysql_query("INSERT INTO users(unique_id, name, email, encrypted_password, salt, created_at) VALUES('$uuid', '$name', '$email', '$encrypted_password', '$salt', NOW())");
    // check for successful store
    if ($result) {
        // get user details
        $uid = mysql_insert_id(); // last inserted id
        $result = mysql_query("SELECT * FROM users WHERE uid = $uid");
        // return user details
        return mysql_fetch_array($result);
    } else {
        return false;
    }
}

Remember you don't want to store the password in its orginal form hence the $salt and hashSSHA function. those are listed below.

    /**
 * Encrypting password
 * @param password
 * returns salt and encrypted password
 */
public function hashSSHA($password) {

    $salt = sha1(rand());
    $salt = substr($salt, 0, 10);
    $encrypted = base64_encode(sha1($password . $salt, true) . $salt);
    $hash = array("salt" => $salt, "encrypted" => $encrypted);
    return $hash;
}

/**
 * Decrypting password
 * @param salt, password
 * returns hash string
 */
public function checkhashSSHA($salt, $password) {

    $hash = base64_encode(sha1($password . $salt, true) . $salt);

    return $hash;
}

These functions will help to keep things secure. also if you want to learn more about this or see the entire API and all its code please visit here

Collectives™ on Stack Overflow

how to populate dropdown list values from mysql

2 Answers 2

9 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

Comments

Related