External Database connection Display results in Forms table as sortable pagination

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.

Social Networking Sites

Group notifications

This group offers an RSS feed. Or subscribe to these personalized, sitewide feeds:

Hot content this week