DataBase values & JSON

eladnova

Hi guys

I have a CMS AddOn which saves form data to a database table.

I'm relatively new to PHP/MySQL etc but the format is (i think) called JSON in a database field called "data".

[code]
{
"firstname":"Mike",
"surname":"Mitchell",
"telephone":"0865-000",
"company":"Company Stuff Ltd",
"website":"www.company.not"
}
[/code]

I was kinda hoping each field (firstname, surname) would be a separate table column which I could use in DataTable columsn but this is what I've been given.

Would you have any pointers on how I can use DataTables to connect with my database (my-database-999) and then that table (my-form-results) and then use the information within my field called "data" ?

Thanks

eladnova

Sorry - re. above code example it seems some CDATA got included. Just ignore that part.

allan

Have a read through this blog post, which I think covers what you are looking for: http://datatables.net/blog/Extended_data_source_options_with_DataTables

Allan

eladnova

Im a bit lost to be honest. :-/
I've succesfully hooked DataTables up to pull in information from columns. That was quite a feat for me.

In the case of my array above, I am unsure of how to pull informaion from my column called "data" when it contains multiple pieces of info.

Simply specifying firstname, surname and telephone as below doesn't work as these "columns" are nested in a database column called "data"

[code]

$(document).ready(function() {
$('#example').dataTable( {
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "/assets/scripts/DataTables/examples/server_side/scripts/objects.php",
"aoColumns": [
{ "mData": "firstname" },
{ "mData": "surname" },
{ "mData": "telephone }
]
} );
} );

[/code]

tangerine

Are you saying your database table has only one field (called "data") and that each row in that field has firstname surname telephone?
Because if so, I would advise spending more time with database design before trying to use Datatables.
Beginning with a bad design will give you more and more problems later.

allan

> Simply specifying firstname, surname and telephone as below doesn't work as these "columns" are nested in a database column called "data"

So does the data structure for each row look something like this:

[code]
{
"data": {
"firstname":"Mike",
"surname":"Mitchell",
"telephone":"0865-000",
"company":"Company Stuff Ltd",
"website":"www.company.not"
}
}
[/code]

If so, you could use DataTables ability to read nested objects using Javascript dotted object notation:

[code]

$(document).ready(function() {
$('#example').dataTable( {
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "/assets/scripts/DataTables/examples/server_side/scripts/objects.php",
"aoColumns": [
{ "mData": "data.firstname" },
{ "mData": "data.surname" },
{ "mData": "data.telephone" }
]
} );
} );

[/code]

If that isn't the case, could you post an example of the full JSON returned by the server front he server-side processing Ajax request?

Thanks,
Allan

eladnova

[quote]Are you saying your database table has only one field (called "data") and that each row in that field has firstname surname telephone?
Because if so, I would advise spending more time with database design before trying to use Datatables.
Beginning with a bad design will give you more and more problems later.[/quote]

Hi Tangerine.
I agree with you but as I mentioned, the 3rd party form we are using is determining the database design.

It throws all the form results it collects into a single column called "data".
I have other columns called id, ip and time which I can pull in DataTable but the issue is nested information within a row in the "data" column.

An example of a Data column content is as follows.

[code]
{
"firstname":"Christian",
"surname":"Mitchell",
"telephone":"0854-000-000",
"email":"NA",
"company":"My Company Ltd",
"website":"www.website.not",
"sector":"Manufacture",
"town":"Edinburgh",
"reg-post-code":"NA",
"company-number":"NA",
"number-of-staff":"15"
}
[/code]

eladnova

Hi Allan

[code]
{ "mData": "data.firstname" },
{ "mData": "data.surname" },
{ "mData": "data.telephone" }
[/code]

I tried the dot notation you suggested. No joy but I did notice something very interesting.

When I load up the following page in a browser,
/server_side/scripts/objects.php
I can see that my data table isn't being included. Is that significant. Not sure of this is the cause or just the result.

tangerine

[quote]I agree with you but as I mentioned, the 3rd party form we are using is determining the database design. [/quote]
Okay, eladnova, fair enough. I share your pain....

allan

We took this offline to resolve. The issue was that the data object being returned was a JSON formatted string, not a JSON object. Decoding the string and outputting as good old JSON resolves the issue :-)

Allan