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”
}