Combobox show information selected from database

ciresuark · May 19, 2020

I have a form gathering data from the database. There is one field 'country' which is a combo box. The combo box is 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. I think I'm close in my code, but unsure. This is a playground to so I can implement on our other site.

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

gw1500se · May 19, 2020

Did you look at the resulting page source to check for any HTML errors?

ciresuark · May 19, 2020

Yes, there are no errors in the code. The top of the page has the var_dump:

array(12) { ["countryCode"]=> string(2) "US" ["countryName"]=> string(13) "United States" ["customerID"]=> string(4) "1045" ["firstName"]=> string(9) "Priscilla" ["lastName"]=> string(5) "Smith" ["address"]=> string(8) "Box 1979" ["city"]=> string(6) "Marion" ["state"]=> string(2) "OH" ["postalCode"]=> string(5) "43305" ["phone"]=> string(14) "(800) 555-1669" ["email"]=> string(18) "psmith@example.com" ["password"]=> string(6) "sesame" }

I am tried to use this to determine if my query was pulling the information. The section pulling this is:

 <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>

and I think I need to update:

<?php echo isset($customerID) == $selectedCountry['customerID'] ? ' selected':''?>

It isn't right as the combo box isn't highlighting "United States".

gw1500se · May 19, 2020

If you looked for an error you won't find it. I meant look at the generated HTML source and see if the structure and data for the option box is there and correct. If it is not, you should be able to see what was built and what is missing/wrong.

Barand · May 19, 2020

Build you country combo options from the country table. The value for each option should be the id/code for the country

Here's an example

<?php
$res = $db->query("SELECT customer_id
                        , fname
                        , lname
                        , country
                   FROM customer
                   LIMIT 10     
                  ");
$tdata = '';
foreach ($res as $row) {
    $tdata .= "<tr>
                    <td>{$row['fname']} {$row['lname']}</td>
                    <td style='text-align: center;'><select name='country'>" . countryOptions($db, $row['country']) . "</select><td>
               </tr>";
}

 
function countryOptions(PDO $db, $current)
{
    $opts = "<option value=''>- select country -</option>\n";
    $res = $db->query("SELECT country_id    as id
                            , country_name  as name
                       FROM country
                       ORDER BY name     
                      ");
    foreach ($res as $row) {
        $sel = $row['id'] == $current ? 'selected' : '';
        $opts .= "<option $sel value='{$row['id']}'>{$row['name']}</option>\n";
    }
    return $opts;
}  
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Example</title>
</head>
<body>
    <table style='width: 600px;'>
        <tr><th>Name</th><th>Country</th></tr>
        <?=$tdata?>
    </table>
</body>
</html>

Example data and ouput

Table: customer                                           Table: country
+-------------+----------+-----------+---------+          +------------+--------------+
| customer_id | fname    | lname     | country |          | country_id | country_name |
+-------------+----------+-----------+---------+          +------------+--------------+
|           1 | Wanda    | Denning   |       1 |          |          1 | England      |
|           2 | Tom      | Westbrook |       2 |          |          2 | Scotland     |
|           3 | Georgina | Jones     |       3 |          |          3 | Wales        |
|           4 | Hannah   | Wentworth |       1 |          |          4 | N. Ireland   |
|           5 | Roy      | Egon      |       4 |          +------------+--------------+
|           6 | Len      | Elridge   |       1 |
|           7 | Sam      | Patterson |       2 |
|           8 | Charles  | Knight    |       1 |
|           9 | Gerald   | Watts     |       1 |
|          10 | Tom      | Bowles    |       4 |
+-------------+----------+-----------+---------+

image.png.f57550b03d9d1e321871d0d99df49418.png

ciresuark · May 19, 2020

I don't know if it makes it more difficult, but the countries are stored in a different table with the country abbreviation which is why I have the join.

In the results you show, are you able to see the other options if a user needed to make updates?

ciresuark · May 19, 2020

33 minutes ago, gw1500se said:

If you looked for an error you won't find it. I meant look at the generated HTML source and see if the structure and data for the option box is there and correct. If it is not, you should be able to see what was built and what is missing/wrong.

I did what you mentioned and I am receiving an Undefined index: customer ID on line 94.

Barand · May 19, 2020

Of course - all the options are in the combo but the relevant one is displayed.

Barand · May 19, 2020

Is line 94 this one..

 <option value="<?php echo $country['customerID']; ?>

where you are trying to use "customer_id" as the value for your country, instead of the country_id/code?

ciresuark · May 19, 2020

1 minute ago, Barand said:
Is line 94 this one..
 <option value="<?php echo $country['customerID']; ?>
where you are trying to use "customer_id" as the value for your country, instead of the country_id/code?

I'm not sure why I did it that way. I changed it to countryName. The countries table only has countryCode and CountryName. There are no ids. After I changed it to

<option value="<?php echo $country['countryName']; ?>

the error went away.

This is what the inspected element looks like.

<option value="Sao Tome and Princip                           selected                            ">Sao Tome and Princip</option>

If I am seeing this right, I need to move the " to have the selected outside the quote. Right now all options are showing as selected.

Barand · May 19, 2020

The value should be the country code (which is what is in your customer table)

ciresuark · May 19, 2020

Agreed. Right now as I look at this I think the primary issue is I am not reading the input from the "customer". The start of the foreach loop looks at countries which is: SELECT * From countries (sql2), which lists all the countries available within the db and makes them available within the combobox.

<?php
                        foreach ($countries->fetchAll() as $country): ?>
                            <option value="<?php echo $country['countryName']; ?>
                          <?php echo isset($selectedCountry) == $selectedCountry['countryName'] ? ' selected':''?>
                            "><?php echo $country['countryName']; ?></option>

                        <?php endforeach;

                        ?>

As I am looking through this I think the code line below is my issue. As I'm looking at the selected country twice and not gathering what the country should be to verify it is correct. Does that sound right?

 <?php echo isset($selectedCountry) == $selectedCountry['countryName'] ? ' selected':''?>

In non-php I want to check the selected country from the db against the customerID's country and highlight it.

$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);

ciresuark · May 19, 2020

After some playing and talking it out loud to myself I was able to figure it out. The code block should be like below.I had a double quote in the wrong spot, and the statement off a little bit.

foreach ($countries->fetchAll() as $country): ?>
                            <option value="<?php echo $country['countryName']; ?>"
                          <?php echo isset($selectedCountry) && $selectedCountry['countryName'] == $country['countryName'] ? ' selected':''?>
                            ><?php echo $country['countryName']; ?></option>

                        <?php endforeach;

Thank you for the help.

Sign In

Combobox show information selected from database

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information