在php mysql中查找具有相似名称的多个表

Question

I'm trying to select data from two mysql tables using Join but I'm wanting to select from several tables with similar names such as profile_starter, profile_advanced, profile_premium, etc. My question is how can I select from the other tables without having to join more tables? I've tried * but it doesn't work:

    $inbox_sql = "
SELECT sender, recipient, time, message, viewed, name, avatar 
FROM messages 
LEFT JOIN profile_* ON profile_*.username = messages.sender
";

Please could you let me know if there's a solution to this? Thank you

Answer 1

The answer to this is that you can't. When using JOIN the table name must be present in full, you cannot use wildcards.

If the tables are the same how about looking at UNION and creating the dump of those tables in a sub query rather than joining each table to another query? - Might also be worth looking at a view, however these many not increase the performance, they would both need to be tested with your data and needs.

It sounds like you only have 3 tables here, if you simplified this example you could query INFORMATION_SCHEMA TABLES with a wildcard to list out all the matching tables, then using this output build your query with the full table names in.