Mysql SELECT and UNION multiple tables with a similar name

Question

I was wondering if it is possible to create a dynamic Union query for all tables with the same name but with a different number at the end. I have created a system but with each user having their own table such as:

user_table_$userID

I have achieved this in PHP but really would like to create a more dynamic code. I currently have 2-3 nested queries to grab the posts from each table without putting strain on the web server or database.

I suppose I could count the number of users in the user login table and create a for loop:

for ($i = 1; $i >= $usrCount; $i++)
{
    $queryArray[] = "(SELECT post_title, post_description FROM user_table_" . $i . ") UNION";
}

But if the user count is a very large number the PHP script could take a long time to load. Is there any way I could get the Mysql database to create a dynamic query based on tables with the name like = "user_table_%"

If there are any suggestions please let me know.

Thank you.

Answer 1

Maybe it's better to normalize your database, but if you need a dynamic query you could use this:

SELECT
  GROUP_CONCAT(
    CONCAT(
      'SELECT * FROM `',
      TABLE_NAME,
      '`') SEPARATOR ' UNION ALL ')
FROM
  `INFORMATION_SCHEMA`.`TABLES` 
WHERE
  `TABLE_NAME` REGEXP '^user\_[0-9]*$'
INTO @sql;

PREPARE stmt FROM @sql;
EXECUTE stmt;

Please see fiddle here.