Populating craft database from non-craft data

Question

Is it possible to populate the Craft content database table with entries from a non-craft database (which isn't a live database, it's a local copy). I've set up my channels etc for the incoming data which corresponds to the data from my other db.

Answer 1

Brad is absolutely correct. Better off trying for an existing plugin (we have one in the works over here for very robust Wordpress migrating!!!), but here are a few steps to get you started:

Move the non-craft database tables over to a staging server with your craft tables, and rename them with a craft_ prefix (or whatever you have set as your prefix in config). Query for the entries

$query = craft()->db->createCommand()
->select('*')
->from('your-non-craft-post-table')
->where('any conditions')
->queryAll();

Loop through the query results, creating new Entry Models and assigning columns to your fields.

//Go through each $query result
foreach($query as $old_entry){
    $entry = new EntryModel();
    $entry->sectionId = your_craft_section_id;
    $entry->authorId = your_craft_user_id;
    $entry->postDate = $old_entry['old_post_date_column'];
    $entry->enabled = true;

    //This is the fun part - mapping content to your custom fields
    $entry->getContent()->title = $old_entry['old_title_column'];
    $entry->getContent()->yourCustomFieldHandle = $old_entry['column_to_map_to_custom_field'];

    //Use the Entries service to save the new entry
    $success = craft()->entries->saveEntry($entry);
}

Obviously there is more to it than that...mapping user accounts, categories, tags, assets, etc. But hopefully this will help you get started!

Answer 2

You might want to consider a CSV import as well. You could export the data out of the db using something like phpmyadmin's export to csv and then import the content back into Craft using the indispensable import plugin by Bob Olde Hampsink to do the dirty work—it'll handle things like which author and mapping the fields and channels to the right places.

Obviously this will only work on a dump-by-dump case; if you need it continually updated, then writing your own might be the way to go.

Answer 3

aberkie's answer is spot on. I just spent the last couple days doing exactly this — importing data from a non-craft database — so thought I would share some additional tips and files that helped. I also used 'PDO' to connect to the database instead of the 'Yii' connection method, but use whatever you like.

• Create your plugin called 'ImportPlugin' (or whatever) with a main plugin file ('import/ImportPlugin.php') and a services file ('import/services/ImportService.php').
• Recommend installing a plugin called 'amcommand' which does two things that are really helpful: 1) it creates a command palette with the option to 'delete all data' from a section, so that you can easily try again if something isn't quite right the first time (which it won't be); 2) it lets you define your own commands, by writing a few simple lines in your plugin file, so you can easily trigger your import service methods.
• Open up your craft database and old database in Sequel Pro for reference.
• Open up your 'craft.log', 'php_errors.log' and 'import.log' in console windows using tail -f /path/to/craft/runtime/logs/logfile.php for monitoring ('import.log' will be created automatically the first time you use it).
• Recommend working on one section at a time, and backing up your craft database after each successfully import.

In craft/plugins/import/ImportPlugin.php:

<?php
namespace Craft;

class ImportPlugin extends BasePlugin
{

    public function getName()
    {
        return 'Import';
    }

    public function getVersion()
    {
        return '0.1';
    }

    public function getDeveloper()
    {
        return craft()->getSiteName();
    }

    public function getDeveloperUrl()
    {
        return 'http://domain.com';
    }

    public function hasCpSection()
    {
        return false;
    }

    public function init()
    {
        parent::init();

        /*
         * This will call your import service method directly when loading
         * any craft template or viewing any page in the CP.
         * Useful for testing your output using `var_dump($vars)`.
         * Not so good for importing — because it may get called more than once.
         * Uncomment if needed.
         */
        //craft()->import->importRecords();

    }

    // Add custom commands to the 'amcommand' palette plugin
    public function addCommands() {
        $commands = array(

            array(
                'name' => 'Import Records',
                'type' => 'Custom',
                'call' => 'importRecords',
                'service' => 'import'
            ),
            // add more commands as needed

        );
        return $commands;
    }

}

In 'craft/plugins/import/services/ImportService.php' (Every case is different; this is just the process I happened to need. Use aberkie's solution if that is simpler):

<?php
namespace Craft;

class ImportService extends BaseApplicationComponent
{

    public function importRecords()
    {   
        $username = 'username';
        $password = 'password';

        try {
            $db = new \PDO('mysql:host=localhost;dbname=old_database;charset=utf8', $username, $password);
            $db->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
        } catch(PDOException $e) {
            echo 'ERROR: ' . $e->getMessage();
        }

        // Write query as needed (in this example I needed to join content from multiple tables)
        $query = 
            "SELECT a.id, b.id, b.tableA_id, c.tableB_id, a.field1, a.field2, b.field3, b.field4, c.field5, c.field6, c.field7
            FROM table_A a
            LEFT JOIN (table_B b, table_C c)
            ON (a.id = b.tableA_id AND b.id=c.tableB_id)
            ORDER BY a.id
            ";

        $result = $db->query($query);

        // reformat results for craft
        $records = [];

        foreach($result as $row)
        {
            /* Because of the sql table join I have more than one row
             * with the same id, so don't recreate the record if it 
             * already exists. Arbitrarily using row['id'] as key to join rows.
             */ 
            if (!array_key_exists($row['id'])
            {
                // map data
                $records[$row['id']] = array(
                    'craftfield1'   => $row['field1'],
                    'craftfield2'   => $row['field2'],
                    'craftfield3'   => [],
                );
            }

            /* if you need to combine data from multiple rows
             * into a single field in craft for some reason you can do it here.
             * (i.e. pushing content into a single craft table field)
             */

            array_push( $records[$row['id']]['craftfield3'] , array("col1" => $row['field3'], "col2" => $row['field4']) );

        }

        // create your craft entries
        foreach($records as $record) {

            $entry = new EntryModel();
            $entry->sectionId   = 1; // Use 'id' from 'craft_sections' table
            $entry->typeId      = 1; // Use 'id' from 'craft_entrytypes' table
            $entry->authorId    = 1; // Use 'id' from 'craft_users' table
            $entry->enabled     = true;

            $entry->getContent()->setAttributes($record);

            // Save entry
            $success = craft()->entries->saveEntry($entry);

            if (!$success)
            {
                $errors = $entry->getErrors();
                foreach ($errors as $error) {
                    ImportPlugin::log('Error:'.$error[0], LogLevel::Error);
                }
            }
            else
            {
                ImportPlugin::log('Successfully saved entry "'.$entry->id.'"', LogLevel::Info);
            };

            /* Print $records for debugging if needed.
             * Note: Will only work if calling this service method directly from
             * plugin's 'init' function, otherwise this will be a background process.
             */ 
            //var_dump($record);
            //print_r($record);
            //echo "<br><br>";
        }

    }
}

Answer 4

It's not just the craft_content table that you'll need to worry about because entry data is stored in multiple tables.

You're going to be better off using an existing import plugin or writing your own custom plugin that interacts with Craft's services... specifically craft()->entries->saveEntry().

See here for more info.