Importing content from non-drupal database - help with SQL please

Question

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++;
  }
}

Answer 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.

Answer 2

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.