How to loop through results of a mysql query and display them in option values in an html form

Question

How can I loop through results of a MySQL query and display them in option values in the html form in my script.

I have tried putting the values manually into the option tags and values, but I want to do it depending on whats already inside the database. Do I need another connection to the database to run in the same part as the form element itself?

<title>Add a unit</title>
</head>
<body>
<div class= "container">
<h1>Add a unit</h1>

<?php // Script 12.4 - add_size.php

// This script adds a blog size to the database.


if ($_SERVER['REQUEST_METHOD'] == 'POST') { // Handle the form.

    // Connect and select:
    $connection = mysqli_connect('localhost', $user, $password, $database);
    mysqli_set_charset($connection, 'utf8');

    // Validate the form data:
    $problem = false;
    if (!empty($_POST['unit']) && !empty($_POST['size']) && !empty($_POST['price'] && isset($_POST['building'])) {
        $unit     = mysqli_real_escape_string($connection, trim(strip_tags($_POST['unit'])));
        $size     = mysqli_real_escape_string($connection, trim(strip_tags($_POST['size'])));
        $price    = mysqli_real_escape_string($connection, trim(strip_tags($_POST['price'])));
        $building = mysqli_real_escape_string($connection, trim(strip_tags($_POST['building'])));
    } else {
        echo '<p style="color: red;">Please submit a unit and an size and price.</p>';
    }

    if (!$problem) {
        // Define the query:
        $query = "INSERT INTO individualspecs (Space, Size, Price, fk_Id, Id) VALUES ('${unit}', '${size}', '${price}', '${building}', 0)";

        // Execute the query:
        if (@mysqli_query($connection, $query)) {
            echo '<p>The unit has been added!</p>';
            // why doesnt print "$msg"; work when using $i
        } else {
            echo '<p style="color: red;">Could not add the unit because:<br>'.mysqli_error($connection).'.</p><p>The query being run was: '.$query.'</p>';
            echo $msg;
        }

        mysqli_close($connection); // Close the connection.
    } // No problem!
} // End of form submission IF.


// Display the form:
?>

<form action="add_units.php" method="post" enctype="multipart/form-data">

    <p>Select Building: <select name="building">
        <option value="<?php echo ?>"><?php echo ?></option>
        <option value=""></option>
        <option value=""></option>
        <option value=""></option>
    </select>
    </p>

    <p>Enter Unit: <input type="text" name="unit" size="40" maxsize="100"></p>
    <p>Enter Size in Sq Feet: <input type="number" name="size" size="40" maxsize="100"></p>
    <p>Enter Price: <input type="text" name="price" size="40" maxsize="100"></p>



    <!-- removed upload photos -->


    <input type="submit" name="submit" value="Add indiviual Space!">

</form>

</div>
</body>
</html>

I would like the select dropdown menu to show a list of all buildings currently in the database so that the user can select a building to add his unit to. If no buildings exist in database handle situation i.e. echo 'No buildings found in database, you need to add a building record before attempting to add individual units';

Here is my buildings table: https://i.stack.imgur.com/E325F.jpg

Here is my units table: https://i.stack.imgur.com/ebiKh.jpg

There is a missing ')' here: `!empty($_POST['price'] && isset($_POST['building'])){` — Dharman, Jan 01 '19 at 00:38
**DO NOT SILENCE YOUR ERRORS!** The errors are there to help you code. Do not use `@` to get rid of the errors, rather fix the underlying problems. — Dharman, Jan 01 '19 at 00:41
To prevent SQL injection use prepared statements instead of `mysqli_real_escape_string`. Also `strip_tags` as assumed to be a XSS prevention is not enough. [How to prevent XSS with HTML/PHP?](https://stackoverflow.com/questions/1996122/how-to-prevent-xss-with-html-php) — Dharman, Jan 01 '19 at 00:50

score 1 · Answer 1 · edited Jan 01 '19 at 00:37

Here's a simple code to do what you need

Your code is so messed up, try to clean it :-)

We connect to mysql DB Using PDO class because it's more powerful and secure

you can change root with your db username pass with your db password db with your db name read more about PDO here

// connect to db
$dbh = new \PDO('mysql:host=127.0.0.1;dbname=db', "root", "pass");
// query to select from db
$q = 'SELECT * FROM users';
// prepare and execute the query 
$buildsq = $dbh->prepare($q);
$buildsq->execute();
// fetch the results and save them to $build var
$builds = $buildsq->fetchAll();
// check if their is results and print them 
if($buildsq->rowCount()) {
    foreach ($builds as $build) {
        echo '<option value="">' . $build['name'] . '</option>';
    }
} else {
    echo "<option>No results </option>";
}

It's not the best, but it does what you need.
Try to put connection part in a function to clean up your code.

I am all for PDO, but OP is not using PDO and claiming that it is more powerful and secure without any evidence might not be good enough reason to change. You seem to have extrapolated a lot of information from the question, but the truth is that we know too little about OP's code to help him. I would suggest to limit your code example only to the `foreach` part (i.e. abstract from DB interface). — Dharman, Jan 01 '19 at 01:04

How to loop through results of a mysql query and display them in option values in an html form

1 Answers1

Here's a simple code to do what you need