Search does not work when using multiple where sql

Search does not work when using multiple where sql

taufik-kurahmantaufik-kurahman Posts: 7Questions: 2Answers: 0

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

This question has an accepted answers - jump to answer

Answers

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    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-kurahmantaufik-kurahman Posts: 7Questions: 2Answers: 0

    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.

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    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-kurahmantaufik-kurahman Posts: 7Questions: 2Answers: 0

    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

  • allanallan Posts: 61,438Questions: 1Answers: 10,049 Site admin
    Answer ✓

    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-kurahmantaufik-kurahman Posts: 7Questions: 2Answers: 0

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

This discussion has been closed.