Print

Migrate Joomla Users to WordPress

This is for Joomla 1.5.x sites!
Note that this article is for Joomla 1.5.x sites to be converted to Wordpress 3.2.x sites. I started with a Joomla 1.5 as the move from Joomla 1.6 or greater is a lot easier since it uses nested categories like Wordpress.

There are lots of commercial migrators out there and they all seem to have this problem. I'm really keen not to ask all my users to have to change their passwords but that is what the commercial applications are doing.

The script to transfer users
This is a free solution to at least get your user accounts all migrated. How you deal with the passwords can then be up to you. To use the following script, you need to change the my_wordpress_db to the name of your wordpress database and change my_joomla_db to the name of your joomla database. Note that your database user should have access to both databases in order for this to run smoothly:
copyraw
INSERT INTO my_wordpress_db.wp_users (ID, user_login, user_nicename, user_email, user_url, user_registered, user_activation_key, user_status, display_name )
SELECT
	a.id 'ID',
	a.username 'user_login',
	REPLACE(TRIM(LOWER(a.username)), ' ', '_') AS 'user_nicename',
	a.email AS 'user_email',
	' ' AS 'user_url',
	a.registerDate AS 'user_registered',
	' ' AS 'user_activation_key',
	CASE a.block
		WHEN 0 THEN 2 
		ELSE 0 
	END AS 'user_status', -- NOTE THAT THIS IS DEPRECATED IN WORDPRESS 3.X
	a.name AS 'display_name'
FROM
	my_joomla_db.jos_users a
ORDER BY
	a.id;
  1.  INSERT INTO my_wordpress_db.wp_users (ID, user_login, user_nicename, user_email, user_url, user_registered, user_activation_key, user_status, display_name ) 
  2.  SELECT 
  3.      a.id 'ID', 
  4.      a.username 'user_login', 
  5.      REPLACE(TRIM(LOWER(a.username)), ' ', '_') AS 'user_nicename', 
  6.      a.email AS 'user_email', 
  7.      ' ' AS 'user_url', 
  8.      a.registerDate AS 'user_registered', 
  9.      ' ' AS 'user_activation_key', 
  10.      CASE a.block 
  11.          WHEN 0 THEN 2 
  12.          ELSE 0 
  13.      END AS 'user_status', -- NOTE THAT THIS IS DEPRECATED IN WORDPRESS 3.X 
  14.      a.name AS 'display_name' 
  15.  FROM 
  16.      my_joomla_db.jos_users a 
  17.  ORDER BY 
  18.      a.id; 

A bit more to do: PHP file
So I needed to do a loop and rather than complicate things with stored procedures and cursors, it's likely if you're using either Joomla or Wordpress that you have a PHP enabled server available. Here's code to top off the user migration, save it to an empty PHP file and upload/run it on your server:
please test before using!
copyraw
<?php

	// to run this script this user needs to be able to access both databases
	$userName   = "WordPressDB_sqluser";
	$password   = "MyUnguessablePassword";
	$hostName   = "localhost";
	$database   = "WordPressDB";


	// Connect to the Database
	if (!($link=mysql_connect($hostName, $userName, $password))) {
		print(sprintf("error connecting to host %s, by user %s", $hostName, $userName));
		exit();
	}
	//----------------

	// Select the Database
	if (!mysql_select_db($database, $link)) {
		print(sprintf("Error in selecting %s database", $databaseName));
		print(sprintf("error:%d %s", mysql_errno($link), mysql_error($link)));
		exit() ;
	}

	$query='
SELECT
	a.id \'user_id\',
	REPLACE(TRIM(LOWER(a.username)), \' \', \'_\') AS \'user_nicename\',
	a.email AS \'user_email\',
	a.params AS \'user_params\',
	CASE a.usertype
		WHEN \'Super Administrator\' THEN 10
		WHEN \'Administrator\' THEN 9
		WHEN \'Manager\' THEN 8
		WHEN \'Public Backend\' THEN 7
		WHEN \'Publisher\' THEN 6
		WHEN \'Editor\' THEN 5
		WHEN \'Author\' THEN 4
		WHEN \'Publisher\' THEN 3
		WHEN \'Editor\' THEN 2
		WHEN \'Author\' THEN 1
		ELSE 0
	END AS \'user_status\',
	CASE a.block
		WHEN 1 THEN 2
		ELSE 0
	END AS \'user_block\',
	a.name AS \'display_name\'
FROM
	my_joomla_db.jos_users a
ORDER BY
	a.id;
	';

	$results = mysql_query( $query );
	while ( $row = mysql_fetch_assoc( $results ) ) {
		$this_id = $row['user_id'];
		$this_name = $row['user_nicename'];
		$this_email = $row['user_email'];
		$this_status = $row['user_status'];
		$this_block = $row['user_block'];
		$this_params = $row['user_params'];
		$this_dispname = $row['display_name'];

		$wp_capabilities=($this_status==10)?'a:1:{s:13:"administrator";s:1:"1";}':'a:1:{s:10:"subscriber";s:1:"1";}';

		$insert_query = '
INSERT INTO wp_usermeta ( user_id, meta_key, meta_value ) VALUES ( '.$this_id.', \'first_name\', \' \' );
INSERT INTO wp_usermeta ( user_id, meta_key, meta_value ) VALUES ( '.$this_id.', \'last_name\', \' \' );
INSERT INTO wp_usermeta ( user_id, meta_key, meta_value ) VALUES ( '.$this_id.', \'nickname\', \''.$this_name.'\' );
INSERT INTO wp_usermeta ( user_id, meta_key, meta_value ) VALUES ( '.$this_id.', \'description\', \' \' );
INSERT INTO wp_usermeta ( user_id, meta_key, meta_value ) VALUES ( '.$this_id.', \'rich_editing\', \'true\' );
INSERT INTO wp_usermeta ( user_id, meta_key, meta_value ) VALUES ( '.$this_id.', \'comment_shortcuts\', \'false\' );
INSERT INTO wp_usermeta ( user_id, meta_key, meta_value ) VALUES ( '.$this_id.', \'admin_color\', \'fresh\' );
INSERT INTO wp_usermeta ( user_id, meta_key, meta_value ) VALUES ( '.$this_id.', \'use_ssl\', 0 );
INSERT INTO wp_usermeta ( user_id, meta_key, meta_value ) VALUES ( '.$this_id.', \'show_admin_bar_front\', \'true\' );
INSERT INTO wp_usermeta ( user_id, meta_key, meta_value ) VALUES ( '.$this_id.', \'show_admin_bar_admin\', \'false\' );
INSERT INTO wp_usermeta ( user_id, meta_key, meta_value ) VALUES ( '.$this_id.', \'aim\', \' \' );
INSERT INTO wp_usermeta ( user_id, meta_key, meta_value ) VALUES ( '.$this_id.', \'yim\', \' \' );
INSERT INTO wp_usermeta ( user_id, meta_key, meta_value ) VALUES ( '.$this_id.', \'jabber\', \' \' );
INSERT INTO wp_usermeta ( user_id, meta_key, meta_value ) VALUES ( '.$this_id.', \'wp_capabilities\', \''.$wp_capabilities.'\' );
INSERT INTO wp_usermeta ( user_id, meta_key, meta_value ) VALUES ( '.$this_id.', \'wp_user_level\', '.$this_status.' );
		';
		$insert_result = mysql_query ( $insert_query );
	}


/*
	if (!($result=mysql_query(sprintf($stmt), $link))) {
		print(sprintf("Error in executing %s stmt", $stmt));
		print(sprintf("error:%d %s", mysql_errno($link), mysql_error($link)));
		exit();
	}
*/

	mysql_close($link);
	//----------------

?>
  1.  <?php 
  2.   
  3.      // to run this script this user needs to be able to access both databases 
  4.      $userName   = "WordPressDB_sqluser"
  5.      $password   = "MyUnguessablePassword"
  6.      $hostName   = "localhost"
  7.      $database   = "WordPressDB"
  8.   
  9.   
  10.      // Connect to the Database 
  11.      if (!($link=mysql_connect($hostName, $userName, $password))) { 
  12.          print(sprintf("error connecting to host %s, by user %s", $hostName, $userName))
  13.          exit()
  14.      } 
  15.      //---------------- 
  16.   
  17.      // Select the Database 
  18.      if (!mysql_select_db($database, $link)) { 
  19.          print(sprintf("Error in selecting %s database", $databaseName))
  20.          print(sprintf("error:%d %s", mysql_errno($link), mysql_error($link)))
  21.          exit() ; 
  22.      } 
  23.   
  24.      $query=' 
  25.  SELECT 
  26.      a.id \'user_id\', 
  27.      REPLACE(TRIM(LOWER(a.username)), \' \', \'_\') AS \'user_nicename\', 
  28.      a.email AS \'user_email\', 
  29.      a.params AS \'user_params\', 
  30.      CASE a.usertype 
  31.          WHEN \'Super Administrator\' THEN 10 
  32.          WHEN \'Administrator\' THEN 9 
  33.          WHEN \'Manager\' THEN 8 
  34.          WHEN \'Public Backend\' THEN 7 
  35.          WHEN \'Publisher\' THEN 6 
  36.          WHEN \'Editor\' THEN 5 
  37.          WHEN \'Author\' THEN 4 
  38.          WHEN \'Publisher\' THEN 3 
  39.          WHEN \'Editor\' THEN 2 
  40.          WHEN \'Author\' THEN 1 
  41.          ELSE 0 
  42.      END AS \'user_status\', 
  43.      CASE a.block 
  44.          WHEN 1 THEN 2 
  45.          ELSE 0 
  46.      END AS \'user_block\', 
  47.      a.name AS \'display_name\' 
  48.  FROM 
  49.      my_joomla_db.jos_users a 
  50.  ORDER BY 
  51.      a.id; 
  52.      '
  53.   
  54.      $results = mysql_query( $query )
  55.      while ( $row = mysql_fetch_assoc( $results ) ) { 
  56.          $this_id = $row['user_id']
  57.          $this_name = $row['user_nicename']
  58.          $this_email = $row['user_email']
  59.          $this_status = $row['user_status']
  60.          $this_block = $row['user_block']
  61.          $this_params = $row['user_params']
  62.          $this_dispname = $row['display_name']
  63.   
  64.          $wp_capabilities=($this_status==10)?'a:1:{s:13:"administrator";s:1:"1";}':'a:1:{s:10:"subscriber";s:1:"1";}'
  65.   
  66.          $insert_query = ' 
  67.  INSERT INTO wp_usermeta ( user_id, meta_key, meta_value ) VALUES ( '.$this_id.', \'first_name\', \' \' )
  68.  INSERT INTO wp_usermeta ( user_id, meta_key, meta_value ) VALUES ( '.$this_id.', \'last_name\', \' \' )
  69.  INSERT INTO wp_usermeta ( user_id, meta_key, meta_value ) VALUES ( '.$this_id.', \'nickname\', \''.$this_name.'\' )
  70.  INSERT INTO wp_usermeta ( user_id, meta_key, meta_value ) VALUES ( '.$this_id.', \'description\', \' \' )
  71.  INSERT INTO wp_usermeta ( user_id, meta_key, meta_value ) VALUES ( '.$this_id.', \'rich_editing\', \'true\' )
  72.  INSERT INTO wp_usermeta ( user_id, meta_key, meta_value ) VALUES ( '.$this_id.', \'comment_shortcuts\', \'false\' )
  73.  INSERT INTO wp_usermeta ( user_id, meta_key, meta_value ) VALUES ( '.$this_id.', \'admin_color\', \'fresh\' )
  74.  INSERT INTO wp_usermeta ( user_id, meta_key, meta_value ) VALUES ( '.$this_id.', \'use_ssl\', 0 )
  75.  INSERT INTO wp_usermeta ( user_id, meta_key, meta_value ) VALUES ( '.$this_id.', \'show_admin_bar_front\', \'true\' )
  76.  INSERT INTO wp_usermeta ( user_id, meta_key, meta_value ) VALUES ( '.$this_id.', \'show_admin_bar_admin\', \'false\' )
  77.  INSERT INTO wp_usermeta ( user_id, meta_key, meta_value ) VALUES ( '.$this_id.', \'aim\', \' \' )
  78.  INSERT INTO wp_usermeta ( user_id, meta_key, meta_value ) VALUES ( '.$this_id.', \'yim\', \' \' )
  79.  INSERT INTO wp_usermeta ( user_id, meta_key, meta_value ) VALUES ( '.$this_id.', \'jabber\', \' \' )
  80.  INSERT INTO wp_usermeta ( user_id, meta_key, meta_value ) VALUES ( '.$this_id.', \'wp_capabilities\', \''.$wp_capabilities.'\' )
  81.  INSERT INTO wp_usermeta ( user_id, meta_key, meta_value ) VALUES ( '.$this_id.', \'wp_user_level\', '.$this_status.' )
  82.          '
  83.          $insert_result = mysql_query ( $insert_query )
  84.      } 
  85.   
  86.   
  87.  /* 
  88.      if (!($result=mysql_query(sprintf($stmt), $link))) { 
  89.          print(sprintf("Error in executing %s stmt", $stmt))
  90.          print(sprintf("error:%d %s", mysql_errno($link), mysql_error($link)))
  91.          exit()
  92.      } 
  93.  */ 
  94.   
  95.      mysql_close($link)
  96.      //---------------- 
  97.   
  98.  ?> 
Still to do for passwords Joomla: MD5( password + salt )
Wordpress: MD5( password ) _ Salt

Although one way, is it possible to extract the md5 of the Joomla password and process using wordpress script for passwords?

TO BE CONTINUED...
Category: Wordpress :: Article: 393