Select Data From Multiple MySQL Tables

Question

Im searching for a function in MySQL in order to Select rows from multiple tables that have a similar name. For example: Proyect_1, Proyect_2, Proyect_3

All of the tables have the same column names, the only difference between the tables is the table name. It starts with the prefix 'proyect'. The issue is that the program doesn´t know how many 'proyect' tables there are, so i can´t make a list of them and select data like always

I need something like this:

SELECT mydata FROM TABLES LIKE 'Proyect_%';

Any ideas? Thanks!

Why do you have multiple tables with the same layout? This sounds more like the issue than any SQL query problem. — Nigel Ren, Jul 03 '21 at 13:26
Because each user has his own table, It is just to make the SQL Select faster as there will be a lot of data in each table — AlexSp3, Jul 03 '21 at 13:41
But you are clearly struggling to make some of the SQL work (that's the reason why you are asking a question). There are many systems out there which have the same issues and solve it by making the right choices (indexes etc.) with a single table. — Nigel Ren, Jul 03 '21 at 13:48
Have a read of https://stackoverflow.com/questions/9774715/mysql-multiple-tables-or-one-table-with-many-columns for some more info. — Nigel Ren, Jul 03 '21 at 13:48
Agreed, this is a fundamental design flaw. Your data is denormalised. Adding new users should not require adding new tables. Selecting from a single table using a where clause to find each user's data will be very fast, and indexes can be used to help performance if necessary too — ADyson, Jul 03 '21 at 14:07

score 1 · Answer 1 · answered Jul 03 '21 at 13:23

1

if you want to search for all tables with name like Proyect then you can get from MySQL information schema.

SELECT * FROM information_schema.tables

From here you can find table by table name

answered Jul 03 '21 at 13:23

Amit Verma

2,450
2
8
21

Samir Selia · Accepted Answer · 2021-07-03T13:46:47.283

To get all tables with a common prefix

SHOW TABLES LIKE 'Proyect_%';

This will return rows of tables that matched the prefix. Example:

Proyect_1
Proyect_2
Proyect_3

In PHP you can create a UNION query that will pick up the tables returned by the above query,

$sql = "SHOW TABLES LIKE 'Proyect_%'";
$result = $conn->query($sql);

$dataQuery = array();
$query = "";
if ($result->num_rows > 0) {
  // output data of each row
  while($row = $result->fetch_array(MYSQLI_NUM)) {
     $dataQuery[] = "SELECT * FROM {$row[0]}";
  }
  $query = implode(' UNION ', $dataQuery);
}

echo $query;

Select Data From Multiple MySQL Tables

2 Answers2