Server-Side PHP PostgreSQL and sOffset

Patricia

I am trying to implement your script and it its giving errors on the sOffset variable.

Snippet
[code]
$sQuery = "
SELECT ".str_replace(" , ", " ", implode(", ", $aColumns))."
FROM $sTable
$sWhere
$sOrder
$sLimit
$sOffset
";
[/code]
I am not sure where you are setting that variable. This is the string sent to the server.

http://192.168.1.100:8080/display/reports/places/test2.php?sEcho=1&iColumns=7&sColumns=&iDisplayStart=0&iDisplayLength=10&sSearch=&bRegex=false&sSearch_0=&bRegex_0=false&bSearchable_0=true&sSearch_1=&bRegex_1=false&bSearchable_1=true&sSearch_2=&bRegex_2=false&bSearchable_2=true&sSearch_3=&bRegex_3=false&bSearchable_3=true&sSearch_4=&bRegex_4=false&bSearchable_4=true&sSearch_5=&bRegex_5=false&bSearchable_5=true&sSearch_6=&bRegex_6=false&bSearchable_6=true&iSortingCols=1&iSortCol_0=0&sSortDir_0=asc&bSortable_0=true&bSortable_1=true&bSortable_2=true&bSortable_3=true&bSortable_4=true&bSortable_5=true&bSortable_6=true&_=1299938650518.

Your help would be much appreciated. I am absolutely floored at what you've done with DataTables. As a webdesigner for over 15 years tables are the hardest to work with when not using them just as layout.

allan

Hi Patricia,

I think that's a little bug in the script - sorry about that :-). There is no need for the $sOffset variable since the offset is included in the $sLimit variable. I've just updated the script here: http://datatables.net/development/server-side/php_postgres - thanks for flagging it up, and the kind words :-)

Regards,
Allan

Patricia

Thanks Allan,

That did work but I also had to take out

[code]
$rResultTotal = pg_query( $gaSql['link'], $sQuery ) or die(pg_last_error());
$iTotal = pg_num_rows($rResultTotal);
// pg_free_result( $iTotal );
[/code]

I think you mean to free the results of $rResultTotal not $iTotal.

It is currently running but I am getting a strange warning when using the search. As soon as I type the first character this warning shows up in the console. "The 'charCode' property of a keyup event should not be used. The value is meaningless." Data is being returned in the correct format.

[code]

http://192.168.1.100:8080/display/reports/places/test2.php?sEcho=3&iColumns=7&sColumns=&iDisplayStart=0&iDisplayLength=10&sSearch=s&bRegex=false&sSearch_0=&bRegex_0=false&bSearchable_0=false&sSearch_1=&bRegex_1=false&bSearchable_1=false&sSearch_2=&bRegex_2=false&bSearchable_2=false&sSearch_3=&bRegex_3=false&bSearchable_3=true&sSearch_4=&bRegex_4=false&bSearchable_4=false&sSearch_5=&bRegex_5=false&bSearchable_5=true&sSearch_6=&bRegex_6=false&bSearchable_6=false&iSortingCols=1&iSortCol_0=0&sSortDir_0=asc&bSortable_0=true&bSortable_1=true&bSortable_2=true&bSortable_3=true&bSortable_4=true&bSortable_5=true&bSortable_6=true&_=1300032465888

{"sEcho":3,"iTotalRecords":1151,"iTotalDisplayRecords":184,"aaData":[["1","2854","541","St Louis, St Louis City","1963",null,null],["2","2286","1346","St Louis, St Louis City","1939",null,null],["7","4122","269","Bonne Terre, St Francois Co.","1901",null,"1922"],["9","3599","788","Bonne Terre, St Francois Co.","1902",null,null],["10","636","3845","Bonne Terre, St Francois Co.","1905",null,null],["11","4064","1544","Bonne Terre, St Francois Co.","1907",null,null],["18","3192","722","Surry, Surry Co., VA","1737",null,null],["20","1613","2596","Southampton, Southampton Co., VA","1708",null,null],["22","1195","1129","Whitby, Yorkshire","1621",null,null],["25","140","981","Annapolis, Anne Arundel Co., MD","1990","Fairfax Co., VA","1996"]]}
[/code]

Any clues?

allan

Certainly did mean $rResultTotal thanks for spotting that one as well.

As for the charCode message, it's safe to ignore that: http://stackoverflow.com/questions/3568106/firebug-the-charcode-property-of-a-keyup-event-should-not-be-used-the-value#3568393

Allan

Patricia

Thanks for the pointer to charCode message!

I've been working with the script a bit more and stumbled on a problem when trying to use a SQL statement that uses table joins, especially where I'm selecting columns of the same name. It's possible it's my lack of skill with SQL statements but I think I'm probably not the only one who would end up using a JOIN in the select statement.

Adding the JOIN statement to the $sTable was easy enough but I needed to refer to the column names both by
what's returned in the PostgreSQL results and how it's used in the arrays and select statements. I added the array $sColumns to switch between them.

[code]
$sTable = "families LEFT OUTER JOIN individuals as h ON (families.husb_id = h.id)
LEFT OUTER JOIN individuals as w ON (families.wife_id = w.id)";

$sColumns = array ( 'h.sort_name as "h.sort_name"' => 'h.sort_name',
'w.sort_name as "w.sort_name"' => 'w.sort_name', 'marr_date' => 'marr_date',
'marr_plac' => 'marr_plac', 'div_date' => 'div_date', 'div_plac' => 'div_plac');

$aColumns = array ('h.sort_name as "h.sort_name"', 'w.sort_name as "w.sort_name"',
'marr_date', 'marr_plac', 'div_date', 'div_plac');

$sIndexColumn = "h.sort_name";

[/code]

The select statement, $sQuery = "SELECT ".implode (', ',$aColumns)." FROM $sTable $sWhere $sOrder $sLimit"; uses the correct column names, but fails when its using $aColumns in $sOrder and $sWhere statements. But by using $sColumns where needed, (selected lines from the script) below it works.

[code]
...
sOrder .= $sColumns[$aColumns[ intval( $_GET['iSortCol_'.$i] ) ]]."
...
$sWhere .= $sColumns[$aColumns[$i]]." ILIKE '%".pg_escape_string($_GET['sSearch'])."%' OR ";
...
$sWhere .= $sColumns[$aColumns[$i]]." ILIKE '%".pg_escape_string($_GET['sSearch_'.$i])."%' ";
...
[/code]

I also had to change where it was called in the output for $output['aaData'].

[code]
$row[] = $aRow[ $sColumns[$aColumns[$i]] ]
[/code]

This project is not live yet else I would love to post a working example. I don't know if there is a better way to do it but it works.

allan

> it works.

Most important thing! :-)

Thanks for posting your solution to the JOIN, I'm sure others will find it useful (as you note, you aren't alone in wanting to do a JOIN in the server-side script)! A translation between the two types sounds good to me, and it won't impact speed, so nice one!

Regards,
Allan

Patricia

Thanks Allan,

Although I was feeling a bit silly once I finally stumbled across sName. I tried implementing that feature but it still won't work with the table_name.column_name format. I would have expected that setting the sName would work but it didn't.

[code]
"aoColumns": [
/* husband */ { "sName": "h.sort_name" },
/* wife */ { "sName": "w.sort_name" },
....
[/code]

The server doesn't return column names as "h.sort_name" "w.sort_name" just "sort_name" "sort_name".

Gerardo

You can use

SELECT h.sort_name AS h_sort_name, w.sort_name AS w_sort_name .... etc

Patricia

Hi Gerardo,

AS h_sort_name didn't work as I originally tried it as SELECT h.sort_name AS husband, w.sort_name AS wife .... etc. The problem was where I went to filter, it was trying to go back to the database to look for a column named 'husband' but it didn't exist. Maybe I have Alzeiheimer's but I really thought Allan or someone posted a link to more information on using the sName. I just can't seem to find it.

allan

This is the documentation for sName : http://datatables.net/usage/columns#sName . There are a couple of examples which use it as well, but generally its a fairly under used parameter - perhaps your work will fix that :-)

I think the issue with using sName in the join is that it is trying to fill two roles - a column identifier for the DataTable which uses just a single table of columns, and the SQL which has multiple tables. I suppose one option might be to build up the SQL in a way which forces the output to have the . in it:

[code]
SELECT h.sort_name as 'h.sort_name'
[/code]
for example should work for both DataTables and the SQL server...

Allan

Patricia

sName is an interesting parameter but the more I think about it the less I want to use it. If I need to change the static code for the client side its probably going to be more than just moving a column. I'll probably need to change the ajax code as well. So it's not really saving me time but possible to add to confusion down the road when looking at the code again. Less places to make changes. I'm unaware if that parameter is used in any other function.

I can't find anything on how to force the database to return results with the . format other than using ‘h.sort_name as “h.sort_name”’. If you use ‘h.sort_name as “husband” it should work for the ORDER BY clause but will not work for the WHERE clause, WHERE seems to look for actual column names but SORT BY will use the alias.

On first pass I would have assumed that $sIndexColumn is used to order the results and in the first ORDER BY statement but it appears to be taking the value of the first column in $aColumns, using ‘h.sort_name as “h.sort_name”’ this causes a SQL Error.

Reviewing the translation array that I made before, $sColumns, I realized that the only place I need the full ‘h.sort_name as “h.sort_name” as a column name is in the SELECT clause. So using $sColumns = array ('h.sort_name as "h.sort_name"', 'w.sort_name as "w.sort_name"', …) and imploding that array instead of $aColumns I only have to change the one line of the script, leaving $aColumns alone everywhere else.

So now I have
[code]
$sColumns = array ('h.sort_name as "h.sort_name"', 'w.sort_name as "w.sort_name"', 'marr_date', 'marr_plac', 'div_date', 'div_plac', 'husb_id');
$aColumns = array ('h.sort_name', 'w.sort_name', 'marr_date', 'marr_plac', 'div_date', 'div_plac', 'husb_id');
…
$sQuery = "SELECT ".implode (', ',$sColumns)." FROM $sTable $sWhere $sOrder $sLimit";
[/code]
as the only changes to the original script.

In an example script I’d add a line that if $sColumns exists use it if not use $aColumns.

kevin2012

Hi Patricia,

Can you please post a complete example script that uses server-side processing but uses 2 tables to produce the output.

Thanks,
- Kevin.

Sanjay

[quote]
kevin2012 October 2011
Posts: 3
Hi Patricia,

Can you please post a complete example script that uses server-side processing but uses 2 tables to produce the output.

Thanks,
- Kevin.
[/quote]

Just wondering if this might have happened. Thank you