How do I create a content type from SQL query?

Question

<?php

function customtable_permission() {
    return array(
        'show people' => array(
            'title' => t('List of people'),
            'description' => t('The table'),
        ),
    );

}

function customtable_menu() {

    $items = array();

    $items['people'] = array(
        'type' => MENU_NORMAL_ITEM,
        'title' => t('Title'),
        'description' => 'This page should show a table from a remote DB',
        'page callback' => 'customtable_db_data',
        'access arguments' => array('show people'),

    );

    return $items;
}

function customtable_db_data() {

    db_set_active('remote_database');

    $results = db_query("SELECT * FROM {people}");

    $header = array(t('Id'), t('Name'), t('Department'), t('Division'), t('Insurance'));

    $rows = array();

    foreach($results AS $result) {

        $rows[] = array(
            $result->id,
            $result->name,
            $result->department,
            $result->division,
            $result->insurance,
        );

    }
    db_set_active('default');

    return theme('table', array('header'=> $header, 'rows' => $rows));
}

?>

This all works fine and I can go to site.com/people and see the all the entries from the database printed nicely in a table

But I want text boxes where I can filter each column. Users can search by name or a specific insurance or department. I think it is possible programmatically, but I'd like to know if there is a more "drupal" approach. Content types have the ability to filter its fields. Am I to create a content type based on my query? I don't exactly know. Any assist is appreciated.

Answer 1

I think the best way to do this is migrate the query result to a drupal content type, to do this you need to use the migrate api.

-Install and enabled migrate and migrate_ui drupal modules. -Create any content type you want with your fields.Using the drupal interface. -Create a custom module, using migrate api. For example:

/sites/all/modules/custom/migrate_customtable/migrate_customtable.migrate.inc

function migrate_customtable_migrate_api() {

    $api = array(
        'api' => 2,
        'groups' => array(
            'custom_table' => array(
                'title' => t('Custom Table'),
            ),
        ),
        'migrations' => array(
            'Projects' => array(
                'class_name' => 'CustomTableMigration',
                'group_name' => 'custom_table',
                'event_log' => ''
            ),

        ),
    );

    return $api;


}

Then, create a class called: CustomTableMigration.inc that will contains the migration:

<?php

/**
 * Created by PhpStorm.
 * User: ldcontreras
 * Date: 25/07/18
 * Time: 10:13
 */
class CustomTableMigration extends Migration {


    public function __construct($arguments) {
        parent::__construct($arguments);
        $query = Database::getConnection('default', 'migrate_custom_table')//this must be define in your settins.php file
        ->select('people')
            ->fields('productos_new', array(
                    'id',
                    'name',
                    'department',
                    'division',
                    'insurance',
                )
            );


        $this->source = new MigrateSourceSQL($query, array(), NULL, array(map_joinable => FALSE));
        $this->destination = new MigrateDestinationNode('content_type_machine_name'); //the content type created

        $this->map = new MigrateSQLMap($this->machineName,
            array(
                'id' => array(
                    'type' => 'int',
                    'unsigned' => TRUE,
                    'not null' => TRUE,
                    'description' => t('Source ID'),
                )
            ),
            MigrateDestinationNode::getKeySchema()
        );

        $this->addFieldMapping('title', 'name');
        $this->addFieldMapping('field_department_content_type', 'department');
        $this->addFieldMapping('field_division_content_type', 'division');
        $this->addFieldMapping('field_insurance_content_type', 'insurance');


        $this->addUnmigratedDestinations(array(
            'body:format',
            'comment',
            'is_new',
            'log',
            'promote',
            'revision',
            'revision_uid',
            'tnid',
            'totalcount',
            'daycount',
            'timestamp',
            'path',
            'translate',
            'sticky',
            'uid',
        ));

    }


}

Finally,enable your custom module and run the migration using drush.