I am not sure if what I am trying to do is even possible but, if it is, I am obviously not Googling properly and would appreciate any assistance I can get here, even if it is just a link to an "Idiot Guide".
Okay, at the moment, I have a database table of 150-odd records. Each record contains basic details (name, location, contact information, etc.) and login credentials (UserID, password, et al). These details are captured by the website admins (i.e. no general public registration) after the prospective user has undergone a successful interview process. When a record is created, a 6-char "username prefix" is assigned to the user (e.g. 'UNPREF') and this, along with the auto-incremental UserID (e.g. 125), is used as the username (e.g. UNPREF125) to log into the website. However, the username is not actually stored in the the database. Instead, when a user logs in, the login script splits the provided username and the two chunks are checked against their relevant fields.
In addition to this primary user table, there are a number of other tables which contain additional information (for instance, educational qualifications, work history, etc), which are linked to the user by means of the UserID, as per the primary table. Now, both users and admins can update a user's data and, therefore, I have created a field for each row that logs who last modified the record (modby) and when (modon) so that, if there are any shenanigans, I can ascertain who fiddled last and, in theory, deal with that particular individual without any "he said/she said" nonsense.
Now here is the tricky bit. My users and admins are stored in separate databases on separate servers (the latter being beyond my control) but I have recently discovered the joys of Federated Tables, which work brilliantly. One small quirk, tho; because my users and admins are stored in separate databases and because I want to maximise the number of records I can store in a single database (there is a size limit of 100mb per database), with the company's current rate of expansion and each branch requiring two admin accounts, it is not an improbable scenario that a user and an admin will end up with the same UserID. Therefore, the modby fields store the full username (i.e. UNPREF125 - admins get their own, unique Username Prefix so as to differentiate between admins and users)
Now, perhaps it is because I am such a newbie at Federated Tables but I can't seem to find a way to compare a field in a table on Server A (i.e. modby) with 2 separate fields (i.e. unprefix and userid) in the Federated Table, called from Server B, but I have come up with a workaround by creating an additional field in Server B's table, namely username, which stores the merged values (namely 'DBPREF125') and modby is checked against this instead, which works fine (I'm sure there is an easier way but I will save that lesson for another time).
Now, here is my question. The admin table is currently small (only 26 records) and so I captured the usernames manually, using phpMyAdmin, but I would prefer to avoid having to manually create usernames for the 150+ records in my users table. Is there any way I can get MySQL to pull the values of the userid and unprefix fields, join them together and store the result into the username field of the same record or would I need to turn to PHP for this and, if so, how would I go about this?
I apologise for the length of my question but I hope this will help explain why Google was not my friend today.
Many thanks in advance.
To store the combination in the table:
UPDATE TableB
SET username = CONCAT(unprefix, userid);
Or you can just use it when comparing:
SELECT *
FROM TableA a
JOIN TableB b
ON a.modby = CONCAT(b.unprefix, b.userid);