MySQL Insert to two seperate tables with pivot

Question

Starting with a single table (user_import) brought in from .csv

| Name       | Login | Email                | CustomA | CustomB |
+------------+-------+----------------------+---------+---------+
| John Smith | johns | john_smith@gmail.com | Blarg   | Narx    |
| Max Power  | maxp  | max_power@gmail.com  | Jarg    | Lipdo   |
+------------+-------+----------------------+---------+---------+

Attempting to have it populate a Joomla! users table

| id  | name       | username | email                | ...
| 514 | Super User | admin    | admin@gmail.com      | ...
| 515 | John Smith | johns    | john_smith@gmail.com | ...
| 516 | Max Power  | maxp     | max_power@gmail.com  | ...

and insert any custom custom fields to the user_profiles table

+---------+-------------------------------------+---------------+----------+
| user_id | profile_key                         | profile_value | ordering |
+---------+-------------------------------------+---------------+----------+
|     515 | customprofile.custom_a              | "Blarg"       |        1 |
|     515 | customprofile.custom_b              | "Jarg"        |        2 |
|     516 | customprofile.custom_a              | "Narx"        |        1 |
|     516 | customprofile.custom_b              | "Lipdo"       |        2 |
+---------+-------------------------------------+---------------+----------+

I don't think there is a way to do this in a single call as the user_id has to auto_increment

First query is pretty strait forward

INSERT INTO prknc_users (name, username, email, params, password)
SELECT Name, Login, Email, '{}', 'tuChaSw-tEte72_!eSW#muc3@trew8steZacra2e7a7R6yuqAyeSAXUy=Stu'
FROM user_import;`

The second one is the one I need some help with, tried with this for one:

INSERT INTO user_profiles (user_id, profile_key, profile_value, ordering)
SELECT (SELECT users.id FROM users, user_import WHERE users.email = user_import.Email), 'customprofile.custom_a',user_import.CustomA, '1'
FROM user_import;

Failing hard. Please help me out if you can.

You know you need to also insert in the the user group map table, right? — Elin, Nov 29 '13 at 02:32
Yah, Joomla! doesn't get angry when I don't insert them but if I ever want them to be able to login I'll want to assign them to the "Registered" group at the very least. I'd love it if I could do all of this in 2-3 queries but I'll obviously settle for something that works. | user_id | group_id | +---------+----------+ | 514 | 8 | | 515 | 2 | | 516 | 2 | +---------+----------+ — thenewkid, Nov 29 '13 at 04:59
Use the api rather than trying to write your own queries. We have an API for a reason. I would look at the profile API as well, IIRC there is direct support for profiles in JUser or JUserHelper. — Elin, Nov 29 '13 at 16:14
That involves using PHP and IMO is a lot more work when dealing with a large user import... I am very interested in your book however :) do they sell it in any stores in Canada? — thenewkid, Nov 29 '13 at 17:29
My friend saw it in Toronto so I guess so! No, it's actually less work, as you can see from the time you have spent trying to get the query right. Do you know how to write a joomla commandline application? Then you can just read through your data inserting everything correctly. You might really like the Joomla Programming book. Here's a (web) application I wrote for fixing assets just to give you an idea of how you might approach it. https://github.com/elinw/AssetFix/blob/j3/assetfix.php — Elin, Nov 29 '13 at 18:47

score 1 · Accepted Answer · answered Nov 30 '13 at 16:47

1

You can try an application like this

https://gist.github.com/elinw/5b579e18b9613f08330d

Just make sure to make the changes that make sense in your use case .

answered Nov 30 '13 at 16:47

Elin

6,507
3
25
47

Thanks! Will definitely take a look at this. – thenewkid Dec 02 '13 at 08:17
https://github.com/elinw/AddUsersFromTable/blob/master/AddUsersFromTable.php Updated it. – Elin Dec 06 '13 at 11:36
Finally got around to using this and it's definitely the better way to go. Very happy you introduced me to the CLI as I've been using it for all sorts of mass activities! Would have been a lot harder to get started without your sample code. Bought a copy of your book as well. Thanks again. – thenewkid Feb 24 '14 at 20:26

score 0 · Answer 2 · answered Nov 29 '13 at 01:06

0

Inner select can return multiple values and not insync with other colums in the outer select. You should move the last three columns into the first select.

INSERT INTO user_profiles (user_id, profile_key, profile_value, ordering)
(SELECT users.id, 'customprofile.custom_a',user_import.CustomA, '1' 
    FROM users, user_import 
    WHERE users.email = user_import.Email)

answered Nov 29 '13 at 01:06

gmansoor

509
1
4
12

Still getting the following: ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '=' – thenewkid Nov 29 '13 at 04:20
whats the type of email in both tables? – gmansoor Nov 29 '13 at 15:36
Had to alter my user_import table - ALTER TABLE user_import CONVERT TO CHARACTER SET utf8; Didn't need the () around SELECT anymore either. Posted an answer above that allows you to do both in a single query. Thanks for your help! – thenewkid Nov 29 '13 at 17:35
glad it worked. here is an another similar question http://stackoverflow.com/questions/11770074/illegal-mix-of-collations-utf8-unicode-ci-implicit-and-utf8-general-ci-implic – gmansoor Nov 29 '13 at 17:41

score 0 · Answer 3 · answered Nov 29 '13 at 17:33

INSERT INTO user_profiles (user_id, profile_key, profile_value, ordering)
  SELECT users.id,'customprofile.custom_b',user_import.CustomB, '2'
    FROM users, user_import 
    WHERE users.email = user_import.Email
UNION
  SELECT users.id,'customprofile.custom_a',user_import.CustomA, '1'
  FROM users, user_import
  WHERE users.email = user_import.Email;

MySQL Insert to two seperate tables with pivot

3 Answers3