2

I am trying to create nodes from information extracted from a SQL query by using the following:

db_set_active('sage');
$query = db_query('SELECT CLIENT_ID, MASTER_CLIENT_ID, PARTICIPANT_ID FROM sage.CLIENT');
$result = $query->execute();
foreach($result as $record) {
  $node = new stdClass();
  $node->title = (string)$record->participant_id;
  $node->client_id = (string)$record->client_id;
  $node->master_client_id = (string)$record->master_client_id;
}

However, from my echo statements, I can see that my code never even enters the loop.

I had thought that execute() returns me an object of the query to loop over, but I guess I was wrong.

Could someone please tell me how I can loop over the results of my query and get the fields?

  • Are you sure your SQL should return something? – Lester Peabody Jun 13 '12 at 19:48
  • @LesterPeabody, Ya it is, I just tried it again and it returns me what I expect. – chlong Jun 13 '12 at 19:51
  • Excellent. Good luck with all your future endeavors. – Lester Peabody Jun 13 '12 at 19:56
  • @LesterPeabody, Do you see an error with my query or code? – chlong Jun 13 '12 at 20:00
  • Not necessarily a syntax error. It looks good to me. – Lester Peabody Jun 13 '12 at 20:03
1
$result = $query->execute()->fetchAll();

should do the trick.

There's a lot more info and several more examples in http://api.drupal.org/api/examples/dbtng_example!dbtng_example.module/7

1

Also: db_query() does the execute for you. You only need $result = db_query('SELECT thingie FROM whatever')' and then loop through the results.

But you really want db_select().

$columns = array('CLIENT_ID', 'MASTER_CLIENT_ID', 'PARTICIPANT_ID')
$query = db_select('client', 'c');
$query->fields('c', $columns);
$results = $query->execute();
while($record = $results->fetchAssoc()) {
  $node = new stdClass();
  $node->type = 'your_node_type';
  node_object_prepare($node);
  foreach($columns as $column) {
    $node->$column = $record[$column];
  }
  node_save($node);
}

Each $record is an associative array, keyed by the column name, thanks to fetchAssoc(). So you'll have $record['CLIENT_ID'], etc. You'd then add them to the $node object however you want/need to.

Some helpful hints here: http://www.group42.ca/creating_and_updating_nodes_programmatically_in_drupal_7

  • Paul, I looked further into the problem. If I were to loop only with the following: $query = db_query('SELECT CLIENT_ID, MASTER_CLIENT_ID, PARTICIPANT_ID FROM sage.CLIENT'); and foreach($query as $record) I am unable to print out anything if I try to echo $record->$client_id. However, the number of times I loop is equal to the number of results returned by the query. Would you happen to know why? – chlong Jun 14 '12 at 17:35
  • Updated the answer. Each $record is an associative array, keyed by the column name, thanks to fetchAssoc(). So you'll have $record['CLIENT_ID'], etc. You'd then add them to the $node object however you want/need to. – paul-m Jun 14 '12 at 17:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.