Datatables : Searching at server side (php mysql) showing extra JOIN records

tester1

I am using Datatables Plugin. Its showing records properly but the issues is when i search then after search its showing me some extra records also .Below is what i have tried till now

SERVER SIDE PHP FILE :

<?php
 
    $statusidbs = 2        
    $statusidla = 3;
    $DEPT = 5;
    $DEPTa = 7;
    ?>
    
    <?php
     
     
    $link = mysql_connect('localhost', 'root', '');
    $db_selected = mysql_select_db('test', $link);
    if (!$db_selected) {
        die ('Can\'t use foo : ' . mysql_error());
    }
     
    // the columns to be filtered, ordered and returned
    // must be in the same order as displayed in the table
    $columns = array
    (
        "main.id",
        "pkt.packet_name",  
        "main.new_value",  
        "third.status_message_name",
        "rolem.role", 
        "role.role",
  
     );
     
    $table = "process AS main ";
     
    $joins = "LEFT JOIN packetid AS pkt ON main.packet_id=pkt.id
              LEFT JOIN statusmessage as third  ON main.status_id = third.id
              LEFT JOIN users AS depat ON main.assigned_to_id=depat.id 
              INNER JOIN deptroles  AS role ON depat.role_id=role.id
              LEFT JOIN deptroles  AS rolem ON main.dept_role=rolem.id
            ";

    //if i am using below where condition in above  JOIN then my search is not working 
    // as i want result with only $statusidla , $DEPT but when i search i get result with
    // $statusidla and  $statusidbs    
     
    // filtering
     $sql_where = "WHERE main.status_id=".$statusidla." AND main.dept_role=".$DEPT." ";
     
      
    if ($_GET['sSearch'] != "")
    {
        $sql_where = "WHERE ";
        foreach ($columns as $column)
        {
             $sql_where .= $column . " LIKE '%" . mysql_real_escape_string( $_GET['sSearch'] ) . "%' OR ";
        }
        $sql_where = substr($sql_where, 0, -3);
    }
    // ordering
    $sql_order = "";
    if ( isset( $_GET['iSortCol_0'] ) )
    {
        $sql_order = "ORDER BY  ";
        for ( $i = 0; $i < mysql_real_escape_string( $_GET['iSortingCols'] ); $i++ )
        {
            $sql_order .= $columns[$_GET['iSortCol_' . $i]] . " " . mysql_real_escape_string( $_GET['sSortDir_' . $i] ) . ", ";
        }
        $sql_order = substr_replace( $sql_order, "", -2 );
    }
     
    // paging
    $sql_limit = "";
    if ( isset( $_GET['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1' )
    {
        $sql_limit = "LIMIT " . mysql_real_escape_string( $_GET['iDisplayStart'] ) . ", " . mysql_real_escape_string( $_GET['iDisplayLength'] );
    }
    
      $main_query = mysql_query("SELECT SQL_CALC_FOUND_ROWS " . implode(", ", $columns) . "
                               FROM {$table} {$joins} {$sql_where} {$sql_order} {$sql_limit}")
        or die(mysql_error());
     
    // get the number of filtered rows
    $filtered_rows_query = mysql_query("SELECT FOUND_ROWS()")
        or die(mysql_error());
    $row = mysql_fetch_array($filtered_rows_query);
    $response['iTotalDisplayRecords'] = $row[0];
     
    // get the number of rows in total
    $total_query = mysql_query("SELECT COUNT(id) FROM {$table} WHERE main.status_id=".$statusidla." AND main.dept_role=".$DEPT." ")
        or die(mysql_error());
    $row = mysql_fetch_array($total_query);
    $response['iTotalRecords'] = $row[0];
     
    // send back the sEcho number requested
    $response['sEcho'] = intval($_GET['sEcho']);
     
    // this line is important in case there are no results
    $response['aaData'] = array();
     
    // finish getting rows from the main query
    while ($row = mysql_fetch_row($main_query))
    {
       
        $response['aaData'][] = $row;
    }
     
    // prevent caching and echo the associative array as json
    header('Cache-Control: no-cache');
    header('Pragma: no-cache');
    header('Expires: Mon, 26 Jul 1997 05:00:00 GMT');
    header('Content-type: application/json');
    echo json_encode($response);
     
    ?>

HTML AND AJAX

    $(document).ready(function() {
        $('#example').dataTable( {
            "bProcessing": true, 
            "bServerSide": true,
            "sAjaxSource": "scripts/server_processing.php"
        } );
    } );


    <table id="example" class="display" cellspacing="0" width="100%">
            <thead>
                <tr>
                    <th>id</th>
                    <th>packet name</th>
                    <th>value</th>
                    <th>status</th>
                    <th>dept</th>
                    <th>deptconct</th>
                </tr>
            </thead>
     
            <tfoot>
                <tr>
                    <th>id</th>
                    <th>packet name</th>
                    <th>value</th>
                    <th>status</th>
                    <th>dept</th>
                    <th>deptconct</th>
                </tr>
            </tfoot>
</table>

All is working fine. thats List of record is shown with where $statusidla only.

but when i search i get result with $statusidla and $statusidbs

but i want my search should list only record for $statusidla

Source of above : http://datatables.net/forums/discussion/2651/alternative-server-side-php-script and http://datatables.net/examples/server_side/simple.html

http://stackoverflow.com/questions/25015124/datatables-searching-at-server-side-php-mysql

tester1

because of search my all work is going to be waste ..hence any one please help me

allan ..pls help me as i am working on datatables from last 3 week and if suddenly this is not sorted then my all weeks will be waste ..as in ay listing of record ... search is impt..

allan

Wouldn't you just remove the search wildcard (i.e. the postfix %) if you don't want partial word search?

If you require urgent help with this, or any other DataTables issue priority support is available.

Allan

tester1

Hey allan ,

Thanks for response ... sir what i need is i dont want search for $statusidbs
as my records show only for $statusidla

But as i have did JOIN query hence while searching $statusidbs records is also seen ...

Sir truly speaking , i cant afford your priority paid support ... i had switch from tablesorter to datatables for easiness .. but finding many difficulties ... as i have wasted my 3 week in this part ..hence atleast this PAGE shouldn't be wasted ..only search is not working properly with JOIN QUERY ... its showing all result .. as my where condition is not set properly .

Your FREE Support for this would definitely help me lot .

Wait for best sir. :)

allan

I don't see statusidbs being used anywhere in your code, so I don't understand why it would be included in the output.

The way I would approach it myself is to echo out the SQL queries that are created and debug them. From there, you will know what the SQL you want to generate is, and can alter the building code accordingly.

Allan

tester1

thanks for the response ... will check the same :) ...