In an UPDATE operation, need help to set default value of a "lookup dropdown" based on value in table

[excelmaster]

Hello all,

 

For the UPDATE portion of my CRUD WebApp what I would like to do is to bring in (and display) the values (of a selected row) from my transaction table.

 

This is working just fine for all fields which are of the "input type". The problem I'm having is with two fields which are of the "select type" i.e. dropdown listboxes.

 

For those two fields, I would like to bring in all the valid choices from the respective lookup/master tables, but then have the default/selected value be shown based on what's in the transaction table. The way I have it right now, those two fields are showing (and updating the record with) the very first entry's in the two lookup tables/select query.

 

The attached picture might make things a little bit clearer. You'll notice in the top screenshot that the first row (which is the one I'm selecting to update) has a "Store Name" = "Super Store" and an "Item Description" = "Old Mill Bagels". Now, when I click the "update" botton and I'm taken to the update screen, the values for those two fields default to the very first entries in the SELECT resultset i.e. "Food Basics" and "BD Cheese Strings". Cricled in green (to the top-left of that screenshot) is the result of an echo that I performed, based on the values that are in the transaction record.

 

I cannot (for the life of me) figure out how to get those values to be used as default/selected values for the two dropdown's...so that if a user does not touch those two dropdown fields, the values in the transaction table will not be changed.

 

Your help will be greatly appreciated.

 

Here's a portion of the FORM code:

 

 

            <form class="form-horizontal" action="update.php?idnumber=<?php echo $idnumber?>" method="post">
                <?php
                    // Connect to Store_Name (sn) table to get values for dropdown
                    $pdo = Database::connect();
                    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
                    $sql = "SELECT DISTINCT store_name FROM store_master ORDER BY store_name ASC";
                    $q_sn = $pdo->prepare($sql);
                    $q_sn->execute();
                    $count_sn = $q_sn->rowCount();        
                    $result_sn = $q_sn->fetchAll();
                    Database::disconnect();

                    // Connect to Item_Description (id) table to get values for dropdown                    
                    $pdo = Database::connect();
                    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
                    $sql = "SELECT DISTINCT product_name FROM product_master ORDER BY product_name ASC";
                    $q_id = $pdo->prepare($sql);
                    $q_id->execute();
                    $count_id = $q_id->rowCount();        
                    $result_id = $q_id->fetchAll();
                    Database::disconnect();

                    foreach($fields AS $field => $attr){
                        $current_store_name = $values['store_name'];
                        $current_item_description = $values['item_description'];
                        
                        //Print the form element for the field.
                        if ($field == 'store_name')
                            {
                                echo $current_store_name;
                                echo '<br />';
                                echo $current_item_description;
                            
                                echo '<div class="control-group">';
                                    echo "<label class='control-label'>{$attr['label']}: </label>";
                                    echo '<div class="controls">';                            
                                        //Echo the actual input. If the form is being displayed with errors, we'll have a value to fill in from the user's previous submission.
                                        echo '<select class="store-name" name="store_name">';                                            
                                            // echo '<option value="">Please select...</option>';
                                            foreach($result_sn as $row)
                                                {
                                                    echo "<option value='" . $row['store_name'] . "'>{$row['store_name']}</option>";
                                                }
                                                // $row['store_name'] = $current_store_name;
                                        echo "</select>";

                                    echo '</div>';
                                echo '</div>';
                            }
                        elseif ($field == 'item_description')
                            {
                                echo '<div class="control-group">';
                                    echo "<label class='control-label'>{$attr['label']}: </label>";
                                    echo '<div class="controls">';                            
                                        echo '<select class="item-desc" name="item_description">';
                                            // echo '<option value="">Please select...</option>';
                                            foreach($result_id as $row)
                                                {
                                                    echo "<option alue='" . $row['product_name'] . "'>{$row['product_name']}</option>";
                                                }    
                                        echo "</select>";
                                    echo '</div>';
                                echo '</div>';                                        
                            }
                        else
                            {
                                echo '<div class="control-group">';
                                    echo "<label class='control-label'>{$attr['label']}: </label>";
                                    echo '<div class="controls">';                            
                                        //Echo the actual input. If the form is being displayed with errors, we'll have a value to fill in from the user's previous submission.
                                        echo '<input type="text" name="'.$field.'"' . (isset($values[$field]) ? ' value="'.$values[$field].'"' : '') . ' /></label>';                            
                                    echo '</div>';
                                echo '</div>';                                        
                            }

 

 

And here's some other declarations/code which I think might be required.

 

 

    $fields = array(
        'store_name'         => array('label' => 'Store Name',
                                     'error' => 'Please enter a store name'),
        'item_description'     => array('label' => 'Item Description',
                                     'error' => 'Please enter an item description'),
        'qty_pkg'             => array('label' => 'Qty / Pkg',
                                     'error' => 'Please indicate whether it\'s Qty or Pkg'),
        'pkg_of'             => array('label' => 'Pkg. Of',
                                     'error' => 'Please enter the quantity'),
        'price'             => array('label' => 'Price',
                                     'error' => 'Please enter the price'),
        'flyer_page'         => array('label' => 'Flyer Page #',
                                     'error' => 'Please enter the flyer page #'),
        'limited_time_sale' => array('label' => 'Limited Time Sale',
                                     'error' => 'Please enter the days for limited-time-sale'),
        'nos_to_purchase'     => array('label' => 'No(s) to Purchase',
                                     'error' => 'Please enter the No. of items to purchase')
    );    
 
...
...
....
        {
            // If [submit] isn't clicked - and therfore POST array is empty - perform a SELECT query to bring in
            // existing values from the table and display, to allow for changes to be made
            $pdo = Database::connect();
            $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            $sql = "SELECT * FROM shoplist where idnumber = ?";
            $q = $pdo->prepare($sql);
            $q->execute(array($idnumber));
            $values = $q->fetch(PDO::FETCH_ASSOC);

            if(!$values)
                {
                    $error = 'Invalid ID provided';
                }        
            Database::disconnect();
        }

 

 

If there's anything I've missed please ask and I'll provide.

 

Thanks

 

[excelmaster]

Oops...sorry, forgot to attach screenshot...which is now attached to this post:

 

 

[Psycho]

Um, I stopped reading after the second paragraph. This is not a difficult problem to solve. Query the possible values for the select list. Then while building the options check if any match the currently selected value. If so, make that option selected. You would want to create a function for this.

 

I do have one question regarding the SELECT queries you are using above. Why is there a DISTINCT in the queries for stores and products? Those are the master tables for those respective record types, why would there be duplicates? Second, why are you not pulling the IDs for those records and using those as the values in the select lists? Those records should have a primary ID field which is numeric. Those numeric IDs would then be stored in the shoplist table as foreign keys. You don't store the names/values of those records in the associative tables.

 

There are lots of other issues such as connecting and disconnecting from the database multiple times. Just connect once. You don't need to prepare a query that has no placeholder values. Also, the flow of the logic seems really odd. I see what you are trying to do with the array of fields, but will box you into a corner. A field can have many different types of errors. A single error message will not suffice when you account for all of them. Plus, you're already putting logic into that foreach loop since you want it to do something different from some fields and not others. You are over complicating the logic. If you want some fields to do the same things, create functions and explicitly call them when creating those fields.

 

Here is a complete rewrite of your code in a much more logical format. It won't work out of the box as there were some things not provided above. But, it shouldn't take too much to put in what you need.

 

 

<?php
 
//Function to create select list options and set selected value if passed
function createSelectOptions($optionsAry, $selectedValue=false)
{
    $optionsHTML = '';
    foreach($optionsAry as $option)
    {
        $selected = ($option['value']==$selectedValue) ? ' selected="selected"' : '';
        $optionsHTML .= "<option value='{$option['value']}'{$selected}>{$option['label']}</option>\n";
    }
    return $optionsHTML;
}
 
//Since you want the format for the input fields to be the same,
//You can create a function to create them
function standardInputField($fieldLabel, $fieldName, $fieldValue)
{
    $fieldHTML  = "<div class='control-group'>\n";
    $fieldHTML .= "  <label class='control-label'>{$fieldLabel}</label>\n";
    $fieldHTML .= "  <div class='controls'>\n";  
    $fieldHTML .= "    <input type='text' name='{$fieldName}' value='{$fieldValue}' />\n";
    $fieldHTML .= "  </div>\n";
    $fieldHTML .= "</div>\n";
    return $fieldHTML;
}
 
 
//Connect to database
$pdo = Database::connect();
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
 
// If [submit] isn't clicked - and therfore POST array is empty - perform a SELECT query to bring in
// existing values from the table and display, to allow for changes to be made
if(!isset($_POST['submit']))
{
    $sql = "SELECT * FROM shoplist where idnumber = ?";
    $query = $pdo->prepare($sql);
    $query->execute(array($idnumber));
    $values = $query->fetch(PDO::FETCH_ASSOC);
 
    if(!$values)
    {
        $error = 'Invalid ID provided';
    }
}
 
//Set values for form fields to prev. selected values or defaults
$current_store_name = isset($values['store_name']) ? $values['store_name'] : '';
$current_item_description = isset($values['item_description']) ? $values['item_description'] : '';
$qty_pkg = isset($values['qty_pkg']) ? $values['qty_pkg'] : '';
$pkg_of = isset($values['pkg_of']) ? $values['pkg_of'] : '';
$price = isset($values['price']) ? $values['price'] : '';
$flyer_page = isset($values['flyer_page']) ? $values['flyer_page'] : '';
$limited_time_sale = isset($values['limited_time_sale']) ? $values['limited_time_sale'] : '';
$nos_to_purchase = isset($values['nos_to_purchase']) ? $values['nos_to_purchase'] : '';
 
//Query stores and create select field options
$sql = "SELECT store_id AS value, store_name AS label FROM store_master ORDER BY store_name ASC";
$result = $pdo->query($sql);
$stores = $result->fetchAll();
$store_options = createSelectOptions($stores, $values['store_name']);
 
//Query products and create select field options
$sql = "SELECT product_id AS value, product_name AS label FROM product_master ORDER BY product_name ASC";
$result = $pdo->query($sql);
$products = $result->fetchAll();
$product_options = createSelectOptions($products);
 
//Add logic to create error values for fields as needed (the error or empty string)
//for each field and store in unique variables to be echo'd within the form
 
?>
 
<form class="form-horizontal" action="update.php?idnumber=<?php echo $idnumber?>" method="post">
    <?php $current_store_name; ?>
    <br />
    <?php echo $current_item_description; ?>
    <div class="control-group">
        <label class='control-label'>Store Name</label>
        <div class="controls">
            <select class="store-name" name="store_name">
                <?php echo $store_options; ?>
            </select>
        </div>
    </div>
 
    <div class="control-group">
        <label class='control-label'>Item Description</label>
        <div class="controls">
            <select class="item-desc" name="item_description">
            <option value="">Please select...</option>
                <?php echo $product_options; ?>
            </select>
        </div>
    </div>
 
    <?php echo standardInputField('Qty / Pkg', 'qty_pkg', $qty_pkg); ?>
    <?php echo standardInputField('Pkg. Of', 'pkg_of', $pkg_of); ?>
    <?php echo standardInputField('Price', 'price', $price); ?>
    <?php echo standardInputField('Flyer Page #', 'flyer_page', $flyer_page); ?>
    <?php echo standardInputField('Limited Time Sale', 'limited_time_sale', $limited_time_sale); ?>
    <?php echo standardInputField('No(s) to Purchase', 'nos_to_purchase', $nos_to_purchase); ?>
 

Edited May 29, 2014 by Psycho

[excelmaster]

@Psycho: Thanks for being so kind and helpful for both, pointing out the flaws in my code and database design, as well as for providing advice and an awesome piece of code to help me get started in improving and streamlining my messy/over-complicated code. The code you provided has certainly over-exceeded my expectations, and to you I'm grateful for that.

 

>> I do have one question regarding the SELECT queries you are using above. Why is there a DISTINCT in the queries for stores and products? Those are the master tables for those respective record types, why would there be duplicates?

 

You're right!....My bad, those DISTINCTs were left there as an oversight (when I copied over that code from another PHP file). The master/lookup tables certainly have just one record each for a store and item respectively, and no duplicates.

 

>> Second, why are you not pulling the IDs for those records and using those as the values in the select lists? Those records should have a primary ID field which is numeric. Those numeric IDs would then be stored in the shoplist table as foreign keys

 

Again, bad database design on my part....I know! I will change my "shoplist" table to store the ID's from the lookup tables, instead of the names/descriptions.

 

>> There are lots of other issues such as connecting and disconnecting from the database multiple times

 

Fully agree with you, and thanks for pointing out those issues. I'm aware of some of them, but either I don't know how to how to tackle them, or I had planned to tackle them once I had a better understanding of PHP and MySQL.

 

>> Here is a complete rewrite of your code in a much more logical format. It won't work out of the box as there were some things not provided above. But, it shouldn't take too much to put in what you need.

 

This is exactly what I had in mind (to do at a later stage), but you have just given me a much-wanted head-start. I will certainly try to first understand the code, and then apply/tweak it as required.

 

Cheers

[excelmaster]

@Psycho: Using the awesome code (and suggestions) you provided me yesterday I was not only able to get the functionality I had originally requested (i.e. displaying the stored value in a lookup dropdown list) ,working like a dream, but I was also able to somewhat normalize my database, albeit very marginally for now. I will certainly take a second/better look at it later (just started reading up on "Database Normalization"), to see if I can normalize it further.

 

With that being said, I do not quite have the know-how (and this is perhaps a very simple/stupid question to ask) to update the transaction table (shoplist), once a different "store name" and/or "item description" is selected from the dropdown list in the form.

 

In other words, and I'm thinking aloud here (so to speak)....I would probably need to take/use the newly-selected "store name" and find/get the corresponding "store id" from the "store_master" table, and then write that value out to the shoplist/transaction file. Is that how you would do it, or am I making it overly complex, as usual?

 

If the above is indeed the way to do it, then would I have to write a SQL statement (similar to the following) and execute it somewhere within the IF block that checks for !empty($_POST)?

 

 

 

SELECT store_id FROM store_master WHERE store_name = $_POST['store_name'];

[Psycho]

Let me give you a generalization of how I approach creating/updating records. First, you should absolutely separate various processes so they can be repurposed. I tried to do that somewhat in the code I provided. But, ideally, you want your functions, database connections, page logic, and output all in separate files. I tried to break them out in my example, but left them on a single page. As I explain below, this will make sense.

 

So, if I need to give the user the ability to create/edit a record I will first start by creating the logic to be run when initiating a New or Edit operation. This could be one script or two separate script, but they would both use/include the logic for the DB connection and necessary functions. Then I would create ONE form for both add and edit processes. That form will include a hidden field for the record ID. If the user selects an option to create a new record I will leave the ID field empty and set any default values on the form. If the user has selected to edit a record, I will set the ID field accordingly along with all currently saved values in the other fields.

 

Then, when the user clicks SAVE on the form it will post to the same processing page. That page will use the ID field to determine if it should be processed as a create or edit. There may be different validations, permission checks, etc. that need to be run for an add vs. an edit. But, you can typically use a single query for both add and edit operations. If you set up the database correctly, the ID field will be set as the primary ID and unique in the database. Then you can use the "ON DUPLICATE KEY UPDATE" clause in an INSERT query. Here is an example:

 

INSERT INTO table_name
    (id, field1, field2, field3, field4)
VALUES
    ('$id', '$field1Value', '$field2Value', '$field3Value', '$field4Value')
ON DUPLICATE KEY UPDATE
    field1 = VALUES(field1),
    field2 = VALUES(field2),
    field3 = VALUES(field3)

 

If the ID passed to the function does not currently exist or is empty, then the INSERT part of the query is run and a new record is created. However, if the ID already exists in the table then the UPDATE part of the query will be run. In the example above, it would only update the values for fields 1-3 passed and not field4.

[excelmaster]

That's an amazing approach indeed, and certainly one that I will attempt to emulate, albeit slowly but surely.

 

>> But, you can typically use a single query for both add and edit operations. If you set up the database correctly, the ID field will be set as the primary ID and unique in the database. Then you can use the "ON DUPLICATE KEY UPDATE" clause in an INSERT query. Here is an example:

 

Oh wow! I didn't know that was even possible...to use one query to perform both, add as well as update (using the ON DUPLICATE KEY UPDATE" clause. Again, I till try to incorporate that methodology into my app.

 

If I may ask, would you have a bare-bones setup available (of your PHP files for add & update, read, delete etc.), that I could use as a template/starting-point...and perhpas, more so to learn from, if you don't mind sharing it, that is?

 

So...just getting back to my question in post # 5 (and assuming I'm leaving things as-is for the time being), would I have to do the query (sanitizing the input field, of course) in order to update the store_id and item_id fields in the shoplist/transaction file..of is there a better/different way to do it?

 

Thanks.