External Database connection Display results in Forms table as sortable pagination

We encourage users to post events happening in the community to the community events group on https://www.drupal.org.
You are viewing a wiki page. You are welcome to join the group and then edit it. Be bold!

<?php
/**
* @file
* This module is for core db search and send selected items via email.
*
* In this module, a user (registered) can search the url list and result will be displayed as list.
* The submitted data will be stored in custom table email_sents with user data , and emails will be sent.
*/

/**
* Menu for Endpoint
*
* Implementation of hook_menu().
*/
function my_module_menu() {

$items['my-module'] = array(
  'title' => t('My Module'),
  'description' => t('Search form'),
  'page callback' => 'drupal_get_form',
  'page arguments' => array(my_module_page_form'),
'access arguments' => array('access content'), // permission can  be added
  'type' => MENU_NORMAL_ITEM,
);

$items['my-item-list'] = array(
  'title' => t('My list'),
  'description' => t('Display simple list in table'),
  'page callback' => 'my_module_display_simple',

'access arguments' => array('access content'), // permission can be added
  'type' => MENU_NORMAL_ITEM,
);





return $items;

}

/* Hook Theme */

function my_module_theme() {

return array(
'search_send' => array(
'arguments' => array('form' => NULL, 'form_state' => NULL),
'template' => 'themes/search_send',
),
'my_module_page_form' => array(
'render element' => 'form',

),

);
}

/**
* Implements hook_block_info().
*/
function my_module_block_info() {
$blocks['search_content'] = array(
'info' => t('Search Content'),
'cache' => DRUPAL_NO_CACHE,
'properties' => array('administrative' => TRUE),
);

return $blocks;
}

/**
* Implements hook_block_view().
*/
function my_module_block_view($delta = '') {
$block = '';
switch ($delta) {
case 'search_content':
$block['subject'] = t('Test search form');
$block['content'] = drupal_get_form('my_module_block_form');
break;

}

return $block;

}

function my_module_display_simple(){
global $user;
$rows = array();
$out = '';
// optional example to fetch data from any table with condition
$query = db_select('ANY_TABLE', 's')
->fields('s')

->condition('s.userID', $user->uid,'=');

$result = $query->execute();

while($record = $result->fetchAssoc()) {


     $createddate = date('Y-m-d h:s:i', $record['created']);
      $rows[] = array(
  'data' => array(

    $record['title'],


    $createddate,
    $record['description'],

  ),

);


}

// Prepare table header

$header = array(
array(
"data" => t('Title'),
// "field" => "title"
),
array(
"data" => t('Date'),
//"field" => "created"
),
array(
"data" => t('Description'),
// "field" => "description"
),

);
$output = theme_table(
array(
"header" => $header,
"rows" => $rows,
"attributes" => array(),
"sticky" => true, // Table header will be sticky
"caption" => "",
"colgroups" => array(),
"empty" => t("Table has no row!") // The message to be displayed if table is empty
)
);

return $output;
}

function my_module_block_form($form, &$form_state) {
$sdata = '';
// need to find out more suitable way to do this
if ($_SESSION['sdata'])
$sdata = $_SESSION['sdata'];
$form['sdata'] = array(
'#type' => 'textfield',
'#title' => 'Keyword',
'#size' => 30,
'#default_value' => $sdata,

);

$form['submit'] = array(
'#type' => 'submit',
'#value' => 'Search',

);

return $form;
}

function my_module_block_form_validate($form, &$form_state) {

}

function my_module_block_form_submit($form, &$form_state) {
$_SESSION['sdata'] = $form_state['values']['sdata'];
}

/**
* Connection with external database or here you can connect with api or json server , I will json next example
*/
function my_module_connection_external_db(){
$ex_database = array(
'database' => 'external_database',
'username' => 'shad',
// 'username' => 'root',
'password' => 'testing',
// 'password' => '',
'host' => '127.0.0.1',
'driver' => 'mysql',
);

Database::addConnectionInfo('my_module', 'default', $ex_database);
db_set_active('my_module');

}

/**
*Fetch from external database and extend query using table sort *pagination
*
*/

function my_module_external_data($sData){
$recArray = '';
// Check if there is sorting request
if(isset($_GET['sort']) && isset($_GET['order'])){
// Sort it Ascending or Descending?
if($_GET['sort'] == 'asc')
$sort = 'ASC';
else
$sort = 'DESC';

// Which column will be sorted
switch($_GET['order']){
   case 'Select':
   // $order = 'title';
    break;
  case 'Title':
    $order = 'title';
    break;
  case 'Description':
    $order = 'shortDescription';
    break;


}

}
else{
// Default sort
$sort = 'ASC';
$order = ' title';
}

// Call externel DB connection PDO
my_module_connection_external_db();
if (!empty($sData) && $sData != 'd'){
$query = db_select('External db Table name', 'c');
$query->fields('c');

$query->condition(db_or()->condition('c.shortDescription', '%'.$sData.'%','like')
                          ->condition('c.title', '%'.$sData.'%','like') );

$query->orderBy($order, $sort);


}
else {
$query = db_select('External db table name', 'c')
->fields('c')
->orderBy($order, $sort);
}

// extend table sort and pager
$query = $query->extend('TableSort')->extend('PagerDefault')->limit(50);

$result = $query->execute();

while($record = $result->fetchAssoc()) {
    $recArray[] = $record;
}





db_set_active(); // without the paramater means set back to the default for the site

// drupal_set_message(t('The queries have been made.'));
return $recArray;
}

function my_module_page_form($form, &$form_state) {

$sData = '';
$recDefault = array();
if (!empty($_SESSION['sdata']))
$sData = $_SESSION['sdata'] ;

$form['s_items'] = array(

'#tree' => TRUE,
);

if($sData)
$recDefault = my_module_external_data($sData);

unset($_SESSION['sdata']);
// Iterate through each database result
if ($recDefault == '' && $sData != ''){
// drupal_set_message(t('No result found'));
$form['no-result'] = array(
'#type' => 'item',
'#markup' => t('No result found'),
);

} else {
foreach ($recDefault as $item) {
$id = $item['tId'];

$form['s_items'][$id]['id'] = array(
   '#type' => 'checkboxes',

  '#options' => array($id => ''),  



);
 $sTitle = check_plain($item['title']);
 $form['s_items'][$id]['title'] = array(
   '#type' => 'item',

  '#markup' => $sTitle,


);
 $form['s_items'][$id]['titleh'] = array(
   '#type' => 'hidden',

  '#value' => $item['title'],


);


 $form['s_items'][$id]['Description'] = array(
   '#type' => 'item',

    '#markup' => check_plain($item['Description']),



);
   $form['s_items'][$id]['Descriptionh'] = array(
   '#type' => 'hidden',

    '#value' => check_plain($item['Description']),



);

} // end foreach loop
} // end else condition

// Now we add our submit button, for submitting the form results.
//
// The 'actions' wrapper used here isn't strictly necessary for tabledrag,
// but is included as a Form API recommended practice.
$form['actions'] = array('#type' => 'actions');
$form['submit'] = array(
'#type' => 'submit',
'#value' => t('Do More'),

);

$form['#submit'][] = 'my_module_page_form_submit';
return $form;
}

/**
* Theme callback for the my_module_simple_form form
*
*
* @return
* The rendered table form
*/
function theme_my_module_page_form($variables) {
$form = $variables['form'];

// Initialize the variable which will store our table rows
$rows = array();

// Iterate over each element in our $form['s_items'] array
foreach (element_children($form['s_items']) as $id) {
$description = drupal_render($form['s_items'][$id]['Description']);

   $rows[] = array(
  'data' => array(

    drupal_render($form['s_items'][$id]['id']),

    drupal_render($form['s_items'][$id]['title']),
    drupal_render($form['s_items'][$id]['Description']),


  ),

);

}

// Prepare table header
$header = array(
array("data" => t('Select'),
),
array(
"data" => t('Title'),
"field" => "title"
),
array(
"data" => t('Description'),
"field" => "Description"
),

);
// Output of table with the paging
$output = theme_table(
array(
"header" => $header,
"rows" => $rows,
"attributes" => array(),
"sticky" => true, // Table header will be sticky
"caption" => "",
"colgroups" => array(),
"empty" => t("Table has no row!") // The message to be displayed if table is empty
)
).theme("pager");

 $output .=  drupal_render($form['actions']);
 $output .= drupal_render($form['submit']);
 $output .= drupal_render_children($form);

return $output;
}

/**
* Submit callback for the my_module_simple_form form
*
*
*/
function my_module_page_form_submit($form, &$form_state) {
global $user;

// Because the form elements were keyed with the item ids from the database,
// we can simply iterate through the submitted values.

foreach ($form_state['values']['s_items'] as $id => $item) {
$sel_id = $item['id'][$id];
if (isset($sel_id) && $sel_id > 0){

  $title = $item['titleh'];
      $desc = $item['descriptionh'];




db_insert('local_table')
->fields(array(
  'Description' => $desc,
  'userID' => $user->uid,
  'title' => $title,
  'itemId' => $sel_id,
  'created' => time(),
  'changed' => 0,
))
->execute();

drupal_set_message(t('Succefully updated - ID !id  - Item - !item', array('!id' => $id, '!item' => $item['tld'][$id])));
}  // sel_id condition check end

} // loop end

}

Comments

What is this?

TravisCarden's picture

What exactly is the purpose of this page? It's not clear what it's for or what it has to do with Quickstart.