In Datatable, if we have a column name with space in server side rendering datatable then i am getting this error

message: "SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Number asc LIMIT 0, 20' at line 1 "

consider, we have a columns as

"Phone Number" => [
            "label" => "Mobile Number"
        ]

I think it is creating a query with Phone Number asc instead of "Phone Number" asc for sorting purpose... can you share how can i achieve working it without error?

I am getting ajax response as

{"draw":2,"recordsTotal":5,"recordsFiltered":5,"data":[{"Procure Id":1,"Email":"sss@gmail.com","Name":"dvbi","Phone Number":"9999999999"},{"Procure Id":1,"Email":"sss@gmail.com","Name":"dvbi","Phone Number":"9999999999"},{"Procure Id":1,"Email":"sss@gmail.com","Name":"dvbi","Phone Number":"9999999999"},{"Procure Id":1,"Email":"sss@gmail.com","Name":"dvbi","Phone Number":"9999999999"},{"Procure Id":1,"Email":"sss@gmail.com","Name":"dvbi","Phone Number":"9999999999"}]}

if i add column name as "`Phone Number`" then it is not mapping with "Phone Number" data from Response... is that possible can i edit this ajax response or any other work arounds?

Hi Sebestian, I have resolved above sorting issue with your suggestion using backtick and while retriving data mapped without backtick by using below command

 "dataSource" => function ($scope) {
                        return $this->src('mysql')
                            ->query($this->paramQuery)
                            ->params($this->sqlParams)
                            ->pipe(new \koolreport\processes\Map([
                                "{value}" => function ($row) {
                                    foreach ($this->columns as $key => $value) {
                                        $removedBacktickkey = str_replace('`', '', $key);
                                        $row[$key] = $row[$removedBacktickkey];
                                    }
                                    return $row;
                                }
                            ]));
                    },

Now i am facing issue on search... i can't able to search using datatable search bar i am getting this below error

"SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':`Updated At`_search_all_0_0_0 OR Email like ?))' at line 1

can you give me the suggestion what i can do to make it work ?

I have another issue too... i can't able to make datatable name concat by uniqid() or rand() functions that available in php in server-side rendering in client side it is working good... i need dynamic unique name what can i do for it?

  DataTables::create(array(
                    "name" => $this->name . uniqid(),
                    "columns" => $this->columns,

i am getting the following response

Hi Sebestian, May i know the response of above queries? Thanks in advance

Hi, when i debug the issue of making datatable name name . uniqid() i got the error from this line

var start = data.indexOf(markStart);

it is throwing -1 instead of number hope this will help you for further investigation.. and please give me the solution as soon as you are back from holidays Thanks :)

Thanks for replying.. i will check its implementation i have another doubt.. in the query we have

Select  COUNT(users.id) AS "count(Users_Id)" FROM users

and in datatable, i am sending column array as

"count(Users_Id)" => [
                "label" => Users Count
        ];

then i am getting this following error

kindly suggest the solution for it

Thanks for fast replying, No, query is generating dynamically... any other solution.. like if i didn't pass columns array it is rendering properly but when i passed column array as mentioned above it is throwing error...