0

The context here is migrating users from an existing Drupal 6 site to a new site that no longer uses Drupal - or any other CMS. Drupal, at least in v6, used a rather complex mechanism for storing additional user data

  • User data went into a users table
  • you defined data keys in a separate table called profile_fields
  • and the actual values in a table called profile_values.

profile_values and users shared a uid field. In my new site I am using a single users table which is structured something like this

CREATE TABLE IF NOT EXISTS `users` (
`uid` int(10) unsigned NOT NULL AUTO_INCREMENT,
`rname` varchar(64)...

In the original Drupal profile_values table the rname entry for each user (distinct uid) is identified by that uid and an 'fid' of 14. 

CREATE TABLE IF NOT EXISTS `profile_values` (
`fid` int(10) unsigned NOT NULL DEFAULT '0',
`uid` int(10) unsigned NOT NULL DEFAULT '0',
`value` text,...

What I need to do is execute a bit of SQL which will pick up the right value (for uid = UID & fid = 14) from the profile_values table and update the users table. I vaguely suspect that this will require using INNER JOIN etc but that is well beyond my level of SQL skills (my poorest suite). I would much appreciate any tips on how this can be accomplished.

An illustration of what I am after may help here

user Table (Before)

uid        name        rname
 1          Any         - empty prior to operation
 2          Eny         - empty prior to operation

profile_values Table

uid        fid      value
 1          13        v1-13
 1          14        v1-14
 2          11        v2-11
 3          14        v2-14

user Table (After)

uid        name        rname
 1          Any        v1-14
 2          Eny        v2-14
DroidOS
  • 8,530
  • 16
  • 99
  • 171

2 Answers2

1

This is a very simple problem, and you need to learn how to join datas.

I will give you the example, but try looking around to learn about how to perform joins.

In this example, we are using a LEFT JOIN, in case of a user not having a rname, you still want to keep this users in the results.

SELECT user.uid, user.name, profile_values.value as rname
FROM user 
    LEFT OUTER JOIN profile_values
        ON user.uid = profile_values.uid
WHERE profile_values.fid = 14

Look at some posts on stackoverflow :

Community
  • 1
  • 1
Cyril Gandon
  • 16,830
  • 14
  • 78
  • 122
0

this will be the query 

update user inner join profile_values on user.uid=profile_values.uid and profile_values.fid=14 set user.rname=profile_values.value
Gaurav Singla
  • 1,405
  • 1
  • 17
  • 17