update selected dropdown values in database with form submit

Question

users register to site. admin will login & see list of users.

I am trying to give an option for admin to select checkbox and change status of user through Dropdown submit. when i tried below code i can able to select "Y/N/A" , after clicking submit button its displaying message "Added Successfully" , but its not updating values in database.

table name : tbl_users , column : userStatus [enum] , values : Y, N, A

form

<form method="post" action="ajax1.php">
 <select name="userStatus">
 <option value="N">N</option>
 <option value="Y">Y</option>
 <option value="A">A</option>
 </select>

 <button type="submit" name="submit" >Submit</button>

 </form>

ajax1.php

if(isset($_POST["submit"]))
{

$userStatus=$_POST["userStatus"];

$conn = new Database();
$stmt = $conn->dbConnection()->prepare("INSERT INTO tbl_users (userStatus) VALUES ('$userStatus')"); 
echo " Added Successfully ";
}

code to display users checkbox, id, name ,email :

$stmt = $user_home->runQuery("SELECT * FROM tbl_users");
$stmt->execute(array(":uid" => $_SESSION['userSession']));
$row = $stmt->fetch(PDO::FETCH_ASSOC);
$stmt->execute();
$status = array('Y'=>'Verified','N'=>'Not verified' , 'A'=>'Approved'); 

echo "<table>";
echo "<tr>
    <td></td>
    <td>id</td>
    <td>name</td>
    <td>email</td>
</tr>";

while($data = $stmt->fetch())
{ 
    echo "<tr>
        <td> <input type='checkbox' > </td>
        <td>" . $data['userID'] . "</td>
        <td>" . $data['name'] . "</td>
        <td>" . $data['email'] . "</td>
</tr>";
}
echo "</table>";

i researched & tried lot before posting question, in some links i saw we need to use Javascript, but in some other links, they mentioned we can achieve only with php. i am very new to coding, please help me

Edit

after following below answer , i updated code as $stmt = $conn->dbConnection()->prepare('UPDATE tbl_users SET userStatus = ? WHERE userID = ?');

what are you doing, you are trying to insert status in user tables. — Happy Coding, Nov 11 '16 at 07:00
@HappyCoding yes , i am trying to update `status`... before only `status` values are there..... i need to change those `values` according to selected values..... — , Nov 11 '16 at 07:01
this one the way to solve problem. Please make a java script function that call the ajax request to update the same thing and you should pass the required variable to querry the same. Then you should call the function on drop-down on-change event. This way you can achieve this. — Pranav MS, Nov 11 '16 at 07:02
@HappyCoding i tried like this : `$stmt = $conn->dbConnection()->prepare('UPDATE tbl_users SET userStatus = ? WHERE userID = ?');` but still no luck..... — , Nov 11 '16 at 07:14
@pranavm.s is `javascript` is mandatary to solve the issue ? — , Nov 11 '16 at 07:15
@RaviMCA when i tried this code : `print $userStatus;` i got error : `Notice: Undefined variable: userStatus` — , Nov 11 '16 at 07:16
@abcd yes because. the on-change event in drop-down is client side . So we have to write some code in java-script to handle the dynamic events and requests from clent — Pranav MS, Nov 11 '16 at 07:18

score -1 · Answer 1 · answered Nov 11 '16 at 07:00

-1

What you are doing is actually inserting a new row to the table with this line:

"INSERT INTO tbl_users (userStatus) VALUES ('$userStatus')"

You should do an UPDATE, not an insert. What you basically want to do is UPDATE the user WHERE the user userID (or whatever the id column is named) is the id of the selected user.

See MySQL UPDATE.

answered Nov 11 '16 at 07:00

Ilker Mutlu

744
6
18

i tried like this : `$stmt = $conn->dbConnection()->prepare('UPDATE tbl_users SET userStatus = ? WHERE userID = ?');` but it didt worked for me, i will check above link..... – Nov 11 '16 at 07:07
Also see this: http://stackoverflow.com/questions/6514649/php-mysql-bind-param-how-to-prepare-statement-for-update-query – Ilker Mutlu Nov 11 '16 at 07:08

Pranav MS · Accepted Answer · 2016-11-11T09:32:02.897

    <form method="post" action="ajax1.php">
        <select name="userStatus" id="userStatus" onchange="UpdateStatus();">
            <option value="N">N</option>
            <option value="Y">Y</option>
            <option value="A">A</option>
        </select> 
        <button type="submit" name="submit" >Submit</button> 
    </form>
    <script> 
        function UpdateStatus(){
            var staus=$('#userStatus :selected').val();

            allCheckedBoxes="";
            $("input[id^='checkBoxId']:visible:checked").each(function(){ // this is to get checked checkbox vaues to update which user to update
                allCheckedBoxes=allCheckedBoxes+$(this).val()+","; // comaseparated valuse
            }); 

            var dataString="allCheckedBoxes="+allCheckedBoxes+"&staus="+staus;

            $.post("aupdate_status.php",'allCheckedBoxes='+allCheckedBoxes'&staus='+staus,function(result,status,xhr)
            {
                if( status.toLowerCase()=="error".toLowerCase() )
                { alert("An Error Occurred.."); }
                else
                {
                    alert(result);
                }
            })
            .fail(function(){ alert("something went wrong. Please try again") });
        }
    </script>
    update_status.php
    <?php 
    $staus=$_POST['staus'];
    $allCheckedBoxes=$_POST['allCheckedBoxes'];
    $ArrallCheckedBoxes=explode(",",$allCheckedBoxes);
    foreach($ArrallCheckedBoxes as $tempBoxes){
        $sqlQueryToUpdate=" UPDATE tbl_users SET userStatus = '".$staus."'  WHERE userID = '".$tempBoxes."' ;";
        $conn = new Database();
        $stmt = $conn->dbConnection()->prepare($sqlQueryToUpdate); 
        echo " ok success";
    }
    ?>

Please try this . This is working in my case. This will work you too. Don't forget add jquery in your coding.

Thanks, but i got this Parse error: syntax error, unexpected '$allCheckedBoxes' (T_VARIABLE) in line `$ArrallCheckedBoxes=explode("," $allCheckedBoxes);` — , Nov 11 '16 at 07:50
@abcd please check now i have edited the ans. the syntax of explode goes mistake that is the reson to the error. Please reolpace that line with **$ArrallCheckedBoxes=explode(",",$allCheckedBoxes);** this. — Pranav MS, Nov 11 '16 at 08:31
i replaced , now i got this error : `Notice: Undefined index: staus` in line `$staus=$_POST['staus'];` & `Notice: Undefined index: allCheckedBoxes` in line `$allCheckedBoxes=$_POST['allCheckedBoxes'];` — , Nov 11 '16 at 09:22

update selected dropdown values in database with form submit

2 Answers2