JQuery, PHP, MYSQL, DataTable невозможно найти

I’m trying to get DataTable to work with INNER JOIN MYSQL QUERY and I made it, but i’ve found a problem with Searching in form.

Ошибка говорит: DataTables предупреждение: таблица id=material_used-недопустимый ответ JSON. Дополнительные сведения об этой ошибке см. В разделе http://datatables.net/tn/1

Ну, вот мой код :

индекс.формат html

<head>
    <link rel="stylesheet" href="//cdn.datatables.net/1.10.10/css/jquery.dataTables.min.css"/>
    <script src="//code.jquery.com/jquery-1.11.3.min.js"></script>
    <script src="//cdn.datatables.net/1.10.10/js/jquery.dataTables.min.js"></script>
</head>
<body>

<table cellpadding="1" cellspacing="1" id="material_used" class="display" width="100%">
    <thead>
    <tr>
        <th><div align="center"><font size="2">Date</font></div></th>
        <th><div align="center"><font size="2">Matl Code</font></div></th>
        <th><div align="center"><font size="2">Name</font></div></th>
        <th><div align="center"><font size="2">Spesification</font></div></th>
        <th><div align="center"><font size="2">Qty</font></div></th>
        <th><div align="center"><font size="2">Note</font></div></th>
        <th><div align="center"><font size="2">Reg. No</font></div></th>
        <th><div align="center"><font size="2">WO / Sheet</font></div></th>
        <th><div align="center"><font size="2">Action</font></div></th>
    </tr>
    </thead>
    <tfoot>
        <th><div align="center"><font size="2">Date</font></div></th>
        <th><div align="center"><font size="2">Matl Code</font></div></th>
        <th><div align="center"><font size="2">Name</font></div></th>
        <th><div align="center"><font size="2">Spesification</font></div></th>
        <th><div align="center"><font size="2">Qty</font></div></th>
        <th><div align="center"><font size="2">Note</font></div></th>
        <th><div align="center"><font size="2">Reg. No</font></div></th>
        <th><div align="center"><font size="2">WO / Sheet</font></div></th>
        <th><div align="center"><font size="2">Action</font></div></th>
    </tfoot>
    
</table>

<script type="text/javascript">
function getWOandSheet(data, type, dataToSet) {
    return data.work_order + " / " + data.sheet_number;
}
    $(document).ready(function () {
        $('#material_used').DataTable({

            "processing": true,
            "serverSide": true,
            "bDestroy": true,
            "deferRender": true,

            "columns": [
                {"data": "date"},
                {"data": "material_code"},
                {"data": "name"},
                {"data": "specs"},
                {"data": "qty_act"},
                {"data": "note_act"},
                {"data": "reg_no"},
                {"data": getWOandSheet},
                { "mData": null , //its null here because history column will contain the mRender
                "mRender" : function ( data, type, full ) {
                    return '<a href="edit_material_used.php?cc='+data.code+'">Edit</a> / <a href="delete_mat_used.php?cc='+data.code+'">Delete</a>';}
                }
            ],
            "ajax": {
                url: 'process.php',
                type: 'POST'
            }
        });
    });
</script>
</body>

процесс.РНР

<?php
/*
 * For more details
 * please check official documentation of DataTables  https://datatables.net/manual/server-side
 * Coded by charaf JRA
 * RefreshMyMind.com
 */

/* IF Query comes from DataTables do the following */
if (!empty($_POST) ) {

    /*
     * Database Configuration and Connection using mysqli
     */

    define("HOST", "localhost");
    define("USER", "root");
    define("PASSWORD", "");
    define("DB", "kdm");
    define("MyTable", "mat_used_master_data");

    $connection = mysqli_connect(HOST, USER, PASSWORD, DB) OR DIE("Impossible to access to DB : " . mysqli_connect_error());

    /* END DB Config and connection */

    /*
     * @param (string) SQL Query
     * @return multidim array containing data array(array('column1'=>value2,'column2'=>value2...))
     *
     */
    function getData($sql){
        global $connection ;//we use connection already opened
        $query = mysqli_query($connection, $sql) OR DIE ("Can't get Data from DB , check your SQL Query " );
        $data = array();
        foreach ($query as $row ) {
            $data[] = $row ;
        }
        return $data;
    }

    /* Useful $_POST Variables coming from the plugin */
    $draw = $_POST["draw"];//counter used by DataTables to ensure that the Ajax returns from server-side processing requests are drawn in sequence by DataTables
    $orderByColumnIndex  = $_POST['order'][0]['column'];// index of the sorting column (0 index based - i.e. 0 is the first record)
    $orderBy = $_POST['columns'][$orderByColumnIndex]['data'];//Get name of the sorting column from its index
    $orderType = $_POST['order'][0]['dir']; // ASC or DESC
    $start  = $_POST["start"];//Paging first record indicator.
    $length = $_POST['length'];//Number of records that the table can display in the current draw
    /* END of POST variables */

    $recordsTotal = count(getData("SELECT * FROM ".MyTable));

    /* SEARCH CASE : Filtered data */
    if(!empty($_POST['search']['value'])){

        /* WHERE Clause for searching */
        for($i=0 ; $i<count($_POST['columns']);$i++){
            $column = $_POST['columns'][$i]['data'];//we get the name of each column using its index from POST request
            $where[]="$column like '%".$_POST['search']['value']."%'";
        }
        $where = "WHERE ".implode(" OR " , $where);// id like '%searchValue%' or name like '%searchValue%' ....
        /* End WHERE */

        $sql = sprintf("SELECT * FROM %s %s", MyTable , $where);//Search query without limit clause (No pagination)

        $recordsFiltered = count(getData($sql));//Count of search result

        /* SQL Query for search with limit and orderBy clauses*/
        $sql = sprintf("SELECT a.code, a.date, a.material_code, b.name, b.specs, a.qty_act, a.note_act, a.reg_no, a.work_order, a.sheet_number FROM mat_used_master_data = a INNER JOIN material_tools_master_data = b ON a.material_code = b.material_code ORDER BY %s %s limit %d , %d ", $where ,$orderBy, $orderType ,$start,$length  );
        $data = getData($sql);
    }
    /* END SEARCH */
    else {
        $sql = sprintf("SELECT a.code, a.date, a.material_code, b.name, b.specs, a.qty_act, a.note_act, a.reg_no, a.work_order, a.sheet_number FROM mat_used_master_data = a INNER JOIN material_tools_master_data = b ON a.material_code = b.material_code ORDER BY %s %s limit %d , %d " ,$orderBy,$orderType ,$start , $length);
        $data = getData($sql);

        $recordsFiltered = $recordsTotal;
    }

    /* Response to client before JSON encoding */
    $response = array(
        "draw" => intval($draw),
        "recordsTotal" => $recordsTotal,
        "recordsFiltered" => $recordsFiltered,
        "data" => $data
    );

    echo json_encode($response);

} else {
    echo "NO POST Query from DataTable";
}
?>

Итак, вот результат JSON после загрузки страницы :

{draw: 1, recordsTotal: 3578, recordsFiltered: 3578,…}
data
:
[{code: "1", date: "2016-01-04", material_code: "K0000285", name: "Cutting Wheel",…},…]
draw
:
1
recordsFiltered
:
3578
recordsTotal
:
3578

{"draw":1,"recordsTotal":3578,"recordsFiltered":3578,"data":[{"code":"1","date":"2016-01-04","material_code":"K0000285","name":"Cutting Wheel","specs":"Nippon Resibon-A30P BF@25EA","qty_act":"5","note_act":null,"reg_no":"OW001","work_order":"Non Work Order","sheet_number":null},{"code":"2","date":"2016-01-04","material_code":"K0000371","name":"Electrode  ROD (Stick)","specs":"niko steel","qty_act":"20","note_act":null,"reg_no":"OW001","work_order":"Non Work Order","sheet_number":null},{"code":"3","date":"2016-01-04","material_code":"K0000502","name":"Grinding Wheel","specs":"Brand: Ultra","qty_act":"15","note_act":null,"reg_no":"OW001","work_order":"Non Work Order","sheet_number":null},{"code":"4","date":"2016-01-04","material_code":"K0000543","name":null,"specs":null,"qty_act":"3","note_act":null,"reg_no":"OW001","work_order":"Non Work Order","sheet_number":null},{"code":"5","date":"2016-01-07","material_code":"K0000285","name":"Cutting Wheel","specs":"Nippon Resibon-A30P BF@25EA","qty_act":"3","note_act":null,"reg_no":"OW001","work_order":"Non Work Order","sheet_number":null},{"code":"6","date":"2016-01-07","material_code":"K0000286","name":"Cutting Wheel","specs":"Brand : Ultra","qty_act":"10","note_act":null,"reg_no":"OW001","work_order":"Non Work Order","sheet_number":null},{"code":"7","date":"2016-01-07","material_code":"K0000502","name":"Grinding Wheel","specs":"Brand: Ultra","qty_act":"10","note_act":null,"reg_no":"OW001","work_order":"Non Work Order","sheet_number":null},{"code":"8","date":"2016-01-07","material_code":"K0000851","name":"Paint DASAR ( EPOXY )","specs":"KANZEI","qty_act":"20","note_act":null,"reg_no":"OW001","work_order":"Non Work Order","sheet_number":null},{"code":"9","date":"2016-01-07","material_code":"K0001204","name":"Thinner","specs":"HG/HIGT GLASS","qty_act":"40","note_act":null,"reg_no":"OW001","work_order":"Non Work Order","sheet_number":null},{"code":"10","date":"2016-01-12","material_code":"K0000847","name":"Paint","specs":"no.115-312,Kansai@20 L/can","qty_act":"20","note_act":null,"reg_no":"OA001","work_order":"Non Work Order","sheet_number":null}]}

И вот результат после того, как я набираю что-то, а затем появляется ошибка :

Can't get Data from DB , check your SQL Query

Кто-нибудь может мне помочь?

спасибо,

2 ответа

  1. Похоже, что SQL неверен при сглаживании таблицы

    FROM mat_used_master_data = a INNER JOIN material_tools_master_data = b
    

    должно быть

    FROM mat_used_master_data AS a INNER JOIN material_tools_master_data AS b
    

    Это сделало бы его так, что ваш json не создан правильно. Кроме того, вы, вероятно, не хотите

    echo "NO POST Query from DataTable";
    

    Вместо этого отправьте пустой объект json.

  2. Неважно, я сделал это. xD

    Вот что я сделал, я изменил весь процесс.php код с этим кодом :

    процесс.РНР

    <?php
        /*
         * Script:    DataTables server-side script for PHP and MySQL
         * Copyright: 2010 - Allan Jardine, 2012 - Chris Wright
         * License:   GPL v2 or BSD (3-point)
         */
         
        /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
         * 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( 'date', 'material_code', 'name', 'specs', 'qty_act', 'note_act', 'work_order','sheet_number','reg_no');
         
        /* Indexed column (used for fast and accurate table cardinality) */
        $sIndexColumn = "code";
         
        /* DB table to use */
        $sTable = "mat_used_master_data";
         
        /* Database connection information */
        $gaSql['user']       = "root";
        $gaSql['password']   = "";
        $gaSql['db']         = "kdm";
        $gaSql['server']     = "localhost";
         
         
        /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
         * 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_pconnect( $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 ' );
        }
         
         
        /*
         * 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 a.code, a.date, a.material_code, b.name, b.specs, a.qty_act, a.note_act, a.reg_no, a.work_order, a.sheet_number
            FROM mat_used_master_data AS a INNER JOIN material_tools_master_data AS b ON a.material_code = b.material_code
            $sWhere
            $sOrder
            $sLimit
        ";
        $rResult = mysql_query( $sQuery, $gaSql['link'] ) or fatal_error( 'MySQL Error: ' . mysql_errno() );
         
        /* Data set length after filtering */
        $sQuery = "
            SELECT FOUND_ROWS()
        ";
        $rResultFilterTotal = mysql_query( $sQuery, $gaSql['link'] ) or fatal_error( 'MySQL Error: ' . mysql_errno() );
        $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 Error: ' . mysql_errno() );
        $aResultTotal = mysql_fetch_array($rResultTotal);
        $iTotal = $aResultTotal[0];
         
         
        /*
         * 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] == "version" )
                {
                    /* 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_encode( $output );
    ?>

    И вуаля! теперь это работает. Спасибо, кстати, за помощь @nerdlyist. Я действительно ценю это.