duancuan6466
duancuan6466
2015-08-05 19:48

使用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:

  1. Joining data form fname and lname columns and
  2. 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>
  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

2条回答

  • duanfan9859 duanfan9859 6年前

    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
    }
    

    ?>

    点赞 评论 复制链接分享
  • duannao8450 duannao8450 6年前

    Figured it out. Thanks for the help Benjamin

    <!DOCTYPE html>
    <html lang="en">
    <head>
    <title></title>
    </head> 
    <body>
    <form action="webfinal2.php"method="post">
        <?php
        $connection = mysqli_connect("mysql553.profrusso.com", "finalexam", "finalexam") or die ("could not connect to mysql");
        mysqli_select_db($connection, 'shoretoshore') 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 = $_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>";
        }
    ?>
    <input type="submit" name="submit" value="submit">
    </form>
    </body>
    </html>
    
    点赞 评论 复制链接分享