May 23
One example of how to create a new Joomla user using just SQL in LQM.
The example in the LQM forum post isn’t really correct because it only checks the name field, which isn’t part of the jos_users table primary key. In fact the only part of the primary key is the ID field, which is kind of useless. I guess the question then is “what is the natural key”? I’d say the keys to really check for are username, and email address.
In the next part of this article I show the SQL I used to create a user and confirm it will create correctly without errors.
So we get the username and the email address as input, and we first confirm that neither already exists in the database. Apart from these two checks my version is the same as the one posted at the LQM forum.
The MQE SQL to create a user in Joomla using SQL from LQM 3.02 is:
<query abort="results" error="A record with that username exists">
SELECT id
FROM jos_users
WHERE username= '<input name="username" title="Username" />'
</query>
<query abort="results" error="A record with that email exists">
SELECT id
FROM jos_users
WHERE email= '<input name="email" title="Primary Email" data="email" required="y" />'
</query>
<query expect="no">
INSERT INTO jos_users (name, username, email, password , usertype, block, sendEmail, gid, registerDate, lastvisitDate,activation,params)
VALUES (
'<input name="name" title="Name" required="y" size="30" ordering="1" />'
,'<input name="username" title="Username" required="y" allowed="a-zA-Z0-9." size="30" ordering="2" />'
,'<input name="email" title="Primary Email" data="email" required="y" ordering="3" />'
, md5('<input name="password" title="Password" required="y" size="30" allowed="a-zA-Z0-9" ordering="4" />')
, 'Registered'
, '0'
, '0'
, '18'
, NOW()
,'0000-00-00 00:00:00'
,''
,'editor=jce')
</query>
<query expect="no">
INSERT INTO jos_core_acl_aro (section_value , value , order_value , name , hidden)
VALUES ('users', last_insert_id(), '0', '<input name="name" />', '0')
</query>
<query expect="no">
INSERT INTO jos_core_acl_groups_aro_map (group_id , section_value , aro_id)
VALUES ('18', '',last_insert_id())
</query>