How to update the database, when changing a select option - PHP, AJAX

Question

I have a table that i made in php, now i want to let the user to change the access level of each user, using a dropdown menu (select/option). For example:

Once the user presses general it sends the request to ajax, changing/updating the database instantly.

admin.php

    <?php
//database connection
include("db_conn.php");
include("session.php");

session_start();

$username = $_SESSION['logged_in']; //set the current user
$query = "SELECT `ID`, `Username`,`Name`, `DOB`, `Email`, `Access` FROM `users`";

$result =   $mysqli->query($query);
$row=$result->fetch_array(MYSQLI_ASSOC);


    function show_table($myData)
    {
        echo "<table border=1 id='staff_table'>
        <tr>
        <th>ID</th>
        <th>Username</th>
        <th>Name</th>
        <th>DOB</th>
        <th>Email</th>
        <th>Access</th>
        </tr>";
        while ($row = $myData->fetch_array(MYSQLI_ASSOC))
        {   
            $access= $row['Access'];
            $access2;


              $access_data = 1; //admin = level 1
              $access_data2 = 2; //general = 2
            if($access == 1)
            {
                $access = "Admin";
                $access2 = "General";

                $access_data = 1; //change when the page loads (depending what access level they are)
                $access_data2 = 2; //change when the page loads
            }
            else
            {

                $access = "General";
                $access2 = "Admin";

                $access_data = 2; //the reason i have these values is they 
                $access_data2 = 1; //change depending on what the user's access level is once the page loads
            }

            echo "<tr>";
            echo "<td>" . $row['ID'] . "</td>";
            echo "<td>" . $row['Username'] . "</td>";
            echo "<td>" . $row['Name'] . "</td>";
            echo "<td>" . $row['DOB'] . "</td>";
            echo "<td>" . $row['Email'] . "</td>";
            echo "<td id='" . $row['ID'] .  "'>";
            echo "<select name='admin_menu' class='admin_menu'>"; 
            echo '<option class="admin_option" value="' . $access_data2. '">' . $access . '</option>';
            echo '<option class="admin_option" value="' . $access_data. '">' . $access2 . '</option>';
            echo "</select>";
            echo "</td>";
            echo "</tr>";
        }
        echo "</table>";
    }

?>
<html>
    <head>
        <title>Admin Table</title>
        <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.2/jquery.min.js"></script>
        <script>
            $(document).ready(function(){
                $('.admin_menu').change(function() {
                    id = $(this).parent().attr('id');
                    level = $('.admin_menu  option:selected').val();
                    var request = $.ajax({
                      url: "changeLevel.php",
                      method: "POST",
                      data: { id : id, level: level },
                      dataType: "html"
                      });

                    request.done(function() {
                       //location.reload(); //reload page (if it actually updates)
                    });

                    });
                });
        </script>   
    </head>


    <body>
        <h1>Admin Table</h1>
        <?php show_table($result); ?>
    </body>
</html>

changeLevel.php

<?php
include("db_conn.php");

$id = $_POST['id'];
$level = $_POST['level'];

    $queryUpdate="UPDATE users SET Access='$level' WHERE ID='$id'";
    $update = $mysqli->query($queryUpdate);
?>

The user table is not updating, is there any other solution to this? Or can anyone see the problem? Thanks in advance, I'm pretty new to AJAX so all help is appreciated :)

and ajax is successful? and what you see at server-side like var_dump($_POST); die; — daremachine
– daremachine, Commented May 22, 2016 at 2:34
you need to check for errors. If your ajax was sent with status 200, then you need check php side, like $mysqli is ok, $id, $level are ok. I recommend to you wrap your query to try catch block and handle the exception, problem might be at database — daremachine
– daremachine, Commented May 22, 2016 at 2:39

Matt Bryce · Accepted Answer · 2016-05-22 08:57:45Z

3

you could trigger the form post when there is a change on the field using

onchange='this.form.submit()'

so your select box may look something like this

  <form action='' method='post'>
      <select name="dropdown" onchange='this.form.submit()'>
          <option name="option1" value="option1">This is Option 1</option>            
          <option name="option2" value="option2">This is Option 2</option>            
      </select>
  </form>

answered May 22, 2016 at 8:57

Matt Bryce

1231 silver badge8 bronze badges

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

Comments

Sercan REYHANLI · Accepted Answer · 2016-05-22 11:55:28Z

1

first change this lines

echo "<select name='admin_menu' class='admin_menu'>"; 
echo '<option class="admin_option" value="' . $access_data2. '">' . $access . '</option>';
echo '<option class="admin_option" value="' . $access_data. '">' . $access2 . '</option>';
echo "</select>";

to

echo "<select name='admin_menu' class='admin_menu' id='Selectid' onchange='postlist();'>"; 
echo '<option class="admin_option" value="'.$access_data2.'-'.$access.'">'.$access.'</option>';
echo '<option class="admin_option" value="'.$access_data.'-'.access2 .'">'.$access2.'</option>';
echo "</select>";

In changed lines I add onchange="" function and id="" to "select" tag, after I change the <option value="">; "$access_data" and "$access" added together with "-" for exploding the value.

JavaScript

<script>
    function postlist() {
    $.ajax({
        type: 'POST', 
        url: 'changeLevel.php', // here posting value to your php file
        data: $('#Selectid').val(), // here get the option value from select
        success: function (answer) {
            $("#ReportResult").html(answer) // here you can define an alert function for after success or you can use it with an id for showing the response
        }
    })
}
</script>

in changeLevel.php

<?php
include("db_conn.php");

$id = $_POST['id']; //here we get value like (13-Admin);
$ids = explode("-", $id); //and here we explode the $id value (explode mean split the value with "-" or you can define "space",",","." or another sign)

    $queryUpdate="UPDATE users SET Access='$ids[1]' WHERE ID='$ids[0]'";
    $update = $mysqli->query($queryUpdate);
?>

PS = $ids[0] eq to id and $ids[1] eq to Level

you can change this order easily

edited May 22, 2016 at 11:55

answered May 22, 2016 at 2:44

Sercan REYHANLI

2,0905 gold badges26 silver badges30 bronze badges

3 Comments

daremachine Over a year ago

why this approach is better then original and why you avoid jQuery listeners if jquery is used?

Sercan REYHANLI Over a year ago

My answer is a suggestion She not have to apply it, but accept it this is another way !

Mike Over a year ago

"Do this" type answers are generally not very useful. Explain what is different and why it should be done this way. What problems are you fixing and how did you achieve it?

Collectives™ on Stack Overflow

How to update the database, when changing a select option - PHP, AJAX

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related