Update single column values of Table

Question

We are using below code to display results in table & now for commission1 column values we need to update values in database with help of ajax.

enter image description here

Under commission1 column, we need to add "Edit" button, once we click on "Edit" button, it should allow to edit that value 10 . once we click on " update" button, it should save values in Database.

enter image description here

enter image description here

display.php

function getDesignerCollection() {
    $user_home = new USER();

    if (!$user_home->is_logged_in()) {
        header("Location: index.php");
        die();
    }

    $stmt = $user_home->runQuery("SELECT * FROM tbl_users");
    $stmt->execute(array(":uid" => $_SESSION['userSession']));
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    $stmt->execute();

    $i=0;
    while($data = $stmt->fetch()) {
        $responce[$i]=array($data['userID'],
                      . $data['phone'],
                      $data['commission1'],
                      .
                      .                  
        $i++;
    }
    echo json_encode($responce);
}

connection

try {
    $dbo = new PDO('mysql:host='.$host_name.';dbname='.$database, $username, $password);
} catch (PDOException $e) {
    print "Error!: " . $e->getMessage() . "<br/>";
    die();
}

script

<script>
    var colsOption = [  
        {id: 'phone' , header: "phone" , width :"130"},
    //Edit commission

    <?php 
    $count="SELECT commission1 FROM tbl_users";
    $i=1;

    foreach ($dbo->query($count) as $row) {
        $m=$i%2;
        $sid='s' . $row['commission1'];
        echo "
        <tr class='r$m'>
            <td>
                <input type=button id=$sid value='Edit' onclick=edit_field($row[commission1])> 
            </td>
        </tr>";
        $i=$i+1;
    }
    ?>
//Edit commission end
    ];
</script>

for displaying commission we were using this code : {id: 'commission1' , header: "commission1" , width :"120"},] as we need to edit values of commission1 column, now we are using above code under //Edit commission but now table is not displaying.

I am new to coding & checked lot of other posts before asking here....


Show source
| javascript   | php   | html   | database   2017-01-03 11:01 2 Answers

Answers ( 2 )

  1. 2017-01-04 04:01

    You should build your html code inside the body and not in the script section. So move the below code to body section. `

    <?php 
        $count="SELECT commission1 FROM tbl_users";
        $i=1;
    
        foreach ($dbo->query($count) as $row) {
            $m=$i%2;
            $sid='s' . $row['commission1'];
            echo "
            <tr class='r$m'>
                <td>
                    <input type=button id=$sid value='Edit' onclick=edit_field($row[commission1])> 
                </td>
            </tr>";
            $i=$i+1;
        }
        ?>`
    
  2. 2017-01-05 07:01

    Please follow this steps.

    Add this is to user col creation script

    {id: 'commission1' , header: "commission1" , width :"120",renderer : my_CommisionRenderId},
    

    my_CommisionRenderId function

    function my_CommisionRenderId(value ,record,columnObj,grid,colNo,rowNo)
    {
        var no= record[columnObj.fieldIndex]; 
        return record[8]+"&nbsp;&nbsp;&nbsp;<input type=button id=sid value='Edit' onclick=edit_fieldDisplay('"+record[0]+"','"+record[1]+"','"+record[8]+"','"+record[2]+"','"+record[3]+"')>";  
    }
    

    edit_fieldDisplay function

    function edit_fieldDisplay(TxtUserID,TxtDesignName,TxtCommission,TxtEmail,TxtPhone){ 
        $('#DivCommissionEdit').show();
    
        $('#TdTxtUserID').html(TxtUserID+'<input  type="hidden" id="TxtBxTxtUserID" value=""/>');
        $('#TxtBxTxtUserID').val(TxtUserID);
    
        $('#TdTxtDesignName').html(TxtDesignName+'<input  type="hidden" id="TxtBxTdTxtDesignName" value=""/>');
        $('#TxtBxTdTxtDesignName').val(TxtDesignName);
    
        $('#TdTxtPhone').html(TxtPhone+'<input  type="hidden" id="TxtBxTdTxtPhone" value=""/>');
        $('#TxtBxTdTxtPhone').val(TxtPhone);
    
        $('#TdTxtEmail').html(TxtEmail+'<input  type="hidden" id="TxtBxTdTxtEmail" value=""/>'); 
        $('#TxtBxTdTxtEmail').val(TxtEmail);
    
        $('#TdTxtCommission').html('<input  type="text" id="TxtBxTdTxtCommission" value="'+TxtCommission+'"/>');
    
    } 
    

    Update_field function

    function Update_field( ){
            var NewTxtCommission=$('#TxtBxTdTxtCommission').val();
            var NewTxtEmail=$('#TxtBxTdTxtEmail').val();
            var NewTxtPhone=$('#TxtBxTdTxtPhone').val();
            var NewTxtDesignName=$('#TxtBxTdTxtDesignName').val();
            var NewTxtUserID=$('#TxtBxTxtUserID').val();
            if(confirm("Do you want to proceed ?")){
                $.post("ajaxChangeCommision.php","NewTxtCommission="+NewTxtCommission+"&NewTxtEmail="+NewTxtEmail+"&NewTxtPhone="+NewTxtPhone+"&NewTxtDesignName="+NewTxtDesignName+"&NewTxtUserID="+NewTxtUserID,function(result,status,xhr){
                    alert(result);
                    $('#DivCommissionEdit').hide();
                    $('#DivCommissionResponse').show();
                    $('#DivCommissionResponse').html(result);
                    window.location.reload()
                })
                .fail(function(){  alert("something went wrong please try again."); });
            }
    
        }
    

    Add this in your head section

    <script type="text/javascript">
    $(document).ready(function(){
         $('#DivCommissionEdit').hide();
    });
    </script>
    

    Add this in your html section

    <div style="margin: 10px; display: block;background:#e6ae64;" align="center" id="DivCommissionEdit">
        <div style="color: #000">
            <span style="font-weight: bold;font-size: 20px;">CHANGE THE COMMISSION HERE</span>
        </div>
        <table border="1">
            <tr >
                <td>Id</td>
                <td>Designer Name</td>
                <td>Phone No</td>
                <td>Email</td>
                <td>Commission</td>
                <td>Action</td>
            </tr>
            <tr>
                <td id="TdTxtUserID"> </td>
                <td id="TdTxtDesignName"> </td>
                <td id="TdTxtPhone"> </td>
                <td id="TdTxtEmail"> </td>
                <td id="TdTxtCommission"> </td>
                <td id=""><input type=button id="" value="UPDATE" onclick="Update_field();"></td>
            </tr>
    
        </table>
      </div>
      <div style="margin: 10px; display: none;background:#4bb458;" align="center" id="DivCommissionResponse"></div>
    

    ajaxChangeCommision.php

    <?php
        $host = "localhost";
        $db_name = "xxx";
        $username = "xxx";
        $password = "xxxxx";
    
        $NewConn=mysqli_connect($host,$username,$password,$db_name);
    
    if( isset($_POST['NewTxtCommission']) && !empty($_POST['NewTxtCommission']) && $_POST['NewTxtCommission']!="" )
    {  $NewTxtCommission=$_POST['NewTxtCommission'];  }else{$NewTxtCommission=0;}
    
    if( isset($_POST['NewTxtEmail']) && !empty($_POST['NewTxtEmail']) && $_POST['NewTxtEmail']!="" )
    {  $NewTxtEmail=$_POST['NewTxtEmail'];  }else{$NewTxtEmail="";}
    
    if( isset($_POST['NewTxtPhone']) && !empty($_POST['NewTxtPhone']) && $_POST['NewTxtPhone']!="" )
    {  $NewTxtPhone=$_POST['NewTxtPhone'];  }else{$NewTxtPhone=0;}
    
    if( isset($_POST['NewTxtDesignName']) && !empty($_POST['NewTxtDesignName']) && $_POST['NewTxtDesignName']!="" )
    {  $NewTxtDesignName=$_POST['NewTxtDesignName'];  }else{$NewTxtDesignName="";}
    
    if( isset($_POST['NewTxtUserID']) && !empty($_POST['NewTxtUserID']) && $_POST['NewTxtUserID']!="" )
    {  $NewTxtUserID=$_POST['NewTxtUserID'];  }else{$NewTxtUserID=0;}
    
    
    $CommissionUpdateQry="UPDATE tbl_users SET commission1='".$NewTxtCommission."' WHERE userID='".$NewTxtUserID."' ;";
        $queryResult=TRUE;
        if( isset($NewConn) && !empty($NewConn) && $NewConn!="" ) {
            $queryResult=mysqli_query($NewConn,$CommissionUpdateQry);
        }
    
        if($queryResult==TRUE){
            echo "Commission Updated Successsfully";
        }else{
            echo "something went wrong please try again";
        }
    
    ?>
    
◀ Go back