CODEWITHSUNDEEP
phphtmlmysqlforms
Epsilon
Epsilon

Reputation: 43

PHP save dropdown value to mysql

First of all let me say, I am new to this and my code is probably laughable to those of you who know what you're doing...

I am trying to retrieve data from my mysql database and display the information in a matching dropdown list. I have this part working with some ugly basic code.

$url = $_SERVER["REQUEST_URI"];
$url_path = parse_url($url, PHP_URL_PATH);
$devid = pathinfo($url_path, PATHINFO_BASENAME);

$con = mysqli_connect("localhost","xxxxxx","xxxxxx","xxxxxxxx");
  //Run a query
  $result = mysqli_query($con,"SELECT type FROM `xxxxx`.`xxxxxx` WHERE device_id='$devid'");


$pulldown1 = '<select name="extension_type">';

while($row = mysqli_fetch_array($result))
{
if($row['type'] == "Unlimited")
{
$pulldown1 .= "<option selected value=\"Unlimited\">Unlimited Extension</option>
<option value=\"Metered\">Metered Extension</option>
<option value=\"Virtual\">Virtual Extension</option>
\n";
}
if($row['type'] == "Metered"){
$pulldown1 .= "<option selected value=\"Metered\">Metered Extension</option>
<option value=\"Unlimited\">Unlimited Extension</option>
<option value=\"Virtual\">Virtual Extension</option>
\n";
}
if($row['type'] == "Virtual"){
$pulldown1 .= "<option selected value=\"Virtual\">Virtual Extension</option>
<option value=\"Unlimited\">Unlimited Extension</option>
<option value=\"Metered\">Metered Extension</option>
\n";
}

}
$pulldown1 .= '</select>';

echo $pulldown1;

mysqli_close($con);

Now I'd like to be able to CHANGE the value and have it save the corresponding value and update that column in the database. This is where I am stuck. Can someone guide me in the right direction, please?

Upvotes: 0

Views: 461

Answers (2)

Revent
Revent

Reputation: 2109

Modified from the PHP documentation on php.net:

<?php
if ( !empty($_POST['extension_type']) )
{
   // Your MySQL connection code should be at the top of the script.
   $query = "UPDATE `xxxxx` SET `extension_type` = ? WHERE `[your_id_field_here]` = ?";
   /* Prepared statement, stage 1: prepare */
   if ( !($stmt = $mysqli->prepare($query) ) {
       echo "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error;
   }
   /* Prepared statement, stage 2: bind and execute */
   if ( !$stmt->bind_param("s", $_POST['extension_type']) || !$stmt->bind_param("i", intval($id)) ) {
       echo "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;
   }
   if ( !$stmt->execute() ) {
       echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
   }
}
?>

As noted by @Simon Carlson, you also need to account for SQL injection. If you have a set list of extension types, you could improve on the code above by checking the $_POST value against known valid values.

Upvotes: 0

JasonMortonNZ
JasonMortonNZ

Reputation: 3750

You could use jQuery to send an AJAX post request when the select box has it's value changed. In turn this could update your mysql database entry. If this is the kind of thing your wanting let me know and I'll expand on my answer further with a code example.

JS Code example

$("select").change(function () {
    // Send ajax request
    $.ajax
    ({
       type: "POST",
       url: "/change-value.php",
       dataType: 'json',
       data: { location: $(this).val() },
       cache: false,
       success: function(data)
       {
       }
    });

});

// PHP change-value.php example

<?php

// Get the changed value
$value = $_POST['value'];

// Code to update you mysql database entry would go here...

Upvotes: 3

Related Questions