SQL queries and SSP limits

east1999

Hi,

I'm implementing server-side processing and it is working very well.

One of the things I haven't yet been able to migrate from the client-side version of my table is a function that built graphs or charts from my data. In client-side, the charts would come from the 5000+ rows, and update upon search. In the server-side version however, only 50 rows are served at each turn, rendering the charts useless as an overview dashboard.

Is there any way I can adapt my idea to SSP? Here's my two possibilities right now:

• Adding an extra result to ssp.class.php, like recordFiltered, that extracts data from the full table and gets served for every search (basically just two new arrays — type + number of and year + number of). Problem: could still be too heavy for the server to handle.
• Exporting my data as a json, cut it down to the fields I need, and then use $.get in a different part of the page. Problem: would need to keep the graphs in sync with the table somehow.

Any ideas? Thank you.

allan

If you need the full data for the table, then don't use server-side processing. Returning the full data set on every request would just introduce network latency (and hammer your server!).

Allan

east1999

Thank you. Yes, that's exactly what I'm afraid of.

What I've tried to do is invoke a second Datatable, using a second version of the example PHP file that only returns two specific columns, not server-side, which gives me the full 5000+ rows in a bearable 500kb json. I can hide and destroy the resulting table but use the data for the charts.

Now, how can I link both Datatables? If I use an "id" column as common reference, can I pass each rendered row in Table 1 to Table 2? This is important in order to sync filtered data from Table 1 to the charts.

allan

I see, so you only need those two columns to get the data to draw the chart?

I've be tempted to use server-side processing and also append the information you need for the chart to the JSON response. Then use draw to update the charts.

Even better would be to try and reduce the data set for the charts on the server-side, returning just the points that the chart needs to draw.

Allan

east1999

The problem is the server-side output only delivers 50 results at each turn. I thought it would be necessary to change ssp.class.php, but what if I simply change the PHP that connects to the SQL table to add a second SQL query and pass it to the encoded JSON?

The only drawback here is that the additional data is loaded during initialisation, which would be still be a bit costly. In client-side I can load this second batch only when the user chooses to see the visualization.

east1999

Here's an extra consideration. I see this is a very simple SQL query so I'm trying to add it to ssp.class.php. Here's how:

        $yearcount = self::sql_exec( $db, $bindings,
            "SELECT `Publication Year`, COUNT(*) AS `Count`
            FROM `$table`
            GROUP BY `Publication Year`"
        );

This should do something like 2006: 175, etc. This should come out as an array and be relative to what is being searched. How can I add it to the return array?

"yearcount" => self::data_output( $yearcount ),

is not working.

east1999

Ok, this worked for me:

$yearcount = self::sql_exec( $db, $bindings,
    "SELECT `Publication Year`, COUNT(*) AS `Count`
    FROM `$table`
    $where
    GROUP BY `Publication Year`
    "
);
$yc = json_encode($yearcount);

And

        "years"           => json_decode($yc),
        "data"            => self::data_output( $columns, $data )

It seems like this allows for adding extra SQL queries to the return array. For some reason I had to encode/decode because simply adding $yc was not working.