I’m quite confident with basic MySQL queries, but this one has me stumped.
The problem: I am building a simple PM/Mail system where a user can sort their inbox by each heading column to quickly find what they are looking for. The user that the mail is To/From is stored in the database as a numerical value, the user ID. As the table is generated the full name of each user is fetched from another table via this ID. Obviously this presents a problem, when someone sorts their inbox by the To/From columns the user ID will be the sorting value, rather than the users name.
What I need to know: How can I order by the user names (which are stored in another table), instead of IDs? Is there some way to reference these values and associate them with the ID in the query?
It’s a little more complicated… I’ll outline the table structure to show you what we’re working with. I’ve stripped it down to the columns we need to see and colour coded the relationships between columns / tables for clarity.
TABLE 1 - Mail
Columns: id, sender_type, sender_id
TABLE 2 - Sender Types
Columns: id
TABLE 3 - Contains full names Can be any one of 3 database tables depending on sender type. Each contains the following consistent columns amongst others unique to that table.
Columns: id, first_name, last_name
I need to be able to order the list of mails fetched from table 1 by the combined first_name and last_name value of table 3… I’m out of my depth and would greatly appreciate a solution (code if possible) from someone that knows how to achieve this.