Tag Archives: Drupal

Result Sets Example of Select Query in Drupal 7

I want to see how the result sets really look like for select query in Drupal 7. This link as below just explains without example data of output.

https://www.drupal.org/node/1251174

I use themename_preprocess_node() to define variables and display it in node.tpl.php. The code is as below:

The table is as below:

id first_name last_name email

10 M3 J0 info10@aoyee.ca

9 M3 J9 info9@aoyee.ca

8 M3 J8 info8@aoyee.ca

7 M3 J info2@aoyee.ca

6 M3 J1 info12@aoyee.ca

5 M3 J7 info5@aoyee.ca

In the theme folder, on the template.php file, add this function:

function aoyee_in_preprocess_node(&$variables) {

$query = db_select(‘info_management’, ‘n’)
->fields(‘n’, array(‘id’))
->execute();
$variables[‘id_array’] = $query->fetchAssoc();

}

Then in the node.tpl.php file, $id_array is available.

var_dump($id_array);

The output is as below:

array(1) {
[“id”]=>
string(1) “5”
}

1, The other ways to retrieve the records. The method/output pairs are as below:

1.1) $variables[‘id_array’] = $query->fetchAllAssoc(‘id’);

array(6) {
[5]=>
object(stdClass)#265 (1) {
[“id”]=>
string(1) “5”
}
[6]=>
object(stdClass)#299 (1) {
[“id”]=>
string(1) “6”
}
[7]=>
object(stdClass)#267 (1) {
[“id”]=>
string(1) “7”
}
[8]=>
object(stdClass)#297 (1) {
[“id”]=>
string(1) “8”
}
[9]=>
object(stdClass)#300 (1) {
[“id”]=>
string(1) “9”
}
[10]=>
object(stdClass)#304 (1) {
[“id”]=>
string(2) “10”
}
}

1.2) $variables[‘id_array’] = $query->fetch()

object(stdClass)#265 (1) {
[“id”]=>
string(1) “5”
}

1.3) $variables[‘id_array’] = $query->fetchObject()

object(stdClass)#265 (1) {
[“id”]=>
string(1) “5”
}

1.4) $variables[‘id_array’] = $query->fetchField(0);

string(1) “5”

1.5) $variables[‘id_array’] = $query->fetchField(1);

NULL

1.6) $variables[‘id_array’] = $query->rowCount();

int(6)

1.7) $variables[‘id_array’] = $query->fetchAll();

array(6) {
[0]=>
object(stdClass)#265 (1) {
[“id”]=>
string(1) “5”
}
[1]=>
object(stdClass)#302 (1) {
[“id”]=>
string(1) “6”
}
[2]=>
object(stdClass)#299 (1) {
[“id”]=>
string(1) “7”
}
[3]=>
object(stdClass)#267 (1) {
[“id”]=>
string(1) “8”
}
[4]=>
object(stdClass)#297 (1) {
[“id”]=>
string(1) “9”
}
[5]=>
object(stdClass)#300 (1) {
[“id”]=>
string(2) “10”
}
}

1.8) $variables[‘id_array’] = $query->fetchAllAssoc(‘first_name’);

array(1) {
[“”]=>
object(stdClass)#267 (1) {
[“id”]=>
string(2) “10”
}
}

1.9) $variables[‘id_array’] = $query->fetchAllAssoc(’email’);

array(1) {
[“”]=>
object(stdClass)#299 (1) {
[“id”]=>
string(2) “10”
}
}

2, The query is as below:

$query = db_select(‘info_management’, ‘n’)
->fields(‘n’, array(‘id’, ’email’))
->execute();

The method/output pairs are as below:

2.1) $variables[‘id_array’] = $query->fetchAllAssoc(’email’);

array(6) {
[“info5@aoyee.ca”]=>
object(stdClass)#265 (2) {
[“id”]=>
string(1) “5”
[“email”]=>
string(14) “info5@aoyee.ca”
}
[“info12@aoyee.ca”]=>
object(stdClass)#299 (2) {
[“id”]=>
string(1) “6”
[“email”]=>
string(15) “info12@aoyee.ca”
}
[“info2@aoyee.ca”]=>
object(stdClass)#267 (2) {
[“id”]=>
string(1) “7”
[“email”]=>
string(14) “info2@aoyee.ca”
}
[“info8@aoyee.ca”]=>
object(stdClass)#297 (2) {
[“id”]=>
string(1) “8”
[“email”]=>
string(14) “info8@aoyee.ca”
}
[“info9@aoyee.ca”]=>
object(stdClass)#300 (2) {
[“id”]=>
string(1) “9”
[“email”]=>
string(14) “info9@aoyee.ca”
}
[“info10@aoyee.ca”]=>
object(stdClass)#304 (2) {
[“id”]=>
string(2) “10”
[“email”]=>
string(15) “info10@aoyee.ca”
}
}

2.2) $variables[‘id_array’] = $query->fetchField(2);

NULL

2.3) $variables[‘id_array’] = $query->fetchField(2);

string(14) “info5@aoyee.ca”

2.4) $variables[‘id_array’] = $query->fetchAllKeyed();

array(6) {
[5]=>
string(14) “info5@aoyee.ca”
[6]=>
string(15) “info12@aoyee.ca”
[7]=>
string(14) “info2@aoyee.ca”
[8]=>
string(14) “info8@aoyee.ca”
[9]=>
string(14) “info9@aoyee.ca”
[10]=>
string(15) “info10@aoyee.ca”
}

3, The query is as below:
$query = db_select(‘info_management’, ‘n’)
->fields(‘n’, array(‘id’, ‘first_name’, ’email’))
->execute();

The method/output pairs are as below:

3.1) $variables[‘id_array’] = $query-> fetchAllKeyed(0,2);
array(6) {
[5]=>
string(14) “info5@aoyee.ca”
[6]=>
string(15) “info12@aoyee.ca”
[7]=>
string(14) “info2@aoyee.ca”
[8]=>
string(14) “info8@aoyee.ca”
[9]=>
string(14) “info9@aoyee.ca”
[10]=>
string(15) “info10@aoyee.ca”
}

3.2) $variables[‘id_array’] = $query-> fetchAllKeyed(1,0);

array(1) {
[“M3”]=>
string(2) “10”
}

3.3) $variables[‘id_array’] = $query-> fetchAllKeyed(0,0);

array(6) {
[5]=>
string(1) “5”
[6]=>
string(1) “6”
[7]=>
string(1) “7”
[8]=>
string(1) “8”
[9]=>
string(1) “9”
[10]=>
string(2) “10”
}

3.4) $variables[‘id_array’] = $query-> fetchCol();

array(6) {
[0]=>
string(1) “5”
[1]=>
string(1) “6”
[2]=>
string(1) “7”
[3]=>
string(1) “8”
[4]=>
string(1) “9”
[5]=>
string(2) “10”
}

3.5) $variables[‘id_array’] = $query-> fetchCol($column_index);

array(6) {
[0]=>
string(1) “5”
[1]=>
string(1) “6”
[2]=>
string(1) “7”
[3]=>
string(1) “8”
[4]=>
string(1) “9”
[5]=>
string(2) “10”
}

3.6) $variables[‘id_array’] = $query-> fetchCol(2);

array(6) {
[0]=>
string(14) “info5@aoyee.ca”
[1]=>
string(15) “info12@aoyee.ca”
[2]=>
string(14) “info2@aoyee.ca”
[3]=>
string(14) “info8@aoyee.ca”
[4]=>
string(14) “info9@aoyee.ca”
[5]=>
string(15) “info10@aoyee.ca”
}

Drupal 7: Get Data from Database and Create a Table with Theme_table

The code is as below:

/**
* display the data.
*/
function display_data($form, &$form_state) {
//Use Database API to retrieve records.
$query = db_select(‘info_manager’, ‘n’)
->fields(‘n’, array(‘id’, ‘first_name’, ‘last_name’, ’email’))
->orderBy(‘id’, ‘DESC’); //Most recent first.
$result = $query->execute();

//Iterate over the resultset and format them.
$header = array(‘ID’, ‘First Name’, ‘Last Name’, ‘Email’);
$rows = array();
foreach ($result as $row){
$rows[] = array($row->id,
$row->fist_name,
$row->last_name,
$row->email,
);
}

if (empty($rows)) { //No content in the last week.
$page_array[‘info_manager_arguments’] = array(
//Title serves as page subtitle
‘#title’ => t(‘All posts from the last week’),
‘#markup’ => t(‘No posts available.’),
);
return $page_array;
}
else {
$page_array[‘info_manager_arguments’] = array(
‘#header’ => $header,
‘#rows’ => $rows,
//Theme function includes theme hook suggestion.
‘#theme’ => ‘table__info_manager’,
);
return $page_array;
}
}

helpful links:

Default theme implementations
https://api.drupal.org/api/drupal/modules!system!theme.api.php/group/themeable/7

Drupal for Beginners: How to Create a Table Using theme_table()
http://zgadzaj.com/drupal-for-beginners-how-to-create-a-table-using-themetable

Drupal 7: .install file not working properly (Fixed)

Below is from drupal.org

The hook will only be called the first time a module is enabled or after it is re-enabled after being uninstalled.

Generally, the “uninstalled” can be ignored. On the module page, there is a tag named “uninstall” on the top-right corner. Click to open, then the modules are listed there. check the space before the module you want to uninstall, then click “uninstall” button to uninstall.