Multiple OR in where clause breaks filter/search

janpan

Hi,

I am using server side processing and everything works 100%.

However, when I have multiple OR statements in my where clause, the filter/search functionality stops working i.e. does not filter on any column.

Without the OR statements in my Where clause, it filters fine.

Any ideas ?

ThomD

Code sample please.

janpan

As requested, here is the code. Just again to explain, filtering works 100% for all user type e.g. Submitter, however, not for the line manager (see server side script where I have multiple OR statments in where clause)

My datatables initialisation:

table = $('#tablesubmissions').DataTable(
                {
                    
                    "order": [[1,'DESC']],
                    "deferRender": true,
                    "bJQueryUI": true,
                    "bPaginate": true,
                    "bStateSave": true,
                    "processing": true,
                    "serverSide": true,
                    "sPaginationType": "full_numbers",
                    "ajax":
                    {
                        "url": "view_Submissions_remote.php",
                        "data":
                        {
                            "role": "".$_SESSION['role']."",
                            "email": "".$_SESSION['email']."",
                            "country": "".$_SESSION['country']."",
                            "primarypractiseid": "".$_SESSION['primarypracticeid'].""
                        }
                    },
                    "columns":[
                        { "data": "first_number", "bSortable": true },
                        { "data": "submission_date", "bSortable": true },
                        { "data": "submitted_by", "bSortable": true },
                        { "data": "location_name", "bSortable": true }
                    ]});

My server side script (view_Submissions_remote.php):

<?php
    /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     * 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)
     */
     
    session_start();
        
    //Connect
    include('conf/config.php');
        

    if(!isValidLogin())
    {
            badRequest();
    }
        
        // POST variables
        // DB table to use
        $table = 'submissions';

        // Table's primary key
        $primaryKey = 'id';

        $role = mysql_real_escape_string($_REQUEST["role"]);
        $email = mysql_real_escape_string($_REQUEST["email"]);
        $country = mysql_real_escape_string($_REQUEST["country"]);
        $primarypractiseid = mysql_real_escape_string($_REQUEST["primarypractiseid"]);
        
        // Columns
    $columns = array(
            array(
                'db' => 'id',
                'dt' => 'DT_RowId',
                'formatter' => function( $d, $row )
                {
                    return $d;
                }
            ),
            array( 'db' => 'firstnumber',   'dt' => 'first_number' ),
            array( 'db' => 'datecreated',  'dt' => 'submission_date'),
            array( 'db' => 'username',   'dt' => 'submitted_by' ),
            array( 'db' => 'areaname',   'dt' => 'location_name' )
            );

        // SQL server connection information
        $sql_details = array(
                'user' => DBUSER,
                'pass' => DBUSERPASS,
                'db'   => DBNAME,
                'host' => DBHOST
        );
 
        
        require( 'libraries/DataTables-1.10.9/examples/server_side/scripts/ssp.class.php' );
 
        if($role == "Submitter")
        {
            echo json_encode(SSP::complex( $_GET, $sql_details, $table, $primaryKey, $columns, "","username='".$email."'"));
        }
        else if($role == "Line Manager")
        {
            $whileLineManager = "";
            $result454 = doSELECTQuery("SELECT email FROM users WHERE primarypractiseid=$primarypractiseid");
            
            while($row = mysql_fetch_array($result454))
            {
                if($whileLineManager=="")
                {
                    $username = $row["email"];
                    $whileLineManager .= " username='$username'";
                } 
                else
                {
                    $username = $row["email"];
                    $whileLineManager .= " OR username='$username'";
                }
            }
            
            echo json_encode(SSP::complex( $_GET, $sql_details, $table, $primaryKey, $columns, "",$whileLineManager));
        }
        else
        {
            echo json_encode(SSP::complex( $_GET, $sql_details, $table, $primaryKey, $columns, "","firstnumber <>''"));
        }

janpan

bump, any ideas ?

allan

I would suggest modifying the SSP class to echo out the SQL query it generates. That should give a clue as to why it isn't working. It might be that you just need to add () around the clause.

Allan

janpan

Thanks Allan, I'll give it a try.

janpan

Wow ! That was it, thanks @allan !