terribleCoder terribleCoder - 2 months ago 12
MySQL Question

Populating drop down list with first name and last name from MySQL query

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>

Answer

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\n", 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
}

?>

Comments