Populating the table with RAWSQL query
Populating the table with RAWSQL query
Khalid Teli
Posts: 251Questions: 71Answers: 0
I am using the raw sql query below to populate some columns in the datatble which is already created using Editor data-tables. I am not sure if I am doing it correctly. I believe that the** if ( ! isset( $_POST['action'] ) ) {** is not working as it should work.
How should the JSON response look like?
In my case I would like JSON to contain all the data 1) From RAW sql to populate few columns and 2) From Editor to populate renaming columns (else condition).
p.s i tried to remove the if condition so i can read all the data in one JSON and then handle them differently on client side but it is showing me** Error: Parse error**
**// if ( ! isset( $_POST['action'] ) ) {
$rawquery = "SELECT start AS time
FROM contract_bal";
$data = $db->sql( $rawquery )->fetchAll();
echo json_encode( array(
'data' => $data
) );
// }else{
......editor stuff
}**
and on the client side i included this:
**columns[
......
{ data: "time" }
.....
]**
This question has an accepted answers - jump to answer
This discussion has been closed.
Answers
What is the response from the server? The details in this tech note will show you how to get that.
Allan
Thank you @allan
1) when i don't use the if condition , the output looks like below, it reads both from RAWSQL ans EDITOR and the error it shows id parse error on line 9 in bold
2) when i put the if condition
**~~(if ( isset( $_POST['action'] ) ) {)~~**it just, i guess it takes both RAWSQL ans Editor as post action and error shows unknown field for "starty" and for**~~(if ( ! isset( $_POST['action'] ) ) {)~~**there is no response3) Then I tried just using the RAW SQL query on server-side and successfully populate the datatatable but the problem is that there is no searching facility. I have to disable the server-side from client side in order to search the datatable
That looks like it is sending two different JSON objects back. That isn't valid JSON.
Also the empty return from 2 isn't valid JSON either, hence the error you are seeing. So there are some issues with the server-side script. Can you show me what you have please? Are you using our PHP libraries?
Allan
@allan Thank you.
yes, I use PHP libraries.
you are right, it returns two JSON objects which are concatenated and therefore resultant JSON is not a valid JSON.
Please find my server-side script below
It is as if the
if/elseis executing both blocks! I think this line is wrong though:It should be:
Allan
@allan
Thank you. That was a silly mistake from my side. However, I changed this and nothing changed.
It seems when it is not set (
if ( ! isset($_POST['action'] )) { ) it ready the data from raw sql query in if statement and doesn't go out of if loop to else statement.please look at the error below:
**DataTables warning: table id=contracts - Requested unknown parameter 'contract_bal.contract_id' for row 0, column 0. For more information about this error, please see http://datatables.net/tn/4**
Just in case you want to see how my 'columns' look from client side
Can you show me the corrected PHP code? In the
ifstatement above you are only selecting thestartcolumn and returning that. So yes, if you tried to tell the DataTable to display anything else, then it would error with the message you are seeing.Allan
@allan Thank you. I am posting my code below. What I am trying to do is , populate one of the columns in datatable with Raw sql query
('SELECT start as starty from contract_bal')and the rest of columns to be filled by Editor instanceSo, is there a way to populate one datatble with both raw sql and editor instance at same time? or in other words can we populate editor with rawsql?
Right - but what the code is doing above is just returning
startyin a data array. It can’t merge the data from the two different parts of theifstatement! Only one half of it runs after all.You can certainly populate with raw SQL, but you can’t do both raw SQL and the Editor instance at the same time.
I don’t actually understand what the goal is here. Why not just include
Field::inst( 'contract_bal.start' ),in your field list?Allan
@allan Thank you for the clarification. Much appreciated!
1) This was for the test purpose , just to try if we can populate with both RAW SQL and EDITOR instance. My final goal was to perform some calculations with RAW SQL and then pass them to Editor instance which I can read from client side.
a) However, is there a way to include SQL inside field instance inside here
Field::inst( 'contract_bal.start' )**
**b) or is it possible to use php code inside the field instance like we used to do in Datatbles using ssp class to perform some operations , for example , in the code below i am reading 'testing' column and perform some operations using php ****
2) I successfully managed to populate my DataTable just with RAWSQL . However, the problem I face here is that when turning the
Serverside:'true'the searching doesn't work and only works when serverside is set to false .Below is my server-side script I used
You can use an SQL function in the Editor
Fieldinformation .e.g.:See the documentation for that here.
Allan
@allan Thank you very much. You are just amazing!!!
Hi @allan
Is there any reason, why this code works fine but the next one doesnt work at all and return null.
As you can see from the code (which doesnt work) I add one more column value after / (division)
/(contract_bal.opt_one + contract_bal.opt_two + contract_bal.opt_five + contract_bal.opt_three )when this is restricted to three values it works , anything more than that it returns nullThis works fine:
This doesnt work :
I don't know why that would happen off the top of my head I'm afraid.
Just before the
->process( $_POST )line, add->debug(true)and then reload the page. The JSON response will now include the SQL that has been generated by the libraries. What does that look like?Allan
@allan
As mentioned in this entry, I want to ask If there is really now way to use RAW SQL queries but continuing using the Editor instance?
for my case I must populate the search pattern and build the sql query as like here:
@allan
This code inside the Editor field works fine
Here is the relevant SQL code from debug
For this code inside the Editor field, it doesn't work
Here is the relevant SQL code from debug
May be this is creating problem but I am not sure (v like structure ):
(contract_bal.monthly_input))** \/ **(contract_bal.opt_one + contract
@tefdat - Could you post your question in a new thread, so we can track a single issue at a time please?
@Khalid Teli I suspect that it is the
/dividing operation that is causing the issue - that is the one thing that stands out as different to me. And it isn't giving you an SQL error? If you replace/with*obviously the result would be nonsense, but does it then work?Another option here, instead of doing the calculation server-side, use a client-side renderer to do it. You've got all the data from the other fields - it is just a case of writing the calculation in Javascript rather than SQ I think.
Allan
@allan
Thank you.
No, it is not giving me any SQL errors.
I tried to replace it with
*and the problem is same .This works fine:
But this doesn't :
Only after adding those extra columns seems causing problem .
On the other hand , I have used the Client-side rendering and it works perfectly fine. The only issue is I am expecting to use large amount of data in future , that is why I was using server side .
You can still use client-side rendering with server-side processing. Although it wouldn't allow for sort and filtering...
If you run the query directly in phpMyAdmin or whatever database client you prefer, does it work there? I honestly don't know why that wouldn't be working based solely on the length of the string. Perhaps try removing parameters until you find out the length it will accept?
Allan
@allan
Thank you. I ran the query on phpMyAdmnin and works fine .
I have decided to render it on client-side and as I mentioned earlier it works fine.
I will come back to it later and if I find what the problem is, I will post it here.
@allan
just for the information, I have tried different combinations, anything greater 11 seems not to be working and anything less or equal to 11 works fine .
For emample, this works fine :
and this doesnt work:
That is very very odd! I'll try to reproduce it locally and will post back if I find anything. Good to hear you've got it working client-side in the meantime.
Allan