how to populate html dropdown with sql data

Question

I am new to PHP, hoping for some help please. I am trying to populate an HTML dropdown list with data from my SQL database. I would like to be able to select an item from a dropdown list that would then fill an HTML table with the associated record from the database. So far I have managed to connect to my database and retrieve all of the data from the relevant table.

Can someone please help me set this up to work through the dropdown list?

Thanks

 <?php
 $username = 'root';
 $password = '';

 $conn = new PDO( 'mysql:host=localhost; dbname=Oaktown', $username, $password );
 $sql ="SELECT RoundNumber, RoundDate, HomeTeam, HomeTeamScore, AwayTeam, AwayTeamScore FROM Fixture";
 $statement = $conn->prepare( $sql );
 $statement->execute();
 $results = $statement->fetchAll( PDO::FETCH_ASSOC );

 ?>
        <h2>Competitions</h2>
<article>
    <p id="TableHeader1">Fixture Information</p>
    <P>Select Round and Game number from the dropdown list under Round Number.</P>
    <br>

    <br><form method="post" action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]); ?>">
<p id="TableHeader2">Round Number &nbsp; &nbsp; 
<select style="width:250px"></select>&emsp;&emsp;<input class="button" 
type="submit" name="Get" value="Get Fixture Results"></p>

<p id="TableHeader2">Results</p>
<table class="table">
<tr><td><b>Round Number:</b></td>
<?php foreach( $results as $row ){
    echo "<td>";
    echo $row['RoundNumber'];
 }
?>
</tr>

<tr>
<td><b>Round Date:</b></td>.
<?php foreach( $results as $row ){
    echo "<td>";
    echo $row['RoundDate'];
 }
 ?>
</tr>

<tr>
<td><b>Home Team:</b></td>
<?php foreach( $results as $row ){
    echo "<td>";
    echo $row['HomeTeam'];
 }
 ?>
</tr>

<tr>
<td><b>Home Team Score:</b></td>
<?php foreach( $results as $row ){
    echo "<td>";
    echo $row['HomeTeamScore'];
   }
 ?> 
</tr>

<tr>
<td><b>Away Team:</b></td>
<?php foreach( $results as $row ){
    echo "<td>";
    echo $row['AwayTeam'];
   }
 ?> 
</tr>

<tr>
<td><b>Away Team Score:</b></td>
<?php foreach( $results as $row ){
    echo "<td>";
    echo $row['AwayTeamScore'];
   }
 ?> 
            <td colspan="2><?php echo $message; ?>"></td>
</tr>

 </table>

</form>

Ted · Accepted Answer · 2018-03-09 09:34:06Z

4

Using a similar block of code like the one you use for the tables you can do something like this:

<select>
<?php foreach( $results as $row ){
    echo "<option value='" . $row['value column'] . "'>" . $row['text column'] . "</option>";
   }
 ?> 
</select>

if you don't have or need a pair of values then you can simply do this:

<select>
<?php foreach( $results as $row ){
    echo "<option>" . $row['text column'] . "</option>";
   }
 ?> 
</select>

answered Mar 9, 2018 at 9:34

Ted

4,0671 gold badge23 silver badges35 bronze badges

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

5 Comments

Bec Over a year ago

Thanks Ted, that has now populated my list. Is there a way that this can now restrict my data to just the record i select from the list?

Ted Over a year ago

that's quite an open question. do you need to re-run the db query, pass it to another page or just filter the data that the <table> displays? all those question have very different answers

Bec Over a year ago

just filter the data that the <table> displays or is it possible to start with a blank <table> that the selection from the dropdown then completes with a single record?

Ted Over a year ago

1. Filtering the data on the <table> is quite tricky and requires a lot of javascript, jQuery and/or an add-on like DataTables datatables.net/index 2. To run the db query after the user has made a choice, requires a lot of changes in your code. You let the user make a choice, submit the value of the select with a form and then on the same or a different page use the submitted value as a parameter in your query.

Ted Over a year ago

And since I answered your original question, I would appreciate it if you marked my post as the answer by clicking on the Tick ;)

Collectives™ on Stack Overflow

how to populate html dropdown with sql data

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related