1

I have two tables, user_info with fields: 'name', 'userid', 'email', 'phone', 'pass' ,'role_id', and user_role with fields: 'role_id' and 'role_name'. I have created two rows in the second table, first, role_id: 1, role_name: Owner and second, role_id: 2, role_name: Customer. I want my dropdown to get values from there and I tried doing this but it didn't work. The first block is code is form validation and inserting data into the database(this works) and the second is trying to get dynamic values in select option.

<-- form -->
<?php
    include('db_conn.php');
    if(isset($_POST["submitform"])){

      $name = mysqli_real_escape_string($con, $_POST['name']);
      $userid = mysqli_real_escape_string($con, $_POST['userid']);
      $email = mysqli_real_escape_string($con, $_POST['email']);
      $phone = mysqli_real_escape_string($con, $_POST['phone']);

      $pass = md5($_POST['pass']);
      $cpass = md5($_POST['cpass']);
      $role_id = $_POST['role_id'];

      $select = "SELECT * FROM user_info WHERE email = '$email'
      && pass = '$pass' ";

      $result = mysqli_query($con, $select);

      if(mysqli_num_rows($result) > 0){
        $error[] = 'user already exists';
      }else{
        if($pass != $cpass){
          $error[]= 'password does not match';
        }else{
          $insert = "INSERT INTO user_info (name, userid, email, phone, pass, role_id)
          VALUES ('$name', '$userid', '$email', '$phone', '$pass', '$role_id')";
          mysqli_query($con, $insert);
          header('location: login.php');
        }
      }

    };

?>


<-- dropdown list -->
                  <select style="padding:5px; width:200px; border-radius:20px;">
                  <option>select role</option>
                  <?php
                  include('db_conn.php');
                  $query = mysqli_query("SELECT * FROM user_info");
                  $user_roles = mysqli_query ($con, $sql);
                  $count = mysqli_num_rows($user_roles);
                  ?>
                  <?php
                  for($i=1; $i<=$count; $i++){
                    $row = mysqli_fetch_array($user_roles)
                  ?>
                  <option value="<?php echo $row["role_id"]; ?>">
                  <?php echo $row["role_name"]; ?>
                  </option>
                  <?php
                  }
                  ?>
            
            
                </select>
Bluesy Ace
  • 21
  • 1
  • 1
    Know that you have other code problems that can cause SQL injection issues, and the `mysqli_real_escape_string` function is not enough protection to help, but I believe this line: `$query = mysqli_query("SELECT * FROM user_info");` should be: `$sql = "SELECT * FROM user_info";`? – Paul T. Jul 13 '22 at 04:15
  • **Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/32391315) – Dharman Jul 13 '22 at 11:25
  • **Never store passwords in clear text or using MD5/SHA1!** Only store password hashes created using PHP's [`password_hash()`](https://php.net/manual/en/function.password-hash.php), which you can then verify using [`password_verify()`](https://php.net/manual/en/function.password-verify.php). Take a look at this post: [How to use password_hash](https://stackoverflow.com/q/30279321/1839439) and learn more about [bcrypt & password hashing in PHP](https://stackoverflow.com/a/6337021/1839439) – Dharman Jul 13 '22 at 11:25

0 Answers0