PHP, grabbing a MySQL query from a MySQL populated drop down list

Question

Quick disclaimer- This is a homework assignment and I've gone through my notes and book and several online examples but I'm not getting anywhere.

I have database that I need to move into a PHP page. The PHP page will have a drop down list populated by a MySQL query, when the user clicks on a choice the page should return to itself another MySQL query. So when I chose a name, the flight information about that Name should be returned. I know I have to do something with $_POST but as having 3 days experience with PHP I'm not really sure how this will work.

<?php

try
{       //database login
    $dsn="mysql:host=courses; dbname=z1756942";
    $pdo = new PDO($dsn, $user, $password);

    //sql command to populate  drop down list
    $sql = ("SELECT CONCAT(fname,' ', lname) as full_name FROM passenger;");

    //sql statement to return on drop down pick
    $r->full_name;
    $row=$pdo->query($sql);
?>
<!-- start the drop down box -->
<form action=" " method ="POST">
    Passenger by Name:
    <select id="name" name="name">
    <option value= 0>Choose</option>

<?php
    //populate the drop down box
    foreach($row as $r)
    {
    $name=$r['full_name'];
     echo "<option value=\"$name\">".$name.'</option>';
    }
?>

    </select>
    <input type="submit" value="Submit">
    </form>
<?php

I have the tags for HTML, body and my catch statement, those all work correctly. The MySQL statement that needs to be returned to the same page is

SELECT flightnum, dateofflight, seatnum FROM manifest, passenger WHERE manifest.passnum LIKE passenger.passnum;

Answer 1

When the user clicks the submit button the selected value will populate in $_POST['name']. This is because the method you set in your form is POST and the name assigned to the select input field in your form is name. See the manual page for Handling external variables for more details about dealing with form input in PHP.

So in your example above you can determine whether or not the user has submitted the name value by checking $_SERVER['REQUEST_METHOD']. A normal HTTP GET request should just load the page and POST request triggers the new query.

Example

if ($_SERVER['REQUEST_METHOD'] === 'POST') { // user submitted the form
    if (isset($_POST['name'])) { // user submitted a name
        $stmt = $pdo->prepare('SELECT flightnum, dateofflight, seatnum
                                      FROM manifest, passenger
                                      WHERE manifest.passnum LIKE passenger.passnum
                                          AND passenger.fname = ?
                                          AND passenger.lname = ?'
        );
        // look up the flight info by passenger name
        $name = explode(" ", $_POST['name']);
        if ($stmt->execute($name)) { // statement executed successfully
            $rows = $stmt->fetchAll(); // rows fetched here
            /* Do whatever with rows */
            echo "Got it!";
        } else { // statement failed for some reason...
            echo "Error executing query!"; // debug with PDO::errorInfo()
            exit; // you should handle this gracefully later
        }
    } else { // user did not submit a name
        echo "Please go back and select a name!";
        exit; // handle graceful failures here
    }
} else { // user just loaded the page
    /* spit out the form here */
}