I have a form gathering data from the database. There is one field ‘country’ which is a combo box. I have the combo box populated from the countries table which joins the customers table to provide the country specific to the customer. I’m trying to have the combo box display the country associated with the customer.
<?php
require_once('database.php');
$sql2 = "SELECT * From countries
";
$countries = $db->query($sql2);
if(isset($_GET['customerID'])) {
$customerID = filter_input(INPUT_GET, 'customerID', FILTER_SANITIZE_NUMBER_INT);
$sql = "SELECT * FROM customers WHERE customerID =$customerID ";
//$sql2 = "SELECT * From countries
//INNER JOIN customers ON countries.countryCode=customers.countryCode
//WHERE customers.customerID = $customerID";
$stmt = $db->query($sql);
}
if(isset($_GET['customerID'])){
$customerID = filter_input(INPUT_GET, 'customerID', FILTER_SANITIZE_NUMBER_INT);
$countryQuery = "
{$sql2} INNER JOIN customers ON countries.countryCode = customers.countryCode WHERE customers.customerID = $customerID";
$countriesQuery = $db->prepare($countryQuery);
$countriesQuery->execute(['customerID' => $_GET['customerID']]);
$selectedCountry = $countriesQuery->fetch(PDO::FETCH_ASSOC);
var_dump($selectedCountry);
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<!-- the head section -->
<head>
<title>My Guitar Shop</title>
<link rel="stylesheet" type="text/css" href="main.css" />
</head>
<!-- the body section -->
<body>
<div id="page">
<div id="header">
<h1>SportsPro Technical Support</h1>
<p>Sports management software for the sports enthusiast.</p></h1>
</div>
<div id="main">
<h1>View/Update Customer</h1>
<form action="update.php" method="get" >
<?php
?>
<div id="content">
<!-- display a table of products -->
<h2>Customers</h2>
<form name="customerInfo">
<?php foreach ($stmt as $cust) { ?>
<div>
<label>First Name</label>
<input type="text" name="name" class ="form-control" value ="<?php echo $cust['firstName']; ?>">
</div><br>
<div>
<label>Last Name</label>
<input type="text" name="name" class ="form-control" value ="<?php echo $cust['lastName']; ?>">
</div><br>
<div>
<label>Address</label>
<input type="text" name="address" class ="form-control" value ="<?php echo $cust['address']; ?>">
</div><br>
<div>
<label>City</label>
<input type="text" name="city" class ="form-control" value ="<?php echo $cust['city']; ?>">
</div><br>
<div>
<label>State</label>
<input type="text" name="state" class ="form-control" value ="<?php echo $cust['state']; ?>">
</div><br>
<form action="update.php" method="get">
<select name="country">
<option value=""></option>
<?php
foreach ($countries->fetchAll() as $country): ?>
<option value="<?php echo $country['customerID']; ?>
<?php echo isset($customerID) == $selectedCountry['customerID'] ? ' selected':''?>
"><?php echo $country['countryName']; ?></option>
<?php endforeach;?>
</select>
</form>
</div>
<br>
<div>
<label>Country Code</label>
<input type="text" name="countryCode" class ="form-control" value ="<?php echo $cust['countryCode']; ?>">
</div><br>
<div>
<label>Zip Code</label>
<input type="text" name="postalCode" class ="form-control" value ="<?php echo $cust['postalCode']; ?>">
</div><br>
<div>
<label>Email </label>
<input type="text" name="email" class ="form-control" value ="<?php echo $cust['email']; ?>">
</div><br>
<div>
<label>Phone Number </label>
<input type="text" name="phone" class ="form-control" value ="<?php echo $cust['phone']; ?>">
</div><br>
<div>
<label>Password </label>
<input type="text" name="password" class ="form-control" value ="<?php echo $cust['password']; ?>">
</div><br>
<div>
<?php }
?>
</div>
</div>
<form action="UpdateCustomer.php" method="get">
<input type="submit" name="data" value="Update_Data"></input>
</form>
<div id="footer">
<p>
© <?php echo date("Y"); ?> SportsPro, Inc.
</p>
</div>
</div><!-- end page -->
</body>
</html>