Drupal to Processwire Notes and Snippets

vim-html-code

These notes are for a project converting a relatively complex drupal 6 site to processwire.  This particular site had a number of relatively complex interrelationships between the data that had to be accounted for. It had also come through multiple versions of drupal over the years had some cleaning up to do.

Because of the amount of data that needed to be cleaned wide use of csv files was more useful than a straight database to database transfer.  More will be added to this later.

SQL Snippets

  • Creating current drupal taxonomy links on pages to their processwire counterparts (some other work before this with views exports, csv imports and then creating a temporary file holding the relationships.  The excel commands =SPLIT(A1;”,”) and =TRANSPOSE(A1:Z1) to convert the views csv export to rows is helpful.
  • UPDATE conditions_patterns_temp INNER JOIN field_title ON conditions_patterns_temp.Conditions = field_title.data SET conditions_patterns_temp.cond_id = field_title.pages_id
  • Updating urls from a temp table in phpmyadmin
  • UPDATE pages INNER JOIN field_title ON pages.id = field_title.pages_id INNER JOIN temp_iherb_functions ON field_title.data = temp_iherb_functions.data SET pages.name = temp_iherb_functions.url
  • Build a table of page ids to create relationships later
  • UPDATE temp_fherb_ingredients INNER JOIN field_fherb_chinese_name ON temp_fherb_ingredients.formulaname = field_fherb_chinese_name.data SET temp_fherb_ingredients.formulaid = field_fherb_chinese_name.pages_id

Excel Snippets

  • Since this is an old drupal site (pre d7 fields) there are many times where you have to split a body into what will become multiple fields.  In the csv via google sheets the following may be useful:
  • A1 part1:part2
  • B1  =RIGHT(A1,LEN(A1)-Find(“:”,A1)) = part2
  • C1  =SUBSTITUTE(A1,B1,””) = part1:
  • A1 part1>part2
  • B1  =LEFT(A2,FIND(“>”,A2)-1) = part1
  • C1 = SUBSTITUTE(A2,B2,””) = >part2

PHP Snippets

  • This is a small script to enumerate through an array that has various mappings and set the sort order column so that you can create fields with multiple relationships in processwire:

  • $query = $database->prepare("SELECT formulaid, indherbid, sortorder FROM temp_fherb_ingredients");
    $query->execute();
    $result = $query->fetchAll();
    if (count($result)) {
    foreach($result as $row) {
    $formulaid = $row['formulaid'];
    $indherbid = $row['indherbid'];
    if ($justusedid != $formulaid) { $cnumber = 0; } else { $cnumber++; }
    $sortorder = $row['sortorder'];
    $justusedid = $formulaid;
    $query = $database->prepare("UPDATE temp_fherb_ingredients SET sortorder = $cnumber WHERE formulaid = $formulaid AND indherbid = $indherbid");
    $query->execute();
    }
    }

Using CSV Files to Update Database Tables with phpMyAdmin

vim2-html

For many reasons either when moving between different cms systems perhaps or for more general reasons it is useful to import data with csv files.  Often this is because it is easier to manipulate the data in a spreadsheet and then create mass updates.

phpMyAdmin doesn’t allow you to update data from a CSV file directly.  You can import the data into an empty table, but you cannot update existing records.  To get around this, you can follow these steps:

  1. Export the data that you need from the current table – in part so you can see how it is structured.
  2. Then edit this file in a spreadsheet program.  After you changes are finished then export the file as csv.  In this example we are updating references to page urls in a table “pages” and our data file has simply id and a url instead of all the data.
  3. In phpmyadmin create a new temporary table
    CREATE pages_url_update LIKE pages (which would create a replica -- or just create the columns you need -- in this example an id and a url)
  4. Open that table in phpMyAdmin and then choose “import”
    — Select the csv to upload
    — And choose CSV Using LOAD DATA under Format
  5. Then select the true data table and click on the sql button
  6. Then do
    UPDATE pages
    INNER JOIN pages_url_update on pages_url_update.id = pages.id
    SET pages.url = pages_url_update.url
  7. Then drop the temporary table – DROP pages_url_update