Search button and page section are not working.

Search button and page section are not working.

rainkvrainkv Posts: 8Questions: 2Answers: 0

I use server side processing datatables with sql server to display the data on my page but it seems that the search button, pagination, show entries are not working.
Code Index.php

<html xmlns="http://www.w3.org/1999/xhtml">
<head>
    <title>Test</title>

    <meta http-equiv="X-UA-Compatible" content="IE=edge">
<link rel="stylesheet" href="dataTables/jquery.dataTables.min.css">
<link rel="stylesheet" href="dataTables/bootstrap.min.css">
<!--<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">-->
<script src="dataTables/jQuery-3.3.1/jquery-3.3.1.min.js"></script>
<script src="dataTables/jquery.dataTables.min.js"></script>
    <script type="text/javascript">
        $(document).ready(function () {
            $('table.display').dataTable({
                "processing": true,
                "serverSide": true,
                "paging": true,  
                 "ordering": true,  
                 "filter": true,  
               "ajax": "Processing.php"

            })
        });
    </script>
</head>
<body>
    <div>
       <table id="example" class="display" style="width:100%">
        <thead>
            <tr>
                <th>First name</th>
                <th>Last name</th>

            </tr>
        </thead>
        <tfoot>
            <tr>
                <th>First name</th>
                <th>Last name</th>

            </tr>
        </tfoot>
    </table>
    </div>
</body>
</html>`

and code Processing.php

<?php
    /* Indexed column (used for fast and accurate table cardinality) */
    $sIndexColumn = "Student_Id";

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

    /* Database connection information */
    $gaSql['user']       = "sa";
    $gaSql['password']   = "1234567";
    $gaSql['db']         = "StudentManager";
    $gaSql['server']     = "192.168.1.150";

    /*
    * Columns
    * If you don't want all of the columns displayed you need to hardcode $aColumns array with your elements.
    * If not this will grab all the columns associated with $sTable
    */
    $aColumns = array("NameStudent","ClassStudent");


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

    /*
     * ODBC connection
     */
    $connectionInfo = array("UID" => $gaSql['user'],"characterSet"=>"UTF-8", "PWD" => $gaSql['password'], "Database"=>$gaSql['db'],"ReturnDatesAsStrings"=>true);
    $gaSql['link'] = sqlsrv_connect( $gaSql['server'], $connectionInfo);
    $params = array();
    $options =  array( "Scrollable" => SQLSRV_CURSOR_KEYSET );


    /* 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] ) ]."
                    ".addslashes( $_GET['sSortDir_'.$i] ) .", ";
            }
        }
        $sOrder = substr_replace( $sOrder, "", -2 );
        if ( $sOrder == "ORDER BY" ) {
            $sOrder = "";
        }
    }

    /* Filtering */
    $sWhere = "";
    if ( isset($_GET['sSearch']) && $_GET['sSearch'] != "" ) {
        $sWhere = "WHERE (";
        for ( $i=0 ; $i<count($aColumns) ; $i++ ) {
            $sWhere .= $aColumns[$i]." LIKE '%".addslashes( $_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 '%".addslashes($_GET['sSearch_'.$i])."%' ";
        }
    }

    /* Paging */
    $top = (isset($_GET['iDisplayStart']))?((int)$_GET['iDisplayStart']):0 ;
    $limit = (isset($_GET['iDisplayLength']))?((int)$_GET['iDisplayLength'] ):25;
    $sQuery = "SELECT TOP $limit ".implode(",",$aColumns)."
        FROM $sTable
        $sWhere ".(($sWhere=="")?" WHERE ":" AND ")." $sIndexColumn NOT IN
        (
            SELECT $sIndexColumn FROM
            (
                SELECT TOP $top ".implode(",",$aColumns)."
                FROM $sTable
                $sWhere
                $sOrder
            )
            as [virtTable]
        )
        $sOrder";

    $rResult = sqlsrv_query($gaSql['link'],$sQuery) or die("$sQuery: " . sqlsrv_errors());

    $sQueryCnt = "SELECT * FROM $sTable $sWhere";
    $rResultCnt = sqlsrv_query( $gaSql['link'], $sQueryCnt ,$params, $options) or die (" $sQueryCnt: " . sqlsrv_errors());
    $iFilteredTotal = sqlsrv_num_rows( $rResultCnt );

    $sQuery = " SELECT * FROM $sTable ";
    $rResultTotal = sqlsrv_query( $gaSql['link'], $sQuery ,$params, $options) or die(sqlsrv_errors());
    $iTotal = sqlsrv_num_rows( $rResultTotal );

    $output = array(
        "sEcho" => intval($_GET['sEcho']),
//       
        "iTotalRecords" => $iTotal,
        "iTotalDisplayRecords" => $iFilteredTotal,
        "aaData" => array()
    );

    while ( $aRow = sqlsrv_fetch_array( $rResult ) ) {
        $row = array();
        for ( $i=0 ; $i<count($aColumns) ; $i++ ) {
            if ( $aColumns[$i] != ' ' ) {
                $v = $aRow[ $aColumns[$i] ];
                $v = mb_check_encoding($v, 'UTF-8') ? $v : utf8_encode($v);
                $row[]=$v;
            }
        }
        If (!empty($row)) { $output['aaData'][] = $row; }
    }  
    echo json_encode( $output );
?>

I get this error message
Notice: Undefined index: sEcho in C:\xampp\htdocs\MyPHP\Processing.php on line 103
{"sEcho":0,"iTotalRecords":68156,"iTotalDisplayRecords":68156,"aaData":[["18000001","NGUY\u1ec4N NG\u1eccC ANH"]

Looking forward to the tutorial, I have just learned about php so maybe I do not understand much. Many thanks!

Answers

  • allanallan Posts: 50,303Questions: 1Answers: 7,478 Site admin

    You are using a legacy server-side processing script there. It uses the old hungarian notation parameters rather than the newer camelCase.

    Try using the new SSP class. Examples on how to use it here.

    Allan

  • rainkvrainkv Posts: 8Questions: 2Answers: 0

    It seems that it is using mysql. And I use Sql Server to get data.

  • rainkvrainkv Posts: 8Questions: 2Answers: 0

    Can someone help me?

  • colincolin Posts: 5,893Questions: 0Answers: 1,020

    Did you use the new code as Allan suggested? What was the result?

  • rainkvrainkv Posts: 8Questions: 2Answers: 0

    Does the code that Allan recommends seem to use mysql? I use sqlsrv to connect, so I don't know how to move from mysql to sqlsrv. Can you help me? Thank you!

  • colincolin Posts: 5,893Questions: 0Answers: 1,020

    The PDO library deals with the DB types. Have you tried it?

Sign In or Register to comment.