Datatable Server Side Searching return unknown colum Error When Using alias name in query

Question

I have used datatble server side using php on my project.It was worked fine but when i going to search it returns unknown column on where error.I have attached my coding below then i can try more way but all are not working.Here is my server side coding

  <?php
 header('Content-type: Text/HTML; Charset=UTF-8');

  $type = $_REQUEST['type'];

 $table = "SELECT p.*,u.name as unit,pt.name as proname,pt.sales_unitid as sales_unitid FROM purchase p LEFT JOIN product pt ON p.pid = pt.pid LEFT JOIN unit_master u ON p.unitid = u.aid ";

$counttable = "SELECT count(purchase_id) FROM purchase p LEFT JOIN product pt ON p.pid = pt.pid LEFT JOIN unit_master u ON p.unitid = u.aid WHERE p.status = 'a' AND purchase_dt = '".$type."'";

 $cond = "p.status = 'a' AND purchase_dt = '".$type."'";

$primaryKey = 'purchase_id';


  $columns = array(
  array( 'db' => 'purchase_dtm','dt' => 0,
    'formatter' => function( $d, $row ) {
        return date( 'H:i:s', strtotime($d));
    }),
  array('db' => 'proname',
       'dt' => 1),
  array('db' => 'qty',
       'dt' => 2),
  array('db' => 'purchase_rate',
       'dt' => 3,
       'formatter' => function($d,$row) {
        return number_format($d,2);
       }),
  array('db' => 'unit',
       'dt' => 4),
  array('db' => 'purchase_id',
       'dt' => 5),
 );


 $sql_details = array(
'user' => 'msl',
'pass' => '',
'db'   => 'msl_pos',
'host' => 'localhost'
);

  require( 'ssp_x.php' );

echo json_encode(
// SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns )
SSP::mycomplex( $_GET, $sql_details, $table,$columns ,$counttable,$cond )
 );

Then my customized SSP function is

static function mycomplex ( $request, $conn, $table, $columns, $counttable, $cond )
{
    $bindings = array();
    $db = self::db( $conn );
    $db->exec("set names utf8");
    // Build the SQL query string from the request
    $limit = self::limit( $request, $columns );
    $order = self::order( $request, $columns );
    $where = self::filter( $request, $columns, $bindings );
    if($where)
    {
        $where = $where.' AND '.$cond;
    }
    else
    {
        $where = 'Where '.$cond;
    }

    // if($counttable=="")
        $counttable=$counttable;

    // Main query to actually get the data
    $data = self::sql_exec( $db, $bindings,
        "$table
         $where
         $order
         $limit"
    );
    // Data set length after filtering
    $resFilterLength = self::sql_exec( $db, $bindings,
        "$counttable
         "
    );
    $recordsFiltered = $resFilterLength[0][0];

/*
    $stmt = $db->prepare( "$table $where" );
    $stmt->execute();
    $recordsFiltered = $stmt->rowCount();*/


    // Total data set length
    $resTotalLength = self::sql_exec( $db, $bindings,
        "$counttable"
    );
    $recordsTotal = $resTotalLength[0][0];

    /*$stmt = $db->prepare( $table );
    $stmt->execute();
    $recordsTotal = $stmt->rowCount();*/

    /*
     * Output
     */
    return array(
        "draw"            => isset ( $request['draw'] ) ?
            intval( $request['draw'] ) :
            0,
        "recordsTotal"    => intval( $recordsTotal ),
        "recordsFiltered" => intval( $recordsFiltered ),
        "data"            => self::data_output( $columns, $data )
    );
}

Thanks in advance. When i going to search it return as

dataTables warning: table id=purchase - An SQL error occurred: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WHERE (`purchase_dtm` LIKE '%k%' OR `proname` LIKE '%k%' OR `qty` LIKE '%k%' OR ' at line 2

Then I have tried with original name like below.It is not worked

$table = "SELECT p.*,u.name,pt.name,pt.sales_unitid as sales_unitid FROM purchase p LEFT JOIN product pt ON p.pid = pt.pid LEFT JOIN unit_master u ON p.unitid = u.aid ";

 $columns = array(
 array( 'db' => 'purchase_dtm','dt' => 0,
    'formatter' => function( $d, $row ) {
        return date( 'H:i:s', strtotime($d));
    }),
array('db' => 'pt.name',
       'dt' => 1),
array('db' => 'qty',
       'dt' => 2),
array('db' => 'purchase_rate',
       'dt' => 3,
       'formatter' => function($d,$row) {
        return number_format($d,2);
       }),
array('db' => 'u.name',
       'dt' => 4),
array('db' => 'purchase_id',
       'dt' => 5),
);

Your code is vulnerable to SQL injection. You should use prepared statements. — Dharman, Mar 09 '20 at 10:36
You can't use the alias you set in the SELECT in your WHERE. Use the real column name there instead. — M. Eriksson, Mar 09 '20 at 10:37
@Magnus Eriksson but my other table have same name columns.. then how i use real column name — Jeya kumar G, Mar 09 '20 at 10:40
You reference it the same way as you did your select: `table_name.column_name` , so use `pt.name` in your WHERE clause — M. Eriksson, Mar 09 '20 at 10:41
@MagnusEriksson I have tried like that but it return same unknown column error then also value doesn't display on table for pt.name field. — Jeya kumar G, Mar 09 '20 at 10:48
If you get the same error, then you haven't change it in the correct place. Can you output the full generated query? — M. Eriksson, Mar 09 '20 at 10:49
@MagnusEriksson i made changes in question with what i have tried bro — Jeya kumar G, Mar 09 '20 at 10:58

score 0 · Answer 1 · answered Mar 09 '20 at 11:39

0

I was replace 'Where' by 'Having' on my filter function in ssp-class.php file

answered Mar 09 '20 at 11:39

Jeya kumar G

85
1
7

Datatable Server Side Searching return unknown colum Error When Using alias name in query

1 Answers1