2

I amusing the following code to display data from database using jquery datatables

    $(document).ready(function() { 
    var oTable = $('#example').dataTable( {
     "bProcessing": true,
     "bServerSide": true,
     "sAjaxSource": "server.php" }
     ); 
     } );

The above code displays data in the table

 <table cellpadding="0" cellspacing="0" border="0" class="display" id="example">
    <thead>
      <tr>
        <th>first name</th>
        <th>last name</th>
        <th>email</th>


      </tr>
    </thead>

    <tfoot>
      <tr>
        <th>first name</th>
        <th>last name</th>
        <th>email</th>
      </tr>
    </tfoot>
  </table>

Now I want to add/update and delete data.How do I do that?

I tried

 $(document).ready(function() { 
    var oTable = $('#example').dataTable.makeEditable( {
     "bProcessing": true,
     "bServerSide": true,
     "sAjaxSource": "server.php" }
     ); 
     } );

but the data doesnt show up. How should I go about?

EDIT:

Here is the file server.php that I used. server.php is just a name that I have given to the file server.php

 <?php
error_reporting(E_ALL ^ E_NOTICE); 
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
 * Easy set variables
 */

/* Array of database columns which should be read and sent back to DataTables. Use a space where
 * you want to insert a non-database field (for example a counter or static image)
 */
$aColumns = array( 'first_name', 'last_name', 'email' );

/* Indexed column (used for fast and accurate table cardinality) */
$sIndexColumn = "customer_id";

/* DB table to use */
$sTable = "customer";

/* Database connection information */
$gaSql['user']       = "root";
$gaSql['password']   = "";
$gaSql['db']         = "test";
$gaSql['server']     = "localhost";

/* REMOVE THIS LINE (it just includes my SQL connection user/pass) */
//include( $_SERVER['DOCUMENT_ROOT']."/datatables/mysql.php" );


/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
 * If you just want to use the basic configuration for DataTables with PHP server-side, there is
 * no need to edit below this line
 */

/* 
 * Local functions
 */
function fatal_error ( $sErrorMessage = '' )
{
    header( $_SERVER['SERVER_PROTOCOL'] .' 500 Internal Server Error' );
    die( $sErrorMessage );
}


/* 
 * MySQL connection
 */
if ( ! $gaSql['link'] = mysql_connect( $gaSql['server'], $gaSql['user'], $gaSql['password']  ) )
{
    fatal_error( 'Could not open connection to server' );
}

if ( ! mysql_select_db( $gaSql['db'], $gaSql['link'] ) )
{
    fatal_error( 'Could not select database ' );
}

$query = "select * from customer";
$rResult = mysql_query($query,$gaSql['link']);

/* 
 * Paging
 */
$sLimit = "";
if ( isset( $_GET['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1' )
{
    $sLimit = "LIMIT ".intval( $_GET['iDisplayStart'] ).", ".
        intval( $_GET['iDisplayLength'] );
}


/*
 * Ordering
 */
$sOrder = "";
if ( isset( $_GET['iSortCol_0'] ) )
{
    $sOrder = "ORDER BY  ";
    for ( $i=0 ; $i<intval( $_GET['iSortingCols'] ) ; $i++ )
    {
        if ( $_GET[ 'bSortable_'.intval($_GET['iSortCol_'.$i]) ] == "true" )
        {
            $sOrder .= "`".$aColumns[ intval( $_GET['iSortCol_'.$i] ) ]."` ".
                ($_GET['sSortDir_'.$i]==='asc' ? 'asc' : 'desc') .", ";
        }
    }

    $sOrder = substr_replace( $sOrder, "", -2 );
    if ( $sOrder == "ORDER BY" )
    {
        $sOrder = "";
    }
}


/* 
 * Filtering
 * NOTE this does not match the built-in DataTables filtering which does it
 * word by word on any field. It's possible to do here, but concerned about efficiency
 * on very large tables, and MySQL's regex functionality is very limited
 */
$sWhere = "";
if ( isset($_GET['sSearch']) && $_GET['sSearch'] != "" )
{
    $sWhere = "WHERE (";
    for ( $i=0 ; $i<count($aColumns) ; $i++ )
    {
        if ( isset($_GET['bSearchable_'.$i]) && $_GET['bSearchable_'.$i] == "true" )
        {
            $sWhere .= "`".$aColumns[$i]."` LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%' OR ";
        }
    }
    $sWhere = substr_replace( $sWhere, "", -3 );
    $sWhere .= ')';
}

/* Individual column filtering */
for ( $i=0 ; $i<count($aColumns) ; $i++ )
{
    if ( isset($_GET['bSearchable_'.$i]) && $_GET['bSearchable_'.$i] == "true" && $_GET['sSearch_'.$i] != '' )
    {
        if ( $sWhere == "" )
        {
            $sWhere = "WHERE ";
        }
        else
        {
            $sWhere .= " AND ";
        }
        $sWhere .= "`".$aColumns[$i]."` LIKE '%".mysql_real_escape_string($_GET['sSearch_'.$i])."%' ";
    }
}


/*
 * SQL queries
 * Get data to display
 */
$sQuery = "
    SELECT SQL_CALC_FOUND_ROWS `".str_replace(" , ", " ", implode("`, `", $aColumns))."`
    FROM   $sTable
    $sWhere
    $sOrder
    $sLimit
    ";
$rResult = mysql_query( $sQuery, $gaSql['link'] ) or fatal_error( 'MySQL Error1: ' . mysql_errno() . " " .mysql_error() );

/* Data set length after filtering */
$sQuery = "
    SELECT FOUND_ROWS()
";
$rResultFilterTotal = mysql_query( $sQuery, $gaSql['link'] ) or fatal_error( 'MySQL Error2: ' . mysql_errno() . " " .mysql_error()  );
$aResultFilterTotal = mysql_fetch_array($rResultFilterTotal);
$iFilteredTotal = $aResultFilterTotal[0];

/* Total data set length */
$sQuery = "
    SELECT COUNT(`".$sIndexColumn."`)
    FROM   $sTable
";
$rResultTotal = mysql_query( $sQuery, $gaSql['link'] ) or fatal_error( 'MySQL Error3: ' . mysql_errno()  . " " .mysql_error() );
$aResultTotal = mysql_fetch_array($rResultTotal);
$iTotal = $aResultTotal[0];
//echo $sQuery;
//exit;



/*
 * Output
 */
$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] == "email" )
        {
            /* Special output formatting for 'version' column */
            $row[] = ($aRow[ $aColumns[$i] ]=="0") ? '-' : $aRow[ $aColumns[$i] ];
        }
        else if ( $aColumns[$i] != ' ' )
        {
            /* General output */
            $row[] = $aRow[ $aColumns[$i] ];
        }
    }
    $output['aaData'][] = $row;
}
//echo Json($output, JsonRequestBehavior.AllowGet);


//var_dump($output);
//exit;
echo json_encode( $output );
 ?>

2 Answers 2

4

Add http://datatables.net/examples/api/add_row.html

Inline Editing Example http://datatables.net/blog/Inline_editing

Delete http://datatables.net/examples/api/select_single_row.html

I would recommend googling your exact question and just adding "jQuery Datatable" to the end of your search. I have worked with this plugin quite a bit and it seems the community around this plugin is very good.

Sign up to request clarification or add additional context in comments.

4 Comments

Thanks for the links.. I am following datatables.net/blog/Inline_editing and it is useful. But have a look at the code I gave. I am calling data and then displaying without using tbody. but in the above link,they are using tbody and have all the edit/delete buttons there. how do i implement them in my code since my approach is diferent?
is the server.php file publicly accessible?
I have edited the ques and added server.php file. H ave a look at it
Sorry, I guess what I was looking for the response from the server. See this page for the example datatables.net/release-datatables/examples/data_sources/…
1

DataTable has no edit options built in, but You can do it by connecting it with jEditable jQuery plugin, as described here: http://datatables.net/examples/api/editable.html

Also there is an ready solution right here: http://code.google.com/p/jquery-datatables-editable/

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.