Can't figure out how to display current selection in a form option list

MySQL database
#1

I have 2 tables involved in which I am trying to show a list of available data to choose from, however I want the initial selected value to appear as the current value.

My tables are:
tableLines
tableMakers

On the web page, I am displaying a list of all data in tableLines which consists of:
line_id
line_name
line_make_id
line_foot_id
line_legal_id

The column line_make_id contains a value from tableMakers, which contains the following:
make_id
make_name

I want to have ability for someone to edit a row in tableLines. In this specific example, I want the ability to edit the value shown in line_make_id, which is just a number, but I want it to display the make_name in the list, instead of the line_make_id number. I am a little lost on how to do this.

Here is the code I have at this time:

       //execute the sql and get some results
        $sql="select * from tableLines;";
        $result1 = $conn->query($sql);

        //Page Title
        echo '<div class="userDisplayBox userDisplayBoxText">';
            echo "Welcome " . $loginName;
        echo '</div>';
                            
        //if the results have more than 1 row
        if ($result1->num_rows > 0) {
            echo '<table class="optionsMenuTable">';
                echo '<tr>';
                    echo '<td><b>line_id</b></td>';
                    echo '<td><b>line_name</b></td>';
                    echo '<td><b>line_make_id</b></td>';
                    echo '<td><b>line_foot_id</b></td>';
                    echo '<td><b>line_legal_id</b></td>';
                echo '</tr>';
                while($row1 = $result1->fetch_assoc()) {
                    echo '<tr>';
                        echo '<td>' . $row1["line_id"] . '</td>';

                        // Edit Option
                        if ($id == "e" . $row1["line_id"]) {
                            // edit form
                            echo '<td>';
                                echo '<form name="edit_newData" id="edit_newData" action="main-lines.php?id=c' . $userSelection .'" method="post">';
                                    echo '<input type="text" name="edit_newLineName" value="' . $row1["line_name"] . '">';
                                    echo '</td><td>';

                                    echo '<select name="edit_newMakeId" class="form-control">';
                                    $makeSql = "select * from tableMakers order by make_name;"; 
                                    $result2 = $conn->query($makeSql);
                                    if ($result2->num_rows > 0) {

                                        while($row2 = $result2->fetch_assoc()) {
                                            ?>
                                            <option value="<?php echo $row2["make_id"] ?>"<?php if ($row2["make_id"] == $edit_newMakeId) { ?> selected<?php } ?>><?php echo $row2["make_name"] ?></option>
                                            <?php
                                        }
                                                    
                                    }
                                    echo '</select>';
                                    echo '</td><td>';
                                    echo '<input type="text" name="edit_newMakeId" value="' . $row["line_make_id"] . '">';
                                    echo '</td><td>';
                                    echo '<input type="text" name="edit_newFootId" value="' . $row1["line_foot_id"] . '">';
                                    echo '</td><td>';
                                    echo '<input type="text" name="edit_newLegalId" value="' . $row1["line_legal_id"] . '">';
                                    echo '</td><td>';                                    
                                    echo '<input type="submit" value="Confirm Change">';
                                echo '</form>'; 
                            echo '</td>';
                        }                        
                        else {
                            echo '<td>' . $row1["line_name"] . '</td>';
                            echo '<td>' . $row1["line_make_id"] . '</td>';
                            echo '<td>' . $row1["line_foot_id"] . '</td>';
                            echo '<td>' . $row1["line_legal_id"] . '</td>';
                        }

The above code is displaying a list of make_names, but it is not doing what I want. In this case if the line_id is 3, then what is happening is that it is showing the make_name for make_id #3, where-as some how I need to do a join, but I’m not sure how to incorporate the following join:
select a.line_id, b.make_name from tableLines a, tableMakers b where a.line_make_id = b.make_id;

I then need the value of this to replace $edit_newMakeId in the line above, but I can’t figure out how to make this work.

Part of my problem, is that I can’t figure out how to query a specific item of data. I know if I can figure that out, I can figure out the above (albeit by nesting a query, within a query, within another query).

For example, in the above I have the following code:

                        else {
                            echo '<td>' . $row1["line_name"] . '</td>';
                            echo '<td>' . $row1["line_make_id"] . '</td>';
                            echo '<td>' . $row1["line_foot_id"] . '</td>';
                            echo '<td>' . $row1["line_legal_id"] . '</td>';
                        }

For line_make_id, I want it to show make_name instead. I have tried the following, which does not work (but I really don’t know what I’m doing):

                            //$sql4="select a.line_id, b.make_name from tableLines a, tableMakers b where a.line_make_id = b.make_id;";
                            //$result4 = $conn->query($sql4);
                            //$row4 = mysql_fetch_row($result4);

I then alter the above example (line_make_id) to:

                        echo '<td>' . $row4["make_name"] . '</td>';

The result is blank. If I someone can help me with this query, then I should be able to solve my problem up top.

#2

You need to simplify what you are trying to do and reduce the clutter in your code.

You are trying to display and edit the value of one row at a time. Don’t mess around trying to display values from the other rows too (this has actually resulted in invalid html, as you cannot spread a form around inside of a html table - you can put a complete html table inside of a form, you can put a complete form inside of a single html table cell, but you cannot put a form and its fields in multiple cells in a html table.)

The biggest things you can do that will help you to clean up your code are -

  1. Separate the database specific code (that knows how to query and retrieve data) from the presentation code (that knows how to produce the output from the data.) The way to do this is to put the database specific code above the start of your html document, retrieve all the data from any query into an appropriately named php variable, then just use the variable at the appropriate point in the html document. This will eliminate the need for variables ending in numbers (you will deal with the data from each query before going onto the next query), forces you to think/define what data you need, allow you more easily test your code, allow you to easily change the database extension you are using, and removes clutter from the html document.

  2. Don’t use php echo statements for each line in your html document. Treat the html document like it is a template and only have php code for things that are dynamic.

There’s at least two other things I noticed, 1) you are producing composite values in the links, i.e. either an ‘e’ or a ‘c’ followed by an id value. This is making extra work every time you process a value. Just use two different parameters in the link, and 2) in order to initially retrieve the current data from the database, but use the submitted form data when there are validation errors, you need to use a common internal variable in your code, that gets the submitted post data copied to in inside of the form processing logic and if is initially empty gets the current data from the database. You would use this common variable in the html document when you populate the form field(s).

Short answer, you actually have the value you need to select the correct option (just noticed it, but its part of a duplicate named form field, which also won’t work), but it’s buried in the clutter you have. It would be better if you first simplified everything so that it will be easier to see what the program logic is actually doing.

#3

I appreciate the feedback, but I’m just coding the way I learnt to in the class I took. Changing the structure is beyond my ability right now. I appreciate you helping me try to learn it, but sadly most of what you wrote just doesn’t register in my brain. I’m looking more for code examples than anything else.

#4

I think I got it working now. I messed around with this line:

<?php if ($row2["make_id"] == $row1["line_make_id"]) { ?> selected
Sponsor our Newsletter | Privacy Policy | Terms of Service