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:
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>
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
}
?>
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>