JOIN tables and GROUP_CONCAT use server side PHP

paintball85

Hi Everybody,

At this moment I works with the altered ssp class s i can join tables.

Now i really want to throw some GROUP_CONCAT and GROUP by.
The following scripts does almost what I want.
It show the rows in datatable and I have my GROUP_CONCAT field with grouped product numbers.

When I want to search though the table, I get a error.

My script:

$table = 'returns';
    $primaryKey = 'id';
    $columns = array(
        array( 'db' => '`r`.`credit_status`',               'dt' => 'DT_RowClass', 'field' => 'credit_status',  'formatter' => function( $d, $row ) 
        {
            if($row['credit_status'] == "yes") { $class = 'success'; } else { $class = ''; }
            return $class;
        }),
        array( 'db' => '`r`.`rma`',                     'dt' => 0, 'field' => 'rma',                                'formatter' => function( $d, $row ) 
        {
            return '<input class="slct_open" name="delete[]" value="'.$d.'" style="cursor: pointer;" type="checkbox">';
        }),
        array( 'db' => '`r`.`rma`',                     'dt' => 1, 'field' => 'rma' ),
        array( 'db' => '`b`.`company`',             'dt' => 2, 'field' => 'company' ),
        array( 'db' => '`c`.`name`',                'dt' => 3, 'field' => 'name' ),
        array( 'db' => '`c`.`email`',           'dt' => 4, 'field' => 'email'),
        array( 'db' => '`r`.`created`',         'dt' => 5, 'field' => 'created'),
        array( 'db' => '`r`.`rma`',                     'dt' => 6, 'field' => 'rma',                                'formatter' => function( $d, $row ) 
        {
            if(!empty($d))
            {
                $db = array ( 
                    'host'      => DATABASE_HST, 
                    'dbname'    => DATABASE_DB, 
                    'user'      => USER_DB, 
                    'pass'      => PASS_DB 
                ); 

                $db = new PDO('mysql:host='.$db['host'].';dbname='.$db['dbname'], $db['user'], $db['pass']); 
                $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); 
                $db->query("SET SESSION sql_mode = 'ANSI,ONLY_FULL_GROUP_BY'");
                $sqlCon = "
                SELECT 
                    status
                FROM
                    return_tnt
                WHERE
                    rma = '".$d."'
                ";
                $stmtCon = $db->prepare($sqlCon); 
                $stmtCon->execute(); 
                
                while($rowCon = $stmtCon->fetch(PDO::FETCH_ASSOC)) 
                {
                    if($rowCon['status']            == "pending") {     $colorCon = 'warning'; }
                    elseif($rowCon['status']    == "exception") { $colorCon = 'danger'; }
                    elseif($rowCon['status']    == "returned") {    $colorCon = 'success'; }
                    else { $colorCon = 'primary'; }
                    return '<a class="btn btn-'.$colorCon.' btn-xs" onclick="show_con(\''.$d.'\')" title="TNT PICK-UP">
                        <span class="glyphicon glyphicon-transfer" style="margin-right:0;"></span>
                    </a>';
                }
            }
        }),
        array( 'db' => '`r`.`id`',                      'dt' => 7, 'field' => 'id',                     'formatter' => function( $d, $row ) 
        {
            return '<a class="btn btn-default btn-xs" onclick="show_rma(\''.$d.'\')" id="view"><span class="glyphicon glyphicon-eye-open" style="margin-right:0;"></span></a> <a class="btn btn-primary btn-xs" onclick="show_pdf(\''.$d.'\')" id="pdf"><span class="glyphicon glyphicon-paperclip" style="margin-right:0;"></span></a> <a class="btn btn-danger btn-xs" onclick="show_confirm(\''.$d.'\')" id="remove" data-id="'.$d.'"><span class="glyphicon glyphicon-trash" style="margin-right:0;"></span></a>';
        })
        ,
        array( 'db' => 'GROUP_CONCAT(`p`.`item`) AS `item`',                        'dt' => 8, 'field' => 'item',                   'formatter' => function( $d, $row ) 
        {
            return $d;
        })
    );
    
    // SQL server connection information
    $sql_details = array(
        'user' => USER_DB,
        'pass' => PASS_DB,
        'db'   => DATABASE_DB,
        'host' => DATABASE_HST
    );
    
    require('../../classes/ssp.class2.php' );
    $joinQuery = "
    FROM 
        `returns` AS `r` 
    JOIN `return_items` AS `p` 
    ON 
        (`p`.`rma` = `r`.`rma`)
    JOIN `contacts` AS `c` 
    ON 
        (`c`.`contact_id` = `r`.`contact_id`) 
    JOIN `clients` AS `b` 
    ON 
        (`b`.`customer` = `c`.`customer_id`)";
    $extraWhere = "`r`.`status` = 'active' GROUP BY `rma`";        
    echo json_encode(
        SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns, $joinQuery, $extraWhere )
    );

Can anybody tell me why if I use the search function, the error is showing?

Capture 1 shows the working table.

Capture 2 show the error.

allan

Hi,

The demo PHP script isn't designed to handle GROUP BY and aggregation methods I'm afraid. You'd need to modify the script to allow for that (including updating the column escaping so it doesn't escape SQL function calls). Adding methods such as groupBy to the class would probably be a good way to do that.

Allan