Search does not work when using multiple where sql

taufik-kurahman

Hi,
I am trying to make multiple where condition in server side processing and it's working,
but the search does not work.

HTML :

<table id="example" class="table table-striped table-bordered" style="border-spacing:0px;">
        <thead>
          <tr>
            <th>No</th>
            <th>Nama</th>
            <th>Tgl Lahir</th>
            <th>Jenis Kelamin</th>
            <th>Status Layanan</th>
            <th>Pendamping</th>
            <th class="text-center">Actions</th>    
          </tr>
        </thead>
</table>

JS :

$(document).ready(function () {
        $('#example').dataTable({
          "processing": true,
          "serverSide": true,
          "ajax": "./inc/load_report.php",
          "aoColumns": [
                  null,
                  null,
                  null,
            null,
                  null,
                  null,
                  {
              "className": "text-center",
                    render: function ( data, type, row ) {
                        return '<a href="?content=report&action=choose&idpm='+row['6']+'&idpes='+row['7']+'" class="btn btn-primary" style="width:71px;">Detail</a>';
                      }
                  }
                ]
        });
    });

Server side :

$table = <<<EOT
 (
    SELECT A.*,B.uname_pes,B.nama_pes FROM pmks A INNER JOIN pes B ON A.id_peksos=B.uname_pes
 ) temp
EOT;

$primaryKey = 'id_pmks';

$columns = array(
    array( 'db' => 'nama_pmks','dt' => 1 ),
    array( 'db' => 'tanggal_lahir_pmks','dt' => 2 ),
    array( 'db' => 'jenis_kelamin_pmks','dt' => 3 ),
    array( 'db' => 'status_layanan_pmks','dt' => 4 ),
    array( 'db' => 'nama_pes','dt' => 5 ),
    array( 'db' => 'id_pmks','dt' => 6 ),
    array( 'db' => 'uname_pes','dt' => 7 )
);

$sql_details = array(
    'user' => 'my_user',
    'pass' => 'my_pass',
    'db'   => 'my_db',
    'host' => 'localhost'
);

require( 'ssp_join.class.php' );

$where="status_layanan_pmks='Reguler' AND id_peksos!='' OR status_layanan_pmks='Homecare' AND id_peksos!=''";
$result=SSP::complex( $_GET, $sql_details, $table, $primaryKey, $columns, null, $where);
$start=$_REQUEST['start']+1;
$idx=0;
foreach($result['data'] as &$res){
    $res[0]=(string)$start;
    $start++;
    $idx++;
}
echo json_encode($result);

Please help me. Thanks

colin

Hi @taufik-kurahman ,

When you say, "it doesn't work", what does that? Does it return nothing? Does it give an error?

The best bet would be to give a live example so we can look and see what's happening.

Cheers,

Colin

taufik-kurahman

Hi @colin
that is, the search box can not search individual columns as usual, even the data is not sorted.
Here is the link https://www.sidadulansia.com/staff/dt.php
Thanks.

colin

Yep , it looks like a problem with the server-side script - it's returning records that clearly do not match. I'm not too familiar with the server-side PHP scripts unfortunately, but have you tried looking at the canned examples and the pages on the site.

taufik-kurahman

Yes iam, FYI when i change WHERE statement with only one condition for example :

$where="status_layanan_pmks='Reguler'";

the search is working again.
Please help me

allan

The columns appear to order correctly for me. I agree that the search doesn't appear to be working.

Try using SSP::simple() without your custom where condition to start with and see if that works for search. My guess is that your custom where is causing the issue as it will need parenthesis to correctly group and prioritise the matching.

Allan

taufik-kurahman

Wow thanks @allan , yes it's fix my problem. The solution is simple as the method name
$result=SSP::simple($_GET, $sql_details, $table, $primaryKey, $columns, null, $where);
Thanks again.