SQL: Set a Field from a linked Table

A little hack for the Joomla Community Builder

Recently, I received a job request. An existing web application will be greatly expanded, and therfore converted to use the CMS “Joomla”. For the management of members  the extension “Community Builder” will be used.
Previously, users had a number as login name. This number should now be available in a field “member number” (german: mitgliedsnummer), and the numbers from existing users should be transferred. So much for the request from a customer perspective.

Analysis

Joomla manages the users in a table jos_user. The “community builder” allows to add additonal fields to the user record. The fields are placed in the separate table “jos_profiler”. These records are linked with the ID of the user. In this way, the Community Builder does not need to change the table of the Joomla core. However, fiddling with the two tables is technically more complex and error-prone.

The requirement is therefore: For all jos_user records with a number in the field username , put this number in the field member_number of the corresponding member record in the table jos_profiler.

Is it possible with SQL?

You can surely make a small PHP program to solve the problem . But if we can do it just with a SQL command, it is much easier. This was the reason for me to inspect the documentation for MySQL.

Fortunately, the MySQL database can use regular expressions. So you can retrieve all user names that consist only of digits with this statement:

SELECT username FROM jos_users WHERE username REGEXP '^[0-9]+$'

Linking two tables

The link between two database tables is also called a “join”, in our case a “inner equijoin”.
To show the username field and the membership number at the same time, we use:

SELECT jos_users.username, jos_comprofiler.cb_mitgliedsnummer
FROM jos_comprofiler, jos_users
WHERE jos_comprofiler.user_id = jos_users.id

This statement will also helps us to check whether everything is set correctly

Setting data

As a final step remains: set the field of one record with the contents of the connected record of the other table. Fortunately, the above two techniques are also working in the SQL UPDATE command.

UPDATE jos_comprofiler, jos_users
SET jos_comprofiler.cb_mitgliedsnummer = jos_users.username
WHERE jos_comprofiler.user_id = jos_users.id
  AND jos_users.username REGEXP '^[0-9]+$'

With a single SQL command, the customer problem is solved. The magic that makes this possible is the mathematical foundation of SQL, the relational algebra.