Help needed - Adding and Deleting rows from datatables

pbagch200

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:
[code]

$(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"
});
});

[/code]

AddForm:
[code]
Add
Delete


column2


column3


column4


column5


column6



[/code]

pbagch200

DeleteData.php
[code]
<?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
[/code]

AddData.php

[code]
<?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' ;
?>

[/code]

Thanks in advance!

pbagch200

Can someone please help me with this?

dmolavi

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:

[code]<?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;

?>[/code]

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.

ldopson

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.

ldopson

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

pbagch200

Thanks Idopson, it worked :).

Gosh always felt it was something simple.

pbagch200

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.

pbagch200

Got it working.

sylviars

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.

allan

@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

rdunton

@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.