Changing column names

[mlukac89]

Recently i had issue when i was using renaming columns using AS using joined tables because some tables have same column names. I was getting error undefined index: novi_naziv and search on column header and search input was not working.

$columns = array(
            array('db' => 'c.naziv AS novi_naziv', 'dt' => 1, 'field' => 'novi_naziv')
);

Using same name in fields was causing that i had same output in 2 columns in datatables, so i needed to use AS to change column name for joined tables, this is example for this scenario, you can see on image below also

$columns = array(
            array('db' => 'a.naziv', 'dt' => 1, 'field' => 'naziv'),
            array('db' => 'c.naziv', 'dt' => 2, 'field' => 'naziv')
);

FIX for this, at least it works for me

if (strpos($column['db'], 'AS') !== FALSE) {
    $column['db'] = explode('AS', $column['db'])[0];
}

those changes was made in those functions in SSP class

function order()
function filter()

you can see fork here https://github.com/mlukac89/ssp/blob/master/ssp.php

[emran]

Actually, that renaming support already exist

You can achieve that by defining the as key in the column array.

Like this

$columns = array(
            array('db' => 'c.naziv', 'dt' => 1, 'field' => 'novi_naziv', 'as' => 'novi_naziv')
);

[mlukac89]

Actually, that renaming support already exist

You can achieve that by defining the as key in the column array.

Like this

$columns = array(
            array('db' => 'c.naziv', 'dt' => 1, 'field' => 'novi_naziv', 'as' => 'novi_naziv')
);

Well, i reverted back changes to original state of yours class then used AS from yours example. When try to search in search box then i get error, here is query output and error

SELECT SQL_CALC_FOUND_ROWS b.naziv, b.registarska_oznaka, a.stanje_brojila, a.datum_servisa, a.iznos_racuna, a.slika_racuna, c.naziv AS serviser, a.redovan_servis, a.napomena, a.opis, a.sljedeci_servis_km, a.id 
        FROM servisi_vozila AS a 
        LEFT JOIN vozila AS b ON (b.id = a.id_vozila) 
        LEFT JOIN tvrtke AS c ON (c.id = a.serviser) 
        ORDER BY c.naziv AS serviser ASC LIMIT 0, 10 // note this one is that makes issue "AS serviser"


PDOException: 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 'AS serviser LIKE '%bui%' OR a.redovan_servis LIKE '%bui%' OR a.napomena LIKE ...' at line 3 in

and when clicked on column to sort then i get this error

SELECT SQL_CALC_FOUND_ROWS b.naziv, b.registarska_oznaka, a.stanje_brojila, a.datum_servisa, a.iznos_racuna, a.slika_racuna, c.naziv AS serviser, a.redovan_servis, a.napomena, a.opis, a.sljedeci_servis_km, a.id 
        FROM servisi_vozila AS a 
        LEFT JOIN vozila AS b ON (b.id = a.id_vozila) 
        LEFT JOIN tvrtke AS c ON (c.id = a.serviser) 
        WHERE (b.naziv LIKE :binding_0 OR b.registarska_oznaka LIKE :binding_1 OR a.stanje_brojila LIKE :binding_2 OR a.datum_servisa LIKE :binding_3 OR a.iznos_racuna LIKE :binding_4 OR a.slika_racuna LIKE :binding_5 OR c.naziv AS serviser LIKE :binding_6 OR a.redovan_servis LIKE :binding_7 OR a.napomena LIKE :binding_8 OR a.opis LIKE :binding_9 OR a.sljedeci_servis_km LIKE :binding_10 OR a.id LIKE :binding_11) 
        ORDER BY c.naziv AS serviser DESC LIMIT 0, 10

PDOException: 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 'AS serviser LIKE '%bui%' OR a.redovan_servis LIKE '%bui%' OR a.napomena LIKE ...' at line 3 in

here is how this is produced

public function get_vehicle_service_by_id($vehicle_id)
	{
        $primaryKey = 'id';

        $joinQuery = "FROM {$this->table} AS a LEFT JOIN vozila AS b ON (b.id = a.id_vozila) LEFT JOIN tvrtke AS c ON (c.id = a.serviser)";
        $where = "a.id_vozila = {$vehicle_id}";
        
        $columns = array(
            array(
                'db' => 'a.datum_servisa', 
                'dt' => 0, 
                'field' => 'datum_servisa',
                'formatter' => function($d, $row) {
                    return date('d.m.Y', strtotime($d));
                }
            ),
            array('db' => 'a.iznos_racuna', 'dt' => 1, 'field' => 'iznos_racuna'),
            array(
                'db' => 'a.slika_racuna',
                'dt' => 2,
                'field' => 'slika_racuna',
                'formatter' => function($d, $row) {
                    return $d != null ? '<a href="'.$d.'" target="_blank" rel="noopener noreferrer">Pogledaj račun</a>' : '';
                }
            ),
            array('db' => 'c.naziv', 'dt' => 3, 'field' => 'serviser', 'as' => 'serviser'),
            array(
                'db' => 'a.redovan_servis', 
                'dt' => 4, 
                'field' => 'redovan_servis',
                'formatter' => function($d, $row) {
                    return $d == 1 ? 'Da' : 'Ne';
                }
            ),
            array('db' => 'a.napomena', 'dt' => 5, 'field' => 'napomena'),
            array('db' => 'a.opis', 'dt' => 6, 'field' => 'opis'),
            array('db' => 'a.sljedeci_servis_km', 'dt' => 7, 'field' => 'sljedeci_servis_km'),
            array(
                'db' => 'a.id', 
                'dt' => 8, 
                'field' => 'id',
                'formatter' => function($d, $row) {
                    return '<a href="javascript:void(0);" title="Uredi" onclick="edit_service('.$d.')"><i class="fa fa-pencil icon text-warning"></i></a>&nbsp;&nbsp;&nbsp;&nbsp;
            <a href="javascript:void(0)" title="Izbriši" onclick="delete_service('.$d.')"><i class="fa fa-trash icon text-danger"></i></a>';
                }
             )
        );

        $sql_details = array(
            'user' => $this->db->username,
            'pass' => $this->db->password,
            'db'   => $this->db->database,
            'host' => $this->db->hostname,
            'charset' => 'utf8'
        );
                 
        echo json_encode(
            SSP::simple( $_POST, $sql_details, $this->table, $primaryKey, $columns, $joinQuery, $where)
        );
	}

EDIT: if i use mine fix in code all is working on both ways

array('db' => 'c.naziv', 'dt' => 3, 'field' => 'serviser', 'as' => 'serviser')

or this

array('db' => 'c.naziv AS serviser', 'dt' => 3, 'field' => 'serviser')