1

I have a website with guitar lessons that I am converting to drupal. I am new to drupal and have started getting familiar over the past few days. I created a content type for lessons and exercises, established categories via taxonomy, used Entity Reference to relate exercises and lessons (lessons can contain exercises or exercises can stand on their own) and created fields for exercises such as audio, video.

I now see that in the database there is no way to view lesson and exercise content as a whole. Everything is done via the node and field tables, and entity relations are through additional tables that I haven't discerned yet.

This database structure is making it hard for me to import all my lesson and exercise data, since in my previous site all fields associated with a lesson or exercise are in a corresponding lesson or exercise table.

I understand the drupal structure allows more power, and from reading this post about content types and database seems I would be crazy to expect to create a database table for my lessons and exercises. So then, how would I create SQL queries in drupal to perform the following task?

"Insert into the drupal structure a lesson content node, that is from a given category, that contains various fields I created in drupal, which have one-to-one relation to the fields in my source database table?"

Likewise, to get exercises squared away, how would I

"Insert into the drupal structure an exercise content node, that is from a given category, that is tied to a given lesson, that contains various fields, mapped one-to-one from source database to fields I created in drupal?"

I am really at a loss for how to compose the SQL. Any tips are vastly appreciated!

Regards

EDIT: here is the script I wrote, which seems to be populating everything as needed, but something is still missing...

EDIT2: I was missing some tables, so below is everything I did to import. Things seem to work but I have no idea if there is some other obscure setting I missed...

EDIT3: while the below worked, I ended up following the advice in the answers and used the API according to this post about adding nodes programmatically and this one which specifically talks about adding nodes with Entity Reference field. I then manually updated the node_counter table because I wasn't sure how to do that via the API.

$pnuke = db_query("SELECT * FROM nuke_guitarlessons_guitarlessons WHERE 1");

$cids = array(8,10,11,13,14); // ignore these lesson categories
$catmap=array(); // this maps old cat ids to the taxonomy term ids
$catmap[] = 7;$catmap[] = 0;$catmap[] = 1;$catmap[] = 9;$catmap[] = 6;$catmap[] = 2;$catmap[] = 8;$catmap[] = 0;$catmap[] = 0;$catmap[] = 0;$catmap[] = 0;$catmap[] = 10;

db_set_active();

$lastid=4; // this just ensures that node ids are incremented as I want
while ($n = $pnuke->fetchAssoc()) {
    if (!in_array($n['cid'], $cids)) {
    $datets = strtotime($n['date']);
    $datenow = new DateTime();

    $title = $n['title'];

    db_insert('node')
    ->fields(array('vid' => $lastid+1, 'type' => 'lesson', 'language' => 'und', 'title' => $title, 'uid' => 1, 'status' => 1, 'created' => $datets, 'changed' => $datets, 'comment' => 0, 'promote' => 1, 'sticky' => 0, 'tnid' => 0, 'translate' => 0))
    ->execute();

    db_insert('node_revision')
    ->fields(array('nid' => $lastid+1, 'vid' => $lastid+1, 'uid' => 1, 'title' => $title, 'log' => '', 'timestamp' => $datenow->getTimestamp(), 'status' => 1, 'comment' => 0, 'promote' => 1, 'sticky' => 0))
    ->execute();

    db_insert('field_data_body')
    ->fields(array('entity_type' => 'node', 'bundle' => 'lesson', 'deleted' => 0, 'entity_id' => $lastid+1, 'revision_id' => $lastid+1, 'language' => 'und', 'delta' => 0, 'body_value' => $n['introtext'], 'body_summary' => '', 'body_format' => 'full_html'))
    ->execute();

    db_insert('field_revision_body')
    ->fields(array('entity_type' => 'node', 'bundle' => 'lesson', 'deleted' => 0, 'entity_id' => $lastid+1, 'revision_id' => $lastid+1, 'language' => 'und', 'delta' => 0, 'body_value' => $n['introtext'], 'body_summary' => '', 'body_format' => 'full_html'))
     ->execute();

    db_insert('field_data_field_lessonintro')
    ->fields(array('entity_type' => 'node', 'bundle' => 'lesson', 'deleted' => 0, 'entity_id' => $lastid+1, 'revision_id' => $lastid+1, 'language' => 'und', 'delta' => 0, 'field_lessonintro_value' => $n['description'], 'field_lessonintro_format' => 'full_html'))
    ->execute();

    db_insert('field_revision_field_lessonintro')
    ->fields(array('entity_type' => 'node', 'bundle' => 'lesson', 'deleted' => 0, 'entity_id' => $lastid+1, 'revision_id' => $lastid+1, 'language' => 'und', 'delta' => 0, 'field_lessonintro_value' => $n['description'], 'field_lessonintro_format' => 'full_html'))
    ->execute();

    db_insert('node_counter')
    ->fields(array('nid' => $lastid+1, 'totalcount' => (int)$n['hits'], 'daycount' => 1, 'timestamp' => $datenow->getTimestamp()))
    ->execute();

    db_insert('taxonomy_index')
    ->fields(array('nid' => $lastid+1, 'tid' => $catmap[(int)($n['cid']-1)], 'sticky' => 0, 'created' => $datets))
    ->execute();

    db_insert('field_data_field_category')
    ->fields(array('entity_type' => 'node', 'bundle' => 'lesson', 'deleted' => 0, 'entity_id' => $lastid+1, 'revision_id' => $lastid+1, 'language' => 'und', 'delta' => 0, 'field_category_tid' => $catmap[(int)($n['cid']-1)]))
    ->execute();

    db_insert('field_revision_field_category')
    ->fields(array('entity_type' => 'node', 'bundle' => 'lesson', 'deleted' => 0, 'entity_id' => $lastid+1, 'revision_id' => $lastid+1, 'language' => 'und', 'delta' => 0, 'field_category_tid' => $catmap[(int)($n['cid']-1)]))
    ->execute();

 $lastid++;
  }
}
  • 3
    This is a ~large~ topic probably well beyond the scope of a single question. Do not seriously consider grinding the SQL directly. It's probably do'able; but you have to touch many tables beyond the mere content in order to keep drupal happy with node ids and whatnot. Start by searching for "drupal import." – ericx Sep 5 '15 at 18:48
  • Well, I did manage to import the data, and I felt I took care of the taxonomy and other matters. But the content is not appearing in drupal. The only thing I didn't do what populate the corresponding field revision tables. But I really feel like I did this carefully, and am not sure what I am missing... – Brian Huether Sep 5 '15 at 19:06
  • 1
    You obviously know how to write code. If you really want to write a custom import, I would consider writing a drush script that actually uses the drupal API to create nodes and populate them. – ericx Sep 5 '15 at 19:40
  • 1
    Lets take a look from another angle - in which ways can you export your lessons/exercises. So, mb you can export data that can be imported with Feeds module + Feeds extensible parsers – ar7max Sep 5 '15 at 20:23
  • Ahhh, ok, that would probably be the way to go! Not sure what drush is but will look into it. Thanks! – Brian Huether Sep 5 '15 at 20:47
1

I would recommend trying the Migrate module. It's a bit harder to implement compared to a direct approach but much cleaner, too.

The migrate module provides a flexible framework for migrating content into Drupal from other sources.

To get started you can check the documentation and more specifically the examples that the module comes with. I think the "beer.inc" one mostly covers migrating SQL tables to Drupal.

The concept of the module is that you define a mapping between a source and a Drupal destination and then you can import the data or rollback them in case something didn't work. There is also a UI to help you keep track of your Migration classes/settings.

0

You could use the Feeds module to import the content from that other database, similar to what is shown in this video tutorial, which shows how to import data as Drupal nodes.

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.