CODEWITHSUNDEEP
phpjqueryhtmlajaxselect
BriannaXD
BriannaXD

Reputation: 169

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

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:

enter image description here

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 :)

Upvotes: 0

Views: 4457

Answers (2)

Ivan Barayev
Ivan Barayev

Reputation: 2055

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

Upvotes: 1

Matt Bryce
Matt Bryce

Reputation: 123

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>

Upvotes: 3

Related Questions