MYSQL PDO insert value from drop down list functionality

Question

In a user creation form I have one element which is a drop down list where a role_type (i.e admin) can be assigned to the user.

Im not sure how to extract the value from the drop down and set it in the 'user' table because the table in the database takes a number however the user will select the value from the drop down.

The table the drop down is populated from only has two columns 'RoleTypeCode' which is auto incremented and 'Role_Title' which is the column used to populate the drop down.

The table i wish to update is referencing the RoleTypeCode.

USER: (UserRecordID(PK - AutoIncrement), Forename, Surname, Email, RoleTypeCode(FK))

Role_Type: (RoleTypeCode(PK - AutoIncrement), Role_Title)

So far i have:

$forename = $_POST['Forename'];
$surname = $_POST['Surname'];
$email = $_POST['Email'];
$role_title = $_POST['Role_Title'];

/*** connect to database ***/
include "db_conx.php";

try
{
   $db_conx = new PDO("mysql:host=$mysql_hostname;dbname=$mysql_dbname", $mysql_username, $mysql_password);

   $db_conx->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

   $insertUser = $db_conx->prepare("INSERT INTO user (Forename, Surname, Email, Role_TypeCode ) VALUES (:forename, :surname, :email, :role_title )");

    /*** bind the parameters ***/
    $insertUser->bindParam(':forename', $forename, PDO::PARAM_STR);
    $insertUser->bindParam(':surname', $surname, PDO::PARAM_STR);
    $insertUser->bindParam(':email', $email, PDO::PARAM_STR);
    $insertUser->bindParam(':role_title', $role_title, PDO::PARAM_STR);

    /*** execute the prepared statement ***/
    $insertUser->execute();

    /*** success message ***/
    $message = 'New user added';
    }
catch(Exception $e)
{
    /*** check if the email already exists ***/
    if( $e->getCode() == 23000)
    {
        $message = 'This email has already been registered';
    }
    else
    {
        /*** fail message incase something went wrong ***/
        $message = 'Unable to process your request. Please try again later';
    }
}

not sure how to cater for role_title

any help would be much appreciated!! :)

UPDATE: the process I'm looking for is after a user hits the submit button. Trying to extract the information from the drop down and set it in the users table. The code above is for the submit file once a user has clicked 'submit'

david strachan · Answer 1 · 2015-02-22T22:17:46.860

1

Use the following to populate drop down

<?php
//database connection
$sql = "SELECT * FROM Role_Type";
$db_conx->query($sql); //query as no parameters
//create html for drop down
$SelectHtml = "<select name='Role_Title' id='Role_Title'>\n";//Name and id to suit
while($row = $db_conx->fetch()) {
  $SelectHtml .=<optionvalue='".$row[$RoleTypeCode]."'>".$row[$Role_Title]."</option>\n";
  }
$SelectHtml .= "<select>\n";
?>

HTML to show drop down

 <form action="xxx">
 form stuff
 <?php  echo $SelectHtml ?>
 more form stuff

Use to update using RoleTypeCode value which is FK to UserRecordID in USER

edited Feb 22 '15 at 22:17

answered Feb 22 '15 at 21:56

david strachan

7,174
2
23
33

oh thank you but i've already managed to populated drop down. I'm just confused about how to take the value from the dropdown and add it to the user table when the user clicks submit. The code in the question is for the adduser_submit file – user90210 Feb 22 '15 at 22:03

prodigitalson · Accepted Answer · 2015-02-22T22:23:53.747

Im not sure how to extract the value from the drop down and set it in the 'user' table because the table in the database takes a number however the user will select the value from the drop down.

The table the drop down is populated from only has two columns 'RoleTypeCode' which is auto incremented and 'Role_Title' which is the column used to populate the drop down.

You use the primary key of the RoleTypeCode as the dropdown value while you use the Role_Title as the display value. Then when you submit the form you just use the RoleTypeCode to set the FK value on the user.

So essentially you form would look like:

<?php
$stmt = $pdo->prepare('SELECT * FROM Role_Type');
$roles = $stmt->fetchAll(PDO::FETCH_ASSOC);
?>
<form>
  <label>First Name</label>
  <input type="text" name="forename" />

  <label>Last Name</label>
  <input type="text" name="surname" />

  <label>Email</label>
  <input type="text" name="email" />

  <label>Role</label>
  <select name="role">
      <?php foreach($roles as $role): ?>
          <option value="<?php echo $role['RoleTypeCode'] ?>"><?php echo $role['Role_Title'] ?></option>
      <?php endforeach; ?>
  </select>
</form>

Then when you get your submit it would be something like:

$insertStmt = $pdo->prepare(prepare('INSERT INTO user (Forename, Surname, Email, Role_TypeCode ) VALUES (:forename, :surname, :email, :role )');
$insertStmt->execute(array(
    ':forname' => $_POST['forename'],
    ':surname' => $_POST['surname'],
    ':email' => $_POST['email'],
    ':role' => $_POST['role']
));

that sounds like a good way to go about it. do i define the display value in the database or using html. Im using phpMyAdmin. thanks — user90210, Feb 22 '15 at 22:16
you guys are brilliant. ill give this a go and let you know! :) thanks! — user90210, Feb 22 '15 at 22:21
@user90210: Its also good to pick a standard format for naming tables and columns. I would recommend lowercase with and underscore word separator. — prodigitalson, Feb 22 '15 at 22:54
Its worked great :D @prodigitalson point taken, i've changed all my table names and columns to the standard format. thank you so much :D — user90210, Feb 23 '15 at 10:18

score 0 · Answer 3 · edited May 23 '17 at 12:12

0

If I understand your question correctly, what you want can be accomplished using AJAX -- which sounds complicated, but isn't. However, it is handled on the front end.

Here are some good posts for getting the basics of AJAX:

A simple example

More complicated example

Populate dropdown 2 based on selection in dropdown 1

edited May 23 '17 at 12:12

Community

1
1

answered Feb 22 '15 at 22:11

cssyphus

37,875
18
96
111

MYSQL PDO insert value from drop down list functionality

3 Answers3