How can I use a select box in HTML to search for a specific user in the database and show that information on a table?

Question

I have a select box that shows the names of all the users in the database, however, I need, using a "Find Button" on the selected user on the combo box, that the data attached to that user shows up on the table

Table that currently shows the data of all users

<table class="table table-hover">
    <thead class="thead-dark"></thead>
    <tr>
        <th scope="col">Shift ID</th>
        <th scope="col">Name</th>
        <th scope="col">Origin</th>
        <th scope="col">Destination</th>
        <th scope="col">Date</th>
    </tr>
    </thead>
    <?php
    global $result, $query;
    $sql = "SELECT * FROM shifts";
    $result = $db->query($sql);
    //Fetch Data form database
    if ($result->num_rows > 0) {
        // output data of each row
        while($row = $result->fetch_assoc()) {
            echo "<tr><td>" . $row["shift_id"]. "</td><td>" . $row["name"] . "</td><td>"  . $row["origin"] . "</td><td>"  . $row["destination"] . "</td><td>"  . $row["date"] . "</td><td>"
                . $row["password"]. "</td></tr>";
        }
        echo "</table>";
    } else { echo "0 results"; }
       ?>
</table>

And here's the form that shows the users in the select box

<form name="form1" method="POST" action="">
    <select name="getUser">
        <?php
        $res = mysqli_query($db, "SELECT * FROM shifts");
        while ($row = mysqli_fetch_array($res))
        {
            ?>
            <option><?php echo $row ["name"]; ?></option>
            <?php
        }
        ?>
    </select>
    <button class="btn-primary rounded">Find</button>
</form>

I'm trying to make it that so when the selected user in the combo box and the find button is pressed, that the data found goes all into the table described above.

I was maybe gonna try to attach a variable to the select box and compare it with the names field on the database.

Something like this

    $query = "SELECT * FROM shifts WHERE $name == $nameSelected ";

Thanks.

Try adding the value property of the – Jikiwiki Jun 02 '19 at 18:00 — Jikiwiki, Jun 02 '19 at 18:00

imposterSyndrome · Accepted Answer · 2019-06-02T21:43:30.113

first echo the user id into the option's value

<option value-"<?echo your id?>"><?php echo $row ["name"]; ?></option>

then when your form submits you get get the value from the $_POST

$userId = $_POST['getUser'];

not you can use the variable to query the database, but you should NEVER put it straight in, you should use PDO prepared statements to prevent injection.

$servername = "localhost";
$username = "username";
$password = "password";



try {
    $conn = new PDO("mysql:host=$servername;dbname=myDB", $username, $password);
    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected successfully"; 
    }
catch(PDOException $e)
    {
    echo "Connection failed: " . $e->getMessage();
    }
//something like this
$query = $conn->prepare("SELECT * FROM shifts WHERE id = :id");
$query->bindParam(':id',$userId,PDO::PARAM_INT);
$query->execute()
return $query->fetchAll();// I realised you wanted to get all the shifts so you don want fetchAll(),

notice that in mysql we only use a single = for our comparison unlike php. Also i've changed name to the unique row in the database, as unless your name field is unique how do you know which use called Dan you want?

If you want to do this without re-loading the whole page you will need to look into using Ajax and passing the value of the option tag via jQuery. Here are some places to start:

https://www.w3schools.com/php/php_mysql_connect.asp

https://www.w3schools.com/xml/ajax_intro.asp

I actually didn't know about that. Thanks for sharing this information. Still very new to programming so I'm glad to know this. — Pedro Dias, Jun 02 '19 at 18:19
Also, should the prepared statements be used for every single query that I have on my website? Thanks! — Pedro Dias, Jun 02 '19 at 18:30
you should always use the pdo object to make your query, if you are not passing any params (e.g. ````SELECT * FROM TABLE`````) then you can use ````$conn->query($sql);```` but if you are inserting then you need to properly bind every variable. The first rule of inserts is Never trust user input — imposterSyndrome, Jun 02 '19 at 18:57

score -1 · Answer 2 · answered Jun 02 '19 at 18:21

if you are not comfortable with javascript (AJAX), try on your form

<?php $res = mysqli_query($db, "SELECT * FROM shifts"); ?>
<form name="form1" method="POST" action="<?php echo $_SERVER['PHP_SELF']; ?>"
    <select name="getUser">
        <option value='All'>All</options>
        <?php
            while ($row = mysqli_fetch_array($res)) { ?>
                <option value='$row ["name"]'><?php echo $row ["name"]; ?></option>
        <?php } ?>
    </select>
    <button class="btn-primary rounded">Find</button>
</form>

And in your table

<table class="table table-hover">
    <thead class="thead-dark"></thead>
    <tr>
        <th scope="col">Shift ID</th>
        <th scope="col">Name</th>
        <th scope="col">Origin</th>
        <th scope="col">Destination</th>
        <th scope="col">Date</th>
    </tr>
    </thead>
    <?php
    global $result, $query;
    if ($_POST['getUser'] == 'All'){
        $sql = "SELECT * FROM shifts";
    } else {
        $sql = "SELECT * FROM shifts WHERE name = " . $_POST['getUser'];
    }
    $result = $db->query($sql);
    //Fetch Data form database
    if ($result->num_rows > 0) {
        // output data of each row
        while($row = $result->fetch_assoc()) {
            echo "<tr><td>" . $row["shift_id"]. "</td><td>" . $row["name"] . "</td><td>"  . $row["origin"] . "</td><td>"  . $row["destination"] . "</td><td>"  . $row["date"] . "</td><td>"
                . $row["password"]. "</td></tr>";
        }
        echo "</table>";
    } else { echo "0 results"; }
       ?>
</table>

putting post variables straight into any query is a huge security issue and should never be recommended — imposterSyndrome, Jun 02 '19 at 18:24
$getuser = mysqli_real_escape_string($_POST['getuser']); therefore, $sql = "SELECT * FROM shifts WHERE name = " . $getUser; — user4468920, Jun 02 '19 at 18:37
https://stackoverflow.com/questions/36145598/when-should-i-use-php-mysqli-real-escape-string-function - notice that this answer is over 3 years old,you need to change your approach if you still think real escape string is the way to go — imposterSyndrome, Jun 02 '19 at 18:59

How can I use a select box in HTML to search for a specific user in the database and show that information on a table?

2 Answers2