Daniel Morris Daniel Morris - 1 month ago 26
Javascript Question

FooTables plugin (Similiar to DataTables) - How to retrieve column and row data from a server/database

Note: Please also keep in mind I do not know JavaScript and I know only Fundamental PHP.

I've searched high, low, far, & wide. I have even tried to learn some JS to figure out a solution. But I have failed at all ends. This question, surprisingly does not exist on this site. So there is no duplicate question here. Also please do not confuse this question with other questions asking about loading large data sets (a large amount of rows) from a json file. Those questions exist and I already know how to do that.

With the DataTables Plugin this is easy. And there is documentation to show you how to do this exactly. Sadly, this does not exist on the fooTable website.

Here is the only page that FooTables has that is even close to what I need.
Break down of Question as it would apply to anyone:


  • The table on the page is using the FooTable Plugin

  • There are 10,000 rows of data in a database.

  • The website owner needs to connect the database to the table

  • The table has pagination. 10 rows per page.

  • Only 10 rows should load on page load. Not the entire database of records.

  • On page load the table pulls 10 rows from the database and only 10 rows making page speed much faster.



This is essentially what I need.

ANY help on this matter is appreciated I have been at it for 2 weeks with no positive results. Please also keep in mind I do not know JavaScript and I know only Fundamental PHP. Thanks in advance!

EDIT: Is this the correct way to do rows.php?

<?php
// rows
$rowData = array(
'id' => $id,
'firstName' => $firstName,
'something' => $something,
'jobTitle' => $jobTitle,
'started' => $started,
'dob' => $dob,
'status' => $status
);

header('Content-Type: application/json');
echo json_encode($rowData, JSON_UNESCAPED_SLASHES);

Answer

The "loading from a JSON file" actually is relevant.

From the page you link, they provide this code:

jQuery(function($){
    $('.table').footable({
        "columns": $.get('columns.json'),
        "rows": $.get('rows.json')
    });
});

This is doing an AJAX request to the server to fetch the data from a JSON file, but it doesn't have to be from a file as long as the server responds with JSON data.

So what you would do instead is $.get('/columns.php') to get the column data, and $.get('/rows.php') to get the row data. More on $.get here.

Then, inside columns.php, you have to make your database calls, pull out your data, structure it the same as this and and this and then json_encode it.

Now I know you said you don't know any JS and only a little PHP, but you're going to have to learn some, because I'm not writing all that code for you ;)


<?php // columns.php
$data = array (
    array (
        'name' => 'id',
        'title' => 'ID',
        'breakpoints' => 'xs sm',
        'type' => 'number',
        'style' =>
            array (
                'width' => 80,
                'maxWidth' => 80,
            ),
    ),
    array (
        'name' => 'firstName',
        'title' => 'First Name',
    ),
    array (
        'name' => 'lastName',
        'title' => 'Last Name',
    ),
    array (
        'name' => 'something',
        'title' => 'Never seen but always around',
        'visible' => false,
        'filterable' => false,
    ),
    array (
        'name' => 'jobTitle',
        'title' => 'Job Title',
        'breakpoints' => 'xs sm',
        'style' =>
            array (
                'maxWidth' => 200,
                'overflow' => 'hidden',
                'textOverflow' => 'ellipsis',
                'wordBreak' => 'keep-all',
                'whiteSpace' => 'nowrap',
            ),
    ),
    array (
        'name' => 'started',
        'title' => 'Started On',
        'type' => 'date',
        'breakpoints' => 'xs sm md',
        'formatString' => 'MMM YYYY',
    ),
    array (
        'name' => 'dob',
        'title' => 'Date of Birth',
        'type' => 'date',
        'breakpoints' => 'xs sm md',
        'formatString' => 'DD MMM YYYY',
    ),
    array (
        'name' => 'status',
        'title' => 'Status',
    ),
);

header('Content-Type: application/json');
echo json_encode($data, JSON_UNESCAPED_SLASHES);

This is exactly the same as fooTable's columns.json example, just in PHP.

Now that it's in PHP, you can pull your data from the database instead of hardcoding. I don't know what your database looks like, so I couldn't write that code for you even if I wanted to.

rows.php will look pretty much the same, just a different array format.

Comments