Submit button which, after selection in the dropdown menu, carries out an update in MySQL

PHP
1

I’ve been puzzling over for 3 days and can’t get the following problem solved (it’s only about the button that represents the problem). I have the following relations or tables in the database: Link

This code is then used to query and convert the date:

<? php
$ servername = "";
$ username = "";
$ password = "";
$ dbname = "";

// Create connection
$ conn = new mysqli ($ servername, $ username, $ password, $ dbname);
// Check connection
if ($ conn-> connect_error) {
 die ("Connection failed:". $ conn-> connect_error);
}
?>
<select>
<? php
$ sql = "SELECT FROM_UNIXTIME (date_from, '% Y-% m-% d% H:% i') as date_from
        FROM mantis_plugin_Calendar_events_table AS mEV
        INNER JOIN mantis_plugin_Calendar_relationship_table AS mRE ON mEV.id = mRE.event_id
        INNER JOIN mantis_bug_table AS mTA ON mRE.bug_id = mTA.id
        WHERE mTA.status = 10 ";

$ result = $ conn-> query ($ sql);

if ($ result-> num_rows> 0) {
 // output data of each row
 while ($ row = $ result-> fetch_assoc ()) {
   echo "<option>". $ row ["date_from"]. "</option>";
 }
} else {
 echo "0 results";
}
$ conn-> close ();
?>

As you can see, only those data should be retrieved that have status 10 in the table “mantis_bug_table”. Now the user should select a date from the dropdown menu and confirm this with a button. By pressing the button, the status should be set from 10 to 50 and thus no longer be displayed (on the website).

In summary: I can’t get the code for the button

I hope one of you can help me

Best regards, Tim

2

You form action attribute should point to a form processing script which will carry out the update to the database.
To identify which row in the table to update, you may want to select the row ID value in the initial query, and put that into the value attribute of the option tag.
This way the processing script can get the ID from the POST array and give it to the update query.
The actual submit button isn’t the hard part, it’s just a standard form submit input.

3

Many thanks for the answer. Since I’m relatively new to this area of ​​php, I want to ask if you could show me an example?

4

I don’t have the time for a full example, but can show example parts you could adapt.
First this needs to be in a form. Because the result makes changes to a database, I would use post as the form method, not get.
The form action (script that processes the submission) could be the very same file (processing code at the top) or an entirly different one, depending on your needs or structure.

The select input needs a name attribute, so you can find the data in the post array, call it what you like, for example date_id.

The select query you have will need the row ID adding (using whatever the ID column is named).

SELECT id, FROM_UNIXTIME (date_from, '% Y-% m-% d% H:% i') as date_from
        FROM mantis_plugin_Calendar_events_table AS mEV
        INNER JOIN mantis_plugin_Calendar_relationship_table AS mRE ON mEV.id = mRE.event_id
        INNER JOIN mantis_bug_table AS mTA ON mRE.bug_id = mTA.id
        WHERE mTA.status = 10

Then the IDs need to go into the options value.

 // output data of each row
 while ($ row = $ result-> fetch_assoc ()) {
   echo '<option value="' . $row['id'] . '">'. $ row ['date_from']. '</option>';
 }

You need a submit button.

<input type="submit" name="submit" value="Submit Data">

And that is your form.

Then to process the form, first you must check the request method for post.

if($_SERVER['REQUEST_METHOD'] == 'POST'){
     // Form processing goes here
}

You will want to retrieve the ID from the post data, the array key will be the name of the select input, eg: $_POST['date_id'].

$id = $id = preg_replace('#[^0-9]#iu', '', $_POST['date_id']) ; // Get the ID

The preg_replace will strip out anything that is not a number from the result, basic sanitisation of the data.
You should also verify submitted data, but that can be done with a check at the query stage, the query should either work or not, depending upon whether a valid ID was given to it.

The next step is your UPDATE query, where you will use a prepared statement to update the status on the row with the ID that matches the $id you have from the form.
You will want to wrap it in a check, to see if the update completed properly, or failed.

5

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.