Hi there,

I have some unix Epoch timestamps in a database column that I am trying to pull out. Typically I would use PHP's Date() function on it and turn it into a regular date string. Is there a way to do that in ARI Data Tables?

When I try to do it using SQL (see the sample query below), ARI Data Tables either runs out of memory (I have something like 750MB allocated to PHP so there should be no reason for this, also it should not be loading the entire table into memory), or just gives me a blank screen. The query when run in MySQL works fine and returns the data as expected. It's a big table (about 750k rows) but that's why I am using the server-side data option.

Here's an example of the SQL (This is on a MySQL 5.5 server by the way):

select id, DATE_FORMAT(FROM_UNIXTIME(timestamp), '%m-%d-%Y %h:%i %p') as date, type, text from table

If I keep the SQL simple, like: select * from table - it works fine. However the date shows up as a number which isn't what I need and I don't see a way to add that (I did notice in another post you seem to have extra undocumented features in the format field so I was wondering if you had some other things like that).

I could fix this if I could enter PHP code in the "Format" box, is that possible?

Another thing I don't see in the SQL query data source is how to specify a default sort. If I try putting in an ORDER BY clause in the SQL, it shows me that blank screen again. I saw in another thread you mentioned there was a way to do it but that was for a CSV data import.

And one last question: Is there a way to add a "From Date - To Date" search field to the form that is used in the query? I don't know what the "Search" box does, or if it just searches all fields marked as "Searchable for a string match, but I don't see a way to add additional search fields.

Hello,

It is possible to use "date_format" formatter in "Columns settings -> Format" value. For this specify column name in "Columns settings -> ID" parameter and use the following code in "Columns settings -> Format" parameter:



The extension searches by fields which are marked as searchable.

It is possible to use request variables in "WHERE" clause of "SELECT" SQL query and it is possible to create a search HTML form outside the table and use values from the form in the query. It requires a little knowledge of HTML.

Regards,
ARI Soft

Thanks - is the date documented anywhere in the DataTables documentation? If not, could it be?

Also is there a separate doc area for just DataTables standalone that might be different than the one under SmartContent? If so I can't find it.

Regarding the search: Is there a way to have it use one of the fields to search with by default?

Regarding the SQL: Is there some reason I can't use regular SQL in the query? It seems like you might be parsing it or something because SQL queries that work fine in MySQL cause a white screen or out of memory error in DataTables. One of my other issues is that I have a column of numbers representing words that I need to replace, and I don't see a way to do that without some sort of PHP or special SQL function query. (e.g., replacing "1" with "General", "2" with "Special", etc., and having the words appear in the column instead of the numbers).

Thanks!

Just FYI turning on "Revert Data" (from "N" to "Y") causes it to whitescreen, regardless of what's in the query settings. I am guessing that it is pulling all the data from the database and manipulating it in memory in PHP or something because that's the only reason I can think of that it would be running out of memory.

With "Revert Data" off, my original date format SQL query actually works fine.

And... on a numeric timestamp value (epoch timestamp) gives me this:

03-04-2002 03&2002-03-04T15:27:13+00:002002Monday20023;27 pm

Upgrade the extension to v. 2.2.15 and use the following code in "Columns settings -> Format" parameter:



When "Revert data" parameter is enabled, the extension retrieves all data using the entered query and then revert it.

We will add description of formatters to documentation soon.

A blank page appears if a PHP error occurs. You can temporary set "System -> Global Configuration -> Server -> Error reporting" parameter in Joomla! settings to "Maximum" value to show an error in browsers.

Could you specify what query causes the problem?

Regards,
ARI Soft