php 使用jquery数据表添加、更新和删除数据库记录

Add,Update and delete records of database using jquery datatables

提问by Seeya K

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

我使用以下代码来显示使用 jquery 数据表的数据库中的数据

    $(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

这是我使用的文件 server.php。server.php 只是我给文件 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 );
 ?>

回答by marty

Addhttp://datatables.net/examples/api/add_row.html

添加http://datatables.net/examples/api/add_row.html

Inline Editing Examplehttp://datatables.net/blog/Inline_editing

内联编辑示例http://datatables.net/blog/Inline_editing

Deletehttp://datatables.net/examples/api/select_single_row.html

删除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.

我建议您在谷歌上搜索您的确切问题，并在搜索末尾添加“jQuery Datatable”。我已经使用了这个插件很多，而且这个插件的社区似乎非常好。

回答by GwynBleidD

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

DataTable 没有内置编辑选项，但您可以通过将其与 jEditable jQuery 插件连接来实现，如下所述：http://datatables.net/examples/api/editable.html

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

这里还有一个现成的解决方案：http: //code.google.com/p/jquery-datatables-editable/