Updating MySQLi from Dropdown not working

[Moorcam]

Hi all,

 

Trying to update two rows in a MySQL table using MySQLi and PHP with values from a bootstrap dropdown. No errors or anything (have error reporting turned on), just nothing is updated in the database.

 

Here is the update code (if anyone has a prepared version of it would love to have it).

 	if(isset($_POST['Update'])){//if the submit button is clicked
 	if(isset($_GET['issue_id']) && isset($_POST['issue_priority']) && isset($_POST['issue_status'])) {
	$sql="UPDATE maintenance_requests SET issue_status='$issue_status', issue_priority='$issue_priority' WHERE issue_id='".$_GET['issue_id']."'";
	$link->query($sql) or die("Cannot update");//update or error
	}
 	}
?>

And here is the form:

<form role="form" method="POST" action="" />
                                <table class="table table-striped table-bordered table-hover" id="bookings_table">
                                    <thead>
                                        <tr>
                                            <th class="text-center">Issue ID</th>
                                            <th class="text-center">Driver Name</th>
                                            <th class="text-center">Date Submitted</th>
                                            <th class="text-center">Fleet Number</th>
                                            <th class="text-center">Issue</th>
                                            <th class="text-center">Description</th>
                                            <th class="text-center">Priority</th>
                                            <th class="text-center">Status</th>
                                        </tr>
                                    </thead>
                                    <tbody>
                                        <tr class="odd gradeX">
                                            <td class="text-center"><?php echo $issue_id; ?></td>
                                            <td class="text-center"><?php echo $driver_name; ?></td>
                                            <td class="text-center"><?php echo $issue_date; ?></td>
                                            <td class="text-center"><?php echo $fleet_number; ?></td>
                                            <td class="text-center"><?php echo $issue_name; ?></td>
                                            <td class="text-center"><?php echo $issue_description; ?></td>
                                            <td class="text-center">
                                                <select name="issue_priority">
                                                    <option value="" hidden="hidden"><?php echo $issue_priority; ?></option>
                                                    <option value="High">High</option>
                                                    <option value="Medium">Medium</option>
                                                    <option value="Low">Low</option>
                                                </select>
                                            </td>
                                            <td class="text-center">
                                                <select name="issue_status">
                                                    <option value="" hidden="hidden"><?php echo $issue_status; ?></option>
                                                    <option Value="Open">Open</option>
                                                    <option Value="Pending">Pending</option>
                                                    <option value="Repaired">Repaired</option>
                                                </select>
                                            </td>
                                        </tr>
                                    </tbody>
<?php

        }
        $stmt -> close();
}
mysqli_close($link);
?>
                                </table>
                                <input type="submit" class="btn btn-default" name="Update" value="Update Issue">
                            </div>
                            </form>

Any help would be really appreciated. Have searched the forums and searched Google but nothing found.

 

Thanks heaps.

[benanamen]

 

You are POSTING your form so where does $_GET['issue_id'] come from?

[Moorcam]

It is because each dropdown is updated individually or as a whole. If I don't have that there, submitting one dropdown value updates all. So I geussed having the GET id  will allow for the form submission to just update the effected or chosen value.

[mac_gyver]

starting with the form, there is no hidden attribute for <option ...> tags, so, that part is meaningless and should be removed. the 1st option choice value should be an empty string, which you do have (so that you can detect when no choice was made) and the 1st option text should be a prompt telling the user to select one of the available choices.

 

next, overall, what is the goal you are trying to achieve? - to edit/update existing data. to accomplish this, wouldn't you use the existing data to populate form fields/pre-select options? to do so, you would output the selected attribute in the correct <option ...> tag. the easiest way of doing this is to have a list of the choices in an array (either by storing the choices in a database table and retrieving them or defining an array in your php code), loop over the list, and dynamically produce the <option ...>...</option> html, with the selected attribute in the correct <option ...> tag. you should also use integers for the option values. integers are easier to validate, take up less storage, and will result in the fastest queries. this will also let you do things like make your application work with different user languages simply by changing the display text, not all the data storage.

 

then for your form processing code, you need to validate all input data before using it and setup and output descriptive validation errors for each possible thing that can be wrong with the submitted data. if you do this. your code will either work or it will tell you why it isn't working. also, once the post method form has been submitted, all form fields (except for un-checked checkbox/radio fields) will be set, so using isset() for these fields will hide coding mistakes and needlessly clutter up your code.

 

converting code to use prepared queries is easy, provided you are using the php PDO extension -

 

1) remove any php variables, any concatenation dots, and single-quotes around the values and replace each one with a ? place-holder.

 

2) call the ->prepare() method.

 

3) call the ->execute(...) method, supplying an array to it consisting of the variables you removed in step #1.

 

4) for queries that return a result set, fetch the data using a fetch method appropriate to the type of data.

[Moorcam]

next, overall, what is the goal you are trying to achieve? - to edit/update existing data. to accomplish this, wouldn't you use the existing data to populate form fields/pre-select options? to do so, you would output the selected attribute in the correct <option ...> tag. the easiest way of doing this is to have a list of the choices in an array (either by storing the choices in a database table and retrieving them or defining an array in your php code), loop over the list, and dynamically produce the <option ...>...</option> html, with the selected attribute in the correct <option ...> tag. you should also use integers for the option values. integers are easier to validate, take up less storage, and will result in the fastest queries. this will also let you do things like make your application work with different user languages simply by changing the display text, not all the data storage.

 

then for your form processing code, you need to validate all input data before using it and setup and output descriptive validation errors for each possible thing that can be wrong with the submitted data. if you do this. your code will either work or it will tell you why it isn't working. also, once the post method form has been submitted, all form fields (except for un-checked checkbox/radio fields) will be set, so using isset() for these fields will hide coding mistakes and needlessly clutter up your code.

 

converting code to use prepared queries is easy, provided you are using the php PDO extension -

 

1) remove any php variables, any concatenation dots, and single-quotes around the values and replace each one with a ? place-holder.

 

2) call the ->prepare() method.

 

3) call the ->execute(...) method, supplying an array to it consisting of the variables you removed in step #1.

 

4) for queries that return a result set, fetch the data using a fetch method appropriate to the type of data.

I am trying to update those particular fields only in MySQL.

So, for example, the table is for vehicle issues. The mechanic logs in, looks at Maintenance Requests that are submitted by drivers. He fixes the changes the status of the issue from Open to Pending or Repaired using the values in the dropdown and also the priority of the issue to either High, Medium or Low using the values of that dropdown.

Once the form is submitted, it updates the fields in the database issue_priority and issue_status to the values the mechanic selected in the form. But this is not happening.

 

The hidden field is capturing the current value in the database and selecting the value in the dropdown that the database is valued to. This works.

Edited April 15, 2018 by DanEthical

[Moorcam]

Ok, did the following, which updates. However, if I update, for example, one dropdown for issue_priority, it updates in the DB but removes the others.

 	if(isset($_POST['Update'])){//if the submit button is clicked
 	    // Loop through each issue
    	foreach ($_POST['maintreq'] as $issueID => $issueArr) {
 	        // Prep statement
 	        $stmt = "UPDATE maintenance_requests SET issue_status = '".$issueArr['issue_status']."', issue_priority = '".$issueArr['issue_priority']."' WHERE issue_id = '".$issueID."'";
 	        // Run it
 	        $link->query($stmt) or die("Cannot update");//update or error
 	    }
 	}

[mac_gyver]

The hidden field is capturing the current value in the database and selecting the value in the dropdown that the database is valued to. This works.

 

 

no it's not selecting the correct choice in the dropdown and no this isn't working.

 

that isn't a hidden field and as already stated, isn't a valid attribute for the <option ...> tag.

 

what your form is doing is adding a 1st/default option choice with an empty string for the value (which is why data is being cleared in the database if you don't actually select one of the choices) and with the existing choice's text. while this can be made to work by setting the value attribute correctly, results in repeating the existing choice in the list of options. Keep It Simple - KISS.

 

the simple solution is to do what was suggested in the previous reply.

[Moorcam]

                                               <select class="form-control" name="maintreq[<?php echo $issue_id; ?>][issue_priority]">
                                       <?php echo '<option value="'.$issue_priority.'">'.$issue_priority.'</option>'; ?>
                                                    <option value="High">High</option>
                                                    <option value="Medium">Medium</option>
                                                    <option value="Low">Low</option>
                                                </select>
                                            </td>
                                            <td class="text-center">
                                                <select class="form-control" name="maintreq[<?php echo $issue_id; ?>][issue_status]">
                                        <?php echo '<option value="'.$issue_status.'">'.$issue_status.'</option>'; ?>
                                                    <option Value="Open">Open</option>
                                                    <option Value="Pending">Pending</option>
                                                    <option value="Repaired">Repaired</option>
                                                </select>

That better?

It works now though. Nothing getting deleted or anything and everything gets updated the way I want.