DataTables logo DataTables

via Ad Packs
Help needed - Adding and Deleting rows from datatables
  • pbagch200pbagch200
    Posts: 8
    I am new to not only datatables but also to web development. I am sure the problem I am facing is very simple but after two days of googling and searching through the forum I decided to finally ask for help. I am using datatables with the jeditable plugin using MySQL DB and PHP server side processing. I need to update, add and delete records. I have been able to implement the update portion (thanks to the forum!).

    In the delete portion when I click 'delete' the record is deleted from the DB but the page is not refreshed to show the reduced set of data. After going through the forum I guess it is because I am getting sEcho=0 but I am not able to get around it.

    In the add section the effect is not even taking place in the DB.

    My table has an id column which is autoincremented.

    Following are the portions of the code:
    <script> 
 $(document).ready(function(){ 
 $('#the_table').dataTable({"bFilter": false ,
     "bInfo": false,
     "bPaginate": false,
     "bProcessing": true,
     "bServerSide": true, 
     'sAjaxSource':'datatables.php',
      "aoColumns":[
                                         {
                                            "bVisible" : false     //id
                                         },
                                         {},    //Column2
                                         {},     //Column3
                                         {},    //Column4
                                         {},   //Column5
                                         {}    //Column6 
                                       ]
     }).makeEditable({
     sUpdateURL: "UpdateData.php",
     sAddURL: "AddData.php",
     sDeleteURL: "DeleteData.php"
     }); 
 }); 
 </script>

    AddForm:
    <button id="btnAddNewRow">Add</button>
<button id="btnDeleteRow">Delete</button>
 </div>    
 <form id="formAddNewRow" action="AddData.php">
        <label for="name">column2</label><input type="text" name="column2" id="column2" class="required" rel="0" />
        <br />
        <label for="name">column3</label><input type="text" name="column3" id="column3" class="required" rel="1" />
        <br />
        <label for="name">column4</label><input type="text" name="column4" id="column4" class="required" rel="2"/>
        <br />
        <label for="name">column5</label><input type="text" name="column5" id="column5" class="required" rel="3" />
        <br />
        <label for="name">column6</label><input type="text" name="column6" id="column6" class="required" rel="4"/>
        <br />
</form>
  • pbagch200pbagch200
    Posts: 8
    DeleteData.php
    <?php
   $id = $_REQUEST['id'] ;
   
  
  $sTable = "tablename";
  
 
  
   
    $aColumns = array( 'id','column2', 'column3', 'column4', 'column5', 'column6');    
   
    
      /* Database connected */
  
    
     $sQuery=" Delete from $sTable WHERE id='$id' ";  
      mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error());
     $sQuery=" commit "; 
      mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error());   
  
      echo 'Record Deleted' ;  
      
      
    $sQuery = "
        SELECT SQL_CALC_FOUND_ROWS ".str_replace(" , ", " ", implode(", ", $aColumns))."
        FROM   $sTable
            ";
    $rResult = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error());
    
   
    $sQuery = "
        SELECT FOUND_ROWS()
    ";
    $rResultFilterTotal = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error());
    $aResultFilterTotal = mysql_fetch_array($rResultFilterTotal);
    $iFilteredTotal = $aResultFilterTotal[0];
    
 
    $sQuery = "SELECT COUNT(1) FROM   $sTable
    ";
    $rResultTotal = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error());
    $aResultTotal = mysql_fetch_array($rResultTotal);
    $iTotal = $aResultTotal[0];
    
    
   
     
    $output = array(
        "sEcho" => intval($_GET['sEcho']),
        "iTotalRecords" => $iTotal,
        "iTotalDisplayRecords" => $iFilteredTotal,
        "aaData" => array()
    );
    
    while ( $aRow = mysql_fetch_array( $rResult ) )
    {
        $row = array();
        for ( $i=0 ; $i<count($aColumns) ; $i++ )
        {
            if ( $aColumns[$i] == "version" )
            {
               
           
                $row[] = ($aRow[ $aColumns[$i] ]=="0") ? '-' : $aRow[ $aColumns[$i] ];
            }
            else if ( $aColumns[$i] != ' ' )
            {
             
           
               $row[] = $aRow[ $aColumns[$i] ];
            }
        }
        $output['aaData'][] = $row;
    }
    
    echo json_encode( $output );      
?>

    AddData.php

    <?php

  
  $column2 = $_REQUEST['column2'] ;
  $column3 = $_REQUEST['column3'] ;
  $column4 = $_REQUEST['column4'] ;
  $column5 = $_REQUEST['column5'] ;
  $column6 = $_REQUEST['column6'] ;      
 

  
  
    $aColumns = array( 'dbcolumn2', 'dbcolumn3', 'dbcolumn4', 'dbcolumn5', 'dbcolumn6');
    
       

    $sTable = "tablename";
      
    /* Database connected */
      
    
    $sQuery = "
        insert into $sTable ($aColumns) values ( 'column2', 'column3', 'column4', 'column5', 'column6')
    ";
     mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error());
    
    
     
     $sQuery = "
        commit
    ";
     mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error());
    
    echo 'Record Added' ;  
?>

    Thanks in advance!
  • pbagch200pbagch200
    Posts: 8
    Can someone please help me with this?
  • dmolavidmolavi
    Posts: 65
    i can't get the add function to work w/ the makeeditable feature, either. any help would be appreciated. here's my code that handles the addition of the record:

    <?php

require_once('auth.php');

require_once('config.php');
//Connect to mysql server
$link = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);
if(!$link) {
	die('Failed to connect to server: ' . mysql_error());
}

//Select database
$db = mysql_select_db(DB_DATABASE);
if(!$db) {
	die("Unable to select database");
}
$id = 0;
$i_query = "INSERT INTO item VALUES('',".$_SESSION['SESS_MEMBER_ID'].",".$_POST['manufacturer'].",'".mysql_real_escape_string(@trim($_POST['partnumber']))."',".$_POST['roadname'].",".$_POST['type'].",'".mysql_real_escape_string(@trim($_POST['description']))."','".mysql_real_escape_string(@trim($_POST['roadnumber']))."','".@trim($_POST['scale'])."',".$_POST['value'].")";
$i_result = mysql_query($i_query);
$u_query = "UPDATE update SET update_time=".time()." WHERE member_id=".$_SESSION['SESS_MEMBER_ID'];
$u_result = mysql_query($u_query);
$id_query = mysql_query("SELECT MAX(i_index) FROM item") or die(mysql_error());
$id_result = mysql_fetch_row($id_query);
$id = $id_result[0];
echo $id;

?>

    based on the docs, it should work, as the ID of the new row must be returned, however, clicking the "ok" button on the generated form does nothing, not even close the form.
  • ldopsonldopson
    Posts: 4
    Did you ever get this working?

    I do think that for a delete on the sever using makeEditable you need to return an "ok" string or the ID of the row.
  • ldopsonldopson
    Posts: 4
    Confirmed...

    It should be "ok" that it returns, verify using Firebug or Developer Tools and make sure that your server code produces the right Mediatype.


    Leo
  • pbagch200pbagch200
    Posts: 8
    Thanks Idopson, it worked :).

    Gosh always felt it was something simple.
  • pbagch200pbagch200
    Posts: 8
    Now, I see a different problem altogether. When there are more than one row say 5 and I hit the delete button it deletes the row and refreshes the page. I go on doing this until there is just one row left - now when I hit the delete button it does delete it from the DB but its still there on the page and the top of the page says 'Processing...'. If I click on the row again then the delete button is again activated and I can hit that too without seeing any errors though the row has been deleted already when I first deleted it.
  • pbagch200pbagch200
    Posts: 8
    Got it working.
  • sylviarssylviars
    Posts: 2
    Hi, i am using datatables. i have used multiple rows select and now i want to perform delete operation on multiple rows. For eg: i have selected 2,3,4 rows, so i want all the 3 rows to get deleted at the same time.
  • allanallan
    Posts: 15,551
    @sylviars: Can I suggest you create a new thread for your question. Also if you could show us the code that you are currently using to do the delete that would be good (presumably a loop of fnDeleteRow).

    Allan
  • rduntonrdunton
    Posts: 2
    @pbagch200 I haven't read all the comments but as far as your first questions go, try checking out JSONData.com. It's a program that is in beta right now but should help with your problem.
This discussion has been closed.
← All Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In Apply for Membership

Support

Get useful and friendly help straight from the source.

Categories

In this Discussion