Getting null values in JOIN query and JSON

davisoski

Hi.

I'm using server_processing to do a JOIN query. Everything goes ok (show datatable and values), except in some columns where I get null values.

I attach a simple netbeans project and ajax database.

https://dl.dropbox.com/u/66063007/join.rar



Any idea?.

Thanks

allan

Set sDefaultContent to an empty string if you don't want a null value to appear in the table.

Allan

davisoski

Hi.


Thanks for your comment. I'm not getting null values in my table, I get an empty cell.

I added the code in: http://datatables.net/ref#sDefaultContent


like this:
[code]
//Table using JOIN
oTable4= $('#example4').dataTable( {
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "../../server_side/scripts/server_processing_join.php" ,
"aoColumnDefs": [
{
"mData": null,
"sDefaultContent": "Edit",
"aTargets": [ -1 ]
}
]

} );
[/code]

and this is the JSON I get using datatable debugger: (http://debug.datatables.net/amiyun)
[code]

{
"sEcho": "9",
"iTotalRecords": "2",
"iTotalDisplayRecords": "2",
"aaData": [
["2012-07-01 23:11:12", "2012-07-22 00:00:00", null, null, "Internet Explorer 5.5", "Este es el asunto del documento", "este es el detalle del asunto en si", "Area de destino", "1"],
["2012-09-24 15:19:43", "2012-10-27 13:13:34", null, null, null, null, "Asunto con detalle Asunto con detalle ", null, "2"]
]
}
[/code]

Edit only appears in the last column ("1" and "2").

In my phpmyadmin I get right values.

Maybe there is an intermediate step where this fails, but I can't image how to ...

Thanks.

allan

Sorry - I'm really not understanding the problem.

You've assigned null data to the last column, and it should be printing 'Edit' there, which it appears to be doing.

So what's the issue?

Allan

davisoski

Hi.

Sorry, Maybe I have not explained well.

I'm getting empty cells in datatable. Empty cells refers to "null" values in JSON.

Take a look at the picture:
https://dl.dropbox.com/u/66063007/datatable-join-mysql.png


As you can see there are values in my sql query, but in some place they are lost and are converted to null.

JSON output, in server interaction appears the null values...

http://debug.datatables.net/amiyun


Thanks for your interest.

Greetings

allan

Okay - so that sounds like an issue with the server-side processing script, rather than DataTables itself then. I'd suggest that you echo out the SQL that is being generated and run on the server, and then entering that into an SQL editor such as phpMyAdmin, running it and seeing what you get.

Allan

davisoski

Hi,

Finally I have found where the error is:

I have debugged my sql query using:

http://datatables.net/forums/discussion/675/x&page=1#Item_5

and everything goes well.

I'm spanish and I have characters like Á, É, í, ó, ü, ..... in my mysql database. These chars are not showed at all, so I'm getting null values in JSON

If I make a change like this: Á --> A, everything goes ok.


Looking at the forum I have found this:

[code]
"oLanguage": {
"sUrl": "../../media/language/es_ES.txt"
}
[/code]
Now I need to find how to show/convert this chars so datatables feel good with them.

Thanks

allan

Is everything in UTF8? The HTML, the Javascript includes and the database?

davisoski

Hi.

I think so. It's a little project made using Netbeans 7.2, using econding: UTF-8 in properties\sources.

Indeed, before using netbeans I had problems for not using UTF-8 files.


In database I use: utf8_spanish_ci


thanks

allan

Try adding this to your script:

[code]
//To send utf8 data to MySql - needed if you need to inject utf8 data for search
mysql_query("SET character_set_client=utf8", $gaSql['link']);
mysql_query("SET character_set_connection=utf8", $gaSql['link']);

//To read utf8 data from MySql - needed if your result set contains utf8 data
mysql_query("SET character_set_results=utf8", $gaSql['link']);
[/code]

Allan

davisoski

Great job.

Now works perfect.

Thanks a lot.

Best regards