| Blog | Status Updates | About | Contact | Advertise

February 1, 2010 / Filed under: Google, MySQL, PHP

Our previous post on migrating Google Notebook entries to a personal database only touched briefly on how to setup your own notebook application using PHP and MySQL.

I've refined the script and database structure slightly.

Overview

Using PHP, I've set up a script that reads a Google Notebook Atom XML file, and inserts each note into a few database tables.

I am then developing a front-end interface to view, add, and edit notebook entries. I will only cover the initial migration aspect in this article - developing the front-end interface is up to you.

Create database tables

First, create three MySQL database tables:

CREATE TABLE `note` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `date_created` datetime NOT NULL,
  `date_modified` datetime DEFAULT NULL,
  `content` text NOT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `category` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(250) NOT NULL,
  `slug` varchar(250) NOT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `note_category` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `noteid` int(11) NOT NULL,
  `categoryid` int(11) NOT NULL,
  PRIMARY KEY (`id`)
);

A description of each table is below:

• note: Holds each individual notebook entry.
• category: Holds each individual notebook name.
• note_category: Holds the relationship of each notebook entry to notebook name.

Insert Notebook names as categories

Since I only had a dozen or so individual notebooks, I just manually inserted each into the category table:

INSERT INTO `category` (`name`, `slug`) VALUES ('My Notebook', 'my-notebook');

Note the unique ID for each notebook, which will be used when importing individual notes.

Create PHP script

This example script assumes the following things:

• $db_conn is a database connection object, with query being a method of that object.
• mynotebook.xml is the name of your exported Google Notebook Atom file.

$notes = file_get_contents("mynotebook.xml");

$notes = simplexml_load_string($notes);

foreach ($notes -> entry as $note)
{
    $noteid = &$db_conn -> query("INSERT INTO note (date_created, date_modified, content) VALUES (NOW(), '" . date( "Y-m-d G:i:s", strtotime($note -> updated) ) . "', '<p>" . str_replace( "'", "\'", $note -> content ) . "</p>')") -> insertId();

    $category_insert = &$db_conn -> query("INSERT INTO note_category (noteid, categoryid) VALUES (" . $noteid . ", 1)");

    echo "Note #" . $noteid . " inserted successfully.<br>";
}

You need to also change the "1" in the $category_insert query to match the ID of the category that the notes are being inserted under.

Export and upload Google Notebook file

In Google Notebook, click the "Export" link at the bottom of page, while viewing the notebook you wish to migrate:

Then click "Atom" to export the notebook as an XML file:

Upload this file to your web server, where your PHP script can access it.

In your script that loops through each note, change the source file in this line to match the name of your uploaded XML file:

$notes = file_get_contents("mynotebook.xml");

Final steps

Run the PHP page in your browser, and you should see a confirmation that each note got imported into your own database.

Do this for each individual Google Notebook, and you'll have all of your notes safely backed up, and available to use for creating your own notebook interface.