best way to import external mysql data into pages?

[ttbb]

hi there,
i've an old website which isn't running with the one of the wellknown CMSes but as a pretty primitive/barebone "home grown" php/mysql construction.
now it comes to migrate/import the data from this old website to a new processwire site.

one additional requirement is a structure change in the data which should be reflected during the import:
it's an art gallery website and currently all artwork entities have an artist entity as a cross reference.
this should be changed to the new structure in pw where an artwork template based pw page needs to be a child of an artist template based pw page.

i browsed xml/json/import related modules but didn't really find what i'm looking for.
of course i'm prepared to write a JSON or XML output script for the old site to prepare data for a correct import if this makes sense or might be useful.

do anybody of you have an idea where to start?

thanks
thomas

[zoeck]

Just Connect to your old database and Save your entries as Pages ??

 

o

[millipedia]

When I need to shift data over from an old site I normally export the data to a csv file and then just write a PHP script to loop through that csv and add pages.

Once you get the idea it's a very flexible way to import data.

Here's some completely untested code as an example of how I'd approach it:

// bootstrap PW
include("index.php"); 

$filename='data_to_import.csv';

// loop through this CSV
if (($handle = fopen($filename, "r")) !== FALSE) {
    while (($data = fgetcsv($handle, 2000, ",")) !== FALSE) {

        // say we have a csv that is in the form 
        // 'artist_name', 'artwork_name', 'artwork_dscription'
        // (I'm assuming this csv doesn't have a header row)

        $artist_name=$data[0];
        $artwork_name=$data[1];
        $artwork_description=$data[2];

        // see if we have a page for this artist already
        $ap=$pages->get("template=artist,name={$artist_name}");

        // if not then add this artist - in this example
        // using the template 'artist' and the parent '/artists/'
        if($ap->id < 1){
            $ap = $pages->add('artist', '/artists/', [
                    'title' => $artist_name
                    ]);
        }

        // now add our new artwork page using the artist_page as a parent
        $artwork_page = $pages->add('artwork', $ap, [
            'title' => $artwork_name,
            'description' => $artwork_description
        ]);

     
    }
    fclose($handle);
}else{
    echo 'cant open csv file for import';
}

 

[ttbb]

WOW! ?
thanks a lot, guys! awesome! ???
i didn't expect such helpful and detailed answers, especially when it's coming with a complete code snippet!
hopefully i can give back some knowledge to this community when i'm not a pw rookie anymore ?
this helps a lot!
best
thomas

[Pixrael]

2 hours ago, millipedia said:

I normally export the data to a csv

Me too, I always try to import the data using the Ryan module https://processwire.com/modules/import-pages-csv/ .. if not fit the case, then I write some code to create the pages

[bernhard]

If you are working with CSV, don't forget about the new $files->getCSV() ? 

while($row = $files->getCSV('/path/to/foods.csv')) {
  echo "Food: $row[Food] ";
  echo "Type: $row[Type] "; 
  echo "Color: $row[Color] ";
}

https://processwire.com/talk/topic/26929-pw-30197-– core-updates/

[ttbb]

thanks bernhard,
this looks pretty handy!