Datatables - Json output - PostgreSQL - Returns null

Question

I use datatables. I use it in conjunction with a Postgresql server. I want to take the data from the Postgresql server. I use the script found on: http://datatables.net/development/server-side/php_postgres

The script works and creates the json file. The problem is that the values on the json file are null.

This is what it returns:

{"sEcho":1, "iTotalRecords":4, "iTotalDisplayRecords":4, "aaData":[[null,null,null], [null,null,null], [null,null,null], [null,null,null]]}

Also what i don't understand is the variable $sIndexColumn (Indexed column (used for fast and accurate table cardinality)). I have set its value to the first column of my table e.g. $sIndexColumn = "'Name'";. Is this the correct use ?

Thanks in advance.

mu is too short · Accepted Answer · 2011-05-02 17:20:56Z

1

The documentation says:

To use the code on your own server, simply change the $aColumns array to list the columns you wish to include from your database, set $sIndexColumn to a column which is indexed (for speed), $sTable to the table name, and finally fill in your database connection parameters to $gaSql.

Emphasis mine. So, $sIndexColumn should be a column name, not a quoted string. Try this:

$sIndexColumn = "Name";

Single quotes are used for strings in PostgreSQL (and most other flavors of SQL).

I'm guessing that you made the same quoting problem with your $aColumns, i.e. you did something like this:

$aColumns = array("'One'", "'Two'", "'Three'");

when you should have done something like this:

$aColumns = array("One", "Two", "Three");

You're getting three columns out but there's nothing in those columns and those column values come from here:

$row[] = $aRow[ $aColumns[$i] ];

So if $aColumns is wrong then you'll get the nulls that you're seeing.

answered May 2, 2011 at 17:20

mu is too short

436k71 gold badges863 silver badges822 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Athanasios Emmanouilidis Over a year ago

Hi. I didn't put the quotes by mistake. Postgresql does not understand table names with capitals, you need to quote them. So here is my array variable. $aColumns = array( '"Name"', '"Surname"', '"Telephone"' ); If i leave it like this it returns the results i told you. If i make it $aColumns = array("Name", "Surname", "Telephone"); then it searches for field "name" not for field "Name" and returns error.

mu is too short Over a year ago

The '"Name"' approach looks right as the double quotes will quote the column names but maybe the PHP is getting confused. Can you have a look at what's in $aRow in the while loop at the end of that PHP file?

mu is too short Over a year ago

"Sort of correct" is better than "completely wrong" :) What is in $aRow in the loop? Are the keys upper case, lower case, or quoted upper case? Can you rewrite that loop to use the column positions rather than their names? You only need the names in there for the special "version" formatting but you're not using a "version" column.

Athanasios Emmanouilidis Over a year ago

Your approach was also correct! I renamed all table columns to lowercase and used your suggestion and it worked ! But how can i use capital letters whithout renaming all my table columns ?

Athanasios Emmanouilidis Over a year ago

I'll ask this directly to the developer's forum. Thanks a lot ;-)

|

Collectives™ on Stack Overflow

Datatables - Json output - PostgreSQL - Returns null

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related