PHP PDO populating dropdown from large table in record edit form - seeing result from database

[fergua]

I'm learning PHP (and having great fun) but I've run up against a problem in a CRUD application that I can't seem to find a way around.

I need to use a dropdown for data entry on the create page. I also want to have the same dropdown on the update page, but of course this would need to set the value to the one already stored in the record when the update form loads. I have hand-coded some of the shorter dropdowns (see below) but the one I need to do next is 2008 records!! (it will be searched as input is typed).

I need to populate the dropdown from a database table ('antimicrobials' from tbl_antmicrobials) to list the values that way but in a way that sets dropdown to existing value when using edit form.

The relevant code is as follows (please be gentle about the coding...I'm new!!). Any help much appreciated.

<?php 

require_once "../includes/header.php";

?>


<?php

if ( isset($_POST['patient_id']) && isset($_POST['datadate'])

    && isset($_POST['bedspace']) && isset($_POST['episode_id']) ) {

$sql = "UPDATE tbl_users SET patient_id = :patient_id,

         datadate = :datadate, bedspace = :bedspace, isadmitday = :isadmitday, antimicrobial = :antimicrobial

        WHERE episode_id = :episode_id";

$stmt = $PDO->prepare($sql);

$stmt->execute(array(

    ':patient_id' => $_POST['patient_id'],

    ':datadate' => $_POST['datadate'],

    ':bedspace' => $_POST['bedspace'],

    ':episode_id' => $_POST['episode_id'],

    ':isadmitday' => $_POST['isadmitday'], 

    ':antimicrobial' => $_POST['antimicrobial']));

$_SESSION['success'] = '<div class="alert alert-success" role="alert"><strong>Record successfully updated</strong></div>';

header( 'Location: index.php' ) ;

return;

}

$stmt = $PDO->prepare("SELECT * FROM tbl_users where episode_id = :xyz");

$stmt->execute(array(":xyz" => $_GET['episode_id']));

$row = $stmt->fetch(PDO::FETCH_ASSOC);

if ( $row === false ) {

$_SESSION['error'] = '<div class="alert alert-danger" role="alert">    <strong>Unable to proceed - bad episode_id value</strong></div';

header( 'episode_id: index.php' ) ;

return;

}

$n = htmlentities($row['patient_id']);

$e = htmlentities($row['datadate']);

$l = htmlentities($row['bedspace']);

$i = htmlentities($row['isadmitday']);

$am = htmlentities($row['antimicrobial']);

$episode_id = htmlentities($row['episode_id']);

?>



    <div class="container">

        <div class="row">

            <div class="row">

                <div class="col-md-12">

                    <h2>Edit episode record</h2><br>

                </div> <!-- /col-md-12 -->

            </div> <!-- /row -->

        </div> <!-- /row -->


        <div class="row">

            <form name="edit_record" id="edit_record" method="POST" action="">

                <div class="form-group">

                    <label for="patient_id" control-label>Patient ID</label>

                    <input type="text" class="form-control" id="patient_id"     name="patient_id" value="<?= $n ?>">

                </div> <!-- /form-group -->


                <div class="form-group">

                    <label for="datadate" control-label>Date</label>

                    <input type="text" class="form-control" id="datadate" name="datadate" value="<?= $e ?>">

                </div>  <!-- /form-group -->


                <div class="form-group">

                    <label for="bedspace" control-label>Bedspace</label>

                    <select class="form-control" required="required" id="bedspace" name="bedspace" value="<?= $l ?>">

                        <option></option>

                        <option value="Side Ward 1" <?php echo $l == 'Side     Ward 1'?'selected':'';?>>Side Ward 1</option>

                        <option value="Bed 1" <?php echo $l == 'Bed   1'?'selected':'';?>>Bed 1</option>

                        <option value="Bed 2" <?php echo $l == 'Bed 2'?'selected':'';?>>Bed 2</option>

                        <option value="Bed 3" <?php echo $l == 'Bed 3'?'selected':'';?>>Bed 3</option>

                        <option value="Bed 4" <?php echo $l == 'Bed 4'?'selected':'';?>>Bed 4</option>

                        <option value="Bed 5" <?php echo $l == 'Bed 5'?'selected':'';?>>Bed 5</option>

                        <option value="Side Ward 2" <?php echo $l == 'Side Ward 2'?'selected':'';?>>Side Ward 2</option>

                        <option value="Side Ward 3" <?php echo $l == 'Side Ward 3'?'selected':'';?>>Side Ward 3</option>

                    </select>

                </div>  <!-- /form-group -->

[Jacques1]

The values you're dealing with (bedspace, antimicrobials, ...) should have numeric IDs, and you should use those IDs rather than some text when the value is selected and stored.

 

When this is done, you can select both the ID and the descriptive text from the table, iterate over the rows and build the dropdown dynamically.

<?php

const APP_HTML_ENCODING = 'UTF-8';

<?php

function html_escape($raw_input, $encoding = APP_HTML_ENCODING)
{
    return htmlspecialchars($raw_input, ENT_QUOTES | ENT_SUBSTITUTE, $encoding);
}

<?php

/* ... */

$antimicrobials = $PDO->query('SELECT antimicrobial_id, description FROM antimicrobials')->fetchAll();

/* ... */

?>

<!-- ... -->

<select <!-- ... -->>
    <?php foreach ($antimicrobials as $antimicrobial): ?>
        <option value="<?= html_escape($antimicrobial['antimicrobial_id']) ?>"><?= html_escape($antimicrobial['description']) ?></option>
    <?php endforeach; ?>
</select>

Feel free to add the logic for preselecting a value.

Edited August 23, 2016 by Jacques1

[mac_gyver]

and if you make an array of the bedspace values, like they had been retrieved from a db table, you can dynamically produce the bedspace select/option menu by looping over the array, without needing to write out and the test and fix all that code and markup.

 

btw - the <select ... value="..."> tag doesn't use a value="..." attribute and the one you are showing in your code should be removed since it doesn't do anything.