Help needed - Adding and Deleting rows from datatables

Help needed - Adding and Deleting rows from datatables

edited May 2011 in General 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>

Replies

  • edited May 2011 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!
  • Posts: 8
    Can someone please help me with this?
  • 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.
  • 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.
  • 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
  • Posts: 8
    Thanks Idopson, it worked :).

    Gosh always felt it was something simple.
  • 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.
  • Posts: 8
    Got it working.
  • 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.
  • Posts: 21,980
    @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
  • 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.