使用MySQL查询中的名字和姓氏填充下拉列表[关闭]

the first part of this problem had me get a captains name from a text box, query the database and pull information into a table. Now I need to create a drop down list with the captains name, populate it with first name and last name ( database fields are separate so I need to join it. I tried explode but I can't get anything to populate in the first place), then show the table again when the user presses the submit button.

So to reiterate I'm having trouble figuring out how to:

Joining data form fname and lname columns and
Displaying them inside the drop down menu.

Thank you for your help in advance, I'll continue to try to work it out in the mean time.

EDIT: I was able to populate the drop down with the names with the revised code below. I still have a problem grabbing the the name selected and querying for data below since it has changed to a drop down list instead of a text box where the user would enter the desired name.

I receive the following error:

Notice: Undefined index: name in C:\Users...\PhpstormProjects\test.php on line 26

Line 26 : $name = htmlentities($_POST['name']);

<!DOCTYPE html>
<html lang="en">
<head>
<title></title>
</head>
<body>
<form action="test.php"method="post">
<input type="submit" name="submit" value="submit">
</form>
    <?php
    $connection = mysqli_connect("host", "username", "password") or die ("could not connect to mysql");
    mysqli_select_db($connection, 'database') or die ("no database");

    $captainResults = mysqli_query($connection, "SELECT concat(fname,' ', lname) as capname from captain");
    echo '<select name="name">';
    while ($row = mysqli_fetch_row($captainResults)){
       foreach ($row as $value) {
               echo '<option>' . $value. ' </option>';
        }
    }
    echo '</select>';

    if(isset($_POST['submit'])) {
        $name = htmlentities($_POST['name']);
        $parts = explode(" ", $name);
        $lastname = array_pop($parts);
        $firstname = implode(" ", $parts);

        $connection = mysqli_connect("mysql553.profrusso.com", "finalexam", "finalexam");

        mysqli_select_db($connection, 'shoretoshore');

        $result = mysqli_query($connection, "SELECT ship_no, shipment_id, arrival_date, origin, destination, lname, fname from shipment, captain WHERE captain.capt_id=shipment.capt_id AND captain.fname='$firstname' AND captain.lname='$lastname'");

        echo '<table border="1">
            <tr style="font-weight:bold">
            <th>Shipment No.</th>
            <th>Shipment Id.</th>
            <th>Arrival Date</th>
            <th>Origin</th>
            <th>Destination</th>
            <th>Last Name</th>
            <th>First Name</th>
            </tr>';
        while ($row = mysqli_fetch_row($result)) {
        echo '<tr>';
        foreach ($row as $value)
            print "<td>".$value."</td>";
            echo "</tr>";
        }
        echo "</table>";
    }
?>
</body>
</html>

写回答
好问题 0 提建议
追加酬金
关注问题
分享
邀请回答
编辑收藏删除结题
收藏举报

2条回答默认最新

duanfan9859 2015-08-05 20:15

关注

Not sure if you've tried already, but just as a suggestion it might be easier to use a proper combobox (aka: ).

<?php

// We're going to need the connection in any case
$connection = mysqli_connect("server", "username", "password", "database");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s
", mysqli_connect_error());
    exit();
}

    echo '<table border="1">
        <tr style="font-weight:bold">
        <th>Shipment No.</th>
        <th>Shipment Id.</th>
        <th>Arrival Date</th>
        <th>Origin</th>
        <th>Destination</th>
        <th>Last Name</th>
        <th>First Name</th>
        </tr>';
    while ($row = mysqli_fetch_row($result)) {
    echo '<tr>';
    foreach ($row as $value)
        print "<td>".$value."</td>";
        echo "</tr>";
    }
    echo "</table>";

if(isset($_POST['submit'])) {
    $name = htmlentities($_POST['name']);
    $parts = explode(" ", $name);
    $lastname = array_pop($parts);
    $firstname = implode(" ", $parts);

    // Make sure to cleanse the input to prevent attacks
    $firstname = mysqli_real_escape_string( $connection, $firstname );
    $lastname = mysqli_real_escape_string( $connection, $lastname );

    // Query the DB for specific captain data
    $result = mysqli_query($connection, "SELECT ship_no, shipment_id, arrival_date, origin, destination, lname, fname from shipment, captain WHERE captain.capt_id=shipment.capt_id AND captain.fname='$firstname' AND captain.lname='$lastname'");
} else {
    // Here is where we'll handle initial page loading

    // Query the DB for bootstrapping data
    $shipmentResults = mysqli_query($connection, "SELECT ship_no, shipment_id, arrival_date, origin, destination, lname, fname from shipment);

    // Use this to populate dropdown (aka: combobox/select) for captain name
    $captainResults = mysqli_query($connection, "SELECT * from captain);


    // Build the dropdown
    echo '<select id="captain_drop_down">';
        while ($row = mysqli_fetch_row($captainResults)) {
           foreach ($row as $value) {
               echo '<option value="' . $row["id"] . '">' . $row["firstname"] . '" "' . $row["lastname"] . '</option>';
           }
        }
    echo '</select>'; // End of #captain_drop_down
}