Update MySQL Database based on option selected from an HTML dropdown menu

Question

I'm working on a website for Pokemon Go, and as part of the site I'm making a custom CMS that allows me to insert/alter data for displaying on various webpages. I want to be able to toggle a Raid Boss to "inactive" based on the boss selected in a drop down menu.

The following is the HTML code for the form where the dropdown is located. Basically I query the database of Raid Bosses for all bosses that are currently active, or "1", and append them to the option list:

<h5 style="text-decoration: underline;">Set Active Raid Boss to Inactive</h5>
    <form class = "form-group" method = "post">
          <select>
          <option disabled selected value> -- select an option -- </option>
          <?php
               $prep_stmt = $conn->prepare("SELECT * FROM raids WHERE isActive = 1");
               $prep_stmt->execute();
               $row = $prep_stmt->fetchAll();
               $count = $prep_stmt->rowCount();

               for($x = 0; $x < $count; $x++) {
                    echo "<option name ='".$row[$x]['name']."' value='".$row[$x]['name']."'>". $row[$x]['name']. "</option>";
               }
          ?>
          </select>
          <input name = "ToggleToInactiveRaid" type = "submit" value="Submit"/>
   </form>

The following is the PHP code for the $_POST request when the button is clicked:

if(isset($_POST['ToggleToInActiveRaid'])){
    $raids = $conn->prepare("SELECT * FROM raids WHERE isActive = 1");
    $raids->execute();

    $raidList = $raids->fetchAll();
    $count = $raids->rowCount();

    for($i = 0; $i < $count; $i++){
        if ($raidList[$i]['name'] == $_POST['name']){
            $stmt = $conn->prepare("UPDATE raids SET isActive = 0 WHERE raids.name = ".$_POST['name']);
            $stmt->execute();
        }
    }
}

For some reason, the POST request is never detected. I know this because I was echoing a dummy variable in the form section that would only be displayed if its value was updated in the POST request if block, and that variable was never dumped onto the page. I have other forms on the CMS page where the PHP code is executed when the respective button is clicked. But this one is giving me quite a bit of trouble and I really don't know why. If anyone can help me out, that would be awesome. Thanks!

Does the code where $_POST['ToggleToInActiveRaid'] is processed reside in the same file as where its submitted? — Nick Gatzouli
– Nick Gatzouli, Commented Jun 4, 2019 at 18:48
@NickGatzos Yes, all POST request processing is located at the top of the CMS page. This is the only POST request not being hit — danielschnoll
– danielschnoll, Commented Jun 4, 2019 at 18:49
@user3783243 if its not how they should be written, I'm not sure why its worked so far, and only giving me issues with this one case. Regardless, the issue isn't the prepared statements but the post request not being hit — danielschnoll
– danielschnoll, Commented Jun 4, 2019 at 18:52
@danielschnoll It would work, but is insecure and could get your database erased. Placeholders (?) should be used in the SQL and the values should be bound in the execute or a bindparam call. See php.net/manual/en/pdostatement.execute.php — user3783243
– user3783243, Commented Jun 4, 2019 at 19:06

Peter van der Wal · Accepted Answer · 2019-06-04 19:03:19Z

3

A) You didn't give your select-box a name. So it won't show up in your $_POST-array. Change <select> to <select name="name">

B) (Assuming isActive can only be 0 or 1) you don't have to loop over all the rows in the database to de-activate just one. Just use (fixed it with prepared statement)

$stmt = $conn->prepare("UPDATE raids SET isActive = 0 WHERE raids.name = :name");
$stmt->execute(array(':name' => $_POST['name']));

It will do the same either with or without your SELECT * FROM raids WHERE isActive = 1; for(...). In both cases either that name will exists in the raids-table (and be updated to 0) or doesn't exists (and nothing will be updated). Skipping the SELECT, for(...) will just make your code more efficient.

answered Jun 4, 2019 at 19:03

Peter van der Wal

12k2 gold badges24 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

danielschnoll Over a year ago

I made the changes you proposed, and the POST request is still never hit. I'm attempting to dump the variable again in the area with the form, and it doesn't print.

Peter van der Wal Over a year ago

Does a var_dump($_POST) right before your if-statement give you any relevant info?

danielschnoll Over a year ago

Yeah it gave me this array(2) { ["name"]=> string(9) "Cresselia" ["ToggleToInactiveRaid"]=> string(6) "Submit" }

danielschnoll Over a year ago

Ok, I see what the problem is now. The button name was "ToggleToInactiveRaid" and the if statement condition was "ToggleToInActiveRaid". The capital A was causing it to miss the block entirely. Thanks for the assistance!

Peter van der Wal Over a year ago

PHP is quite strict with capitals :) Glad I could help.

Collectives™ on Stack Overflow

Update MySQL Database based on option selected from an HTML dropdown menu

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related