Search code examples
phpmysqliprepared-statement

populating my dropdown menu using procedural prepared statement

I would like to ask on how do I populate my dropdown/<select> via retrieving my data in MySQL database. I am using procedural prepared statement to add a security or avoid SQL injection.

Problem: It's only retrieving one data from my database which is I do have a two data stored in my table and how do I insert it through my <option> tag? I'm currently doing right now is first retrieve all research_title.

index.php

<form action="#" method="POST" enctype="multipart/form-data">
      <div class="form-group">
        <label for="LabelTitle">Research Title</label>
        <?php 
              include 'includes/includes_getOptionList.php';
        ?>
        
      </div>

includes_getOptionList.php

<?php
// Connection in DB is stored here
include 'connection_operation.php';


$query = "SELECT research_title FROM tbl_topicresearch";
$smtmt = mysqli_stmt_init($conn);

if (!mysqli_stmt_prepare($smtmt, $query)) {
    echo "SQL Statement" . mysqli_stmt_error($smtmt);
} else {
    mysqli_stmt_execute($smtmt);
    $getResult = mysqli_stmt_get_result($smtmt);
    if ($getResult) {
?>
        <select class="form-control" name="research_title" id="research_title">
            <?php
            while ($row = mysqli_fetch_assoc($getResult)) {
                $title_research = $row['research_title'];
                echo $title_research;
                echo '<option value="<?php echo $row[research_title]; ?>"> <?php echo $row[research_title]; ?> </option>';
            ?>
        </select>
    <?php
            }
        } else {
        }
    }

    ?>

enter image description here

Solution
    • The </select> close tag should be outside the while loop.
    • You must not include PHP inside PHP (ex: echo '<?php ... ?>';).
    • You have an extra call (echo $title_research;).

    Code:

    if ($getResult) 
{
    echo '<select class="form-control" name="research_title" id="research_title">';

    while ($row = mysqli_fetch_assoc($getResult)) 
    {
        $title_research = $row['research_title'];
        echo '<option value="' . $title_research . '">' . $title_research. '</option>';
    } 

    echo '</select>';
}
else 
{
    echo '<p>no results</p>';
}

    Or:

    <?php if ($getResult) : ?>

    <select class="form-control" name="research_title" id="research_title">

        <?php while ($row = mysqli_fetch_assoc($getResult)): ?>

            <option value="<?php echo $row['research_title'] ?>">
                <?php $row['research_title'] ?>
            </option>
        
        <?php endwhile; ?>

    </select>

<?php else: ?>

    <p>no results</p>

<?php endif; ?>