Geeklog Forums

I installed phplinks on my site Kevin Horton's RV-8 Page, and I wanted to import the links from Geeklog, rather than having to copy and paste them all. I managed to do it - here is how.

Note, I am no mysql expert - everything I know about mysql I have learned in the last two weeks by reading the online documentation, and searching the web. Always have a backup, and test after each step to be sure things are OK before moving to the next one.

I initially did the phplinks installation on my local test server, sorted it all out, then moved it to my production server, on phpwebhosting. If you want to install straight onto your production server you will have to make a few adjustments from my notes.

First, get phplinks installed and running, and creat all the categories you need, and input a couple of test links so you have some data in the tables. I had trouble running the mysql script to create the new tables. Eventually I got it to run using phpmyadmin, but I had to select the script file via the browse function.

Then extract the links and category data from the existing geeklog links tables. It is possible to extract data from the links table to a text file:

mysql -u user_name -p -e "SELECT * FROM gl_links;" geeklog_database_name > gl_links.out

Note - this gets you a delimited file of all the data in the gl_links table. It looks like any carriage returns in the Link Description field will produce a new line in the output file. I had to clean up some of my Description fields to get rid of linebreaks, then extact the data again.

Do the same thing with the gl_pllinks and gl_categories tables, so you can see how the table formats compare to the geeklog links table.

mysql -u user_name -p -e "SELECT * FROM gl_plcategories;" geeklog_database_name > gl_plcategories.out

mysql -u root -p -e "SELECT * FROM gl_pllinks;" geeklog > gl_pllinks.out

Open all tables up in a spreadsheet. Sort the spreadsheet with the gl_links data by category, so that all the links with the same category are grouped together. Now, compare the gl_links and gl_pllinks spreadsheets. We are going to delete, add and move columns as required in the gl_links spreadsheet to get the columns in the same order in both spreadsheets. There are some columns that you will have to create from scratch - e.g. LastUpdate and Added - just copy data from the test links you put in the gl_pllinks file. Check the gl_categories file, and replace the category names with the correct ID. Fill the ID column with a series of integers, starting at 1.

Then, remove the first row (list of column headings) and save the file as a tab delimited text file, being careful to use unix line endings (if you are a Mac user, the easiest way may be to copy the contents of the spreadsheet file, paste into BBEdit, and save from there).

There are several ways to import the tab delimited text file into mysql - the best way will depend on how your setup is configured. You may find that only one way will work due to permissions issues. Here are the ways I tried on my local server:

mysql -u user_name -p -e 'LOAD DATA LOCAL INFILE "full_path_to_data_file" REPLACE INTO TABLE gl_pllinks;' geeklog

get mysql running, and inside mysql type
LOAD DATA INFILE "full_path_to_data_file" REPLACE INTO TABLE gl_pllinks;

mysqlimport -u root --password -r geeklog gl_links.txt,/code>