Find table name & then get data from that table.

Question

Model

   ID     Model
    1      1
    2      2
    3      4
    4      10

Model-table

ID  model_id   table_name
1     1          table1
2     3          table2
3     4          table3

Note: It is not important that every model requires table_name.

I find the table_name of model width id 3, it is table2. It is so simple fo find it, I do not see the reason to write here my sql. After finding table2, I should select from table2 and find all parametres. In this case I should write second sql. Here is the structure of table2

Note: We can find all parametres of model_id with 3.

table2

ID    model_id   param1      param2     param3
1          3        0           5          10

My question: I am looking for the way, firstly find suitable model_id in model_table and fetch table_name, THEN SELECT from table_name and fetch all parametres with 1 SQL.

Note: I have id and model_id variables on my PHP PAGE. So, we need to use Limit 1 and fetch suitable data to 1 id and model_id.

possible duplicate of [MYSQL query using variable as table name in LEFT JOIN](http://stackoverflow.com/questions/3646412/mysql-query-using-variable-as-table-name-in-left-join) — Scuzzy, Apr 22 '14 at 08:36

score 0 · Accepted Answer · answered Apr 22 '14 at 08:51

You have to do this in two queries as table names cannot be dynamic. Your code should look something like this:

$mysqli = new MySQLi(/*connection details*/);

// Fetch table name
$query = "SELECT table_name 
    FROM model-table 
    WHERE model_id = 3";
$result = $mysqli->query($query);
$row = $result->fetch_assoc($result);
$table = $row['table_name'];

// Fetch data from table2
$query = "SELECT ID, model_id, param1, param2, param3 
    FROM $table";
$result = $mysqli->query($query);
while($row = $mysqli->fetch_assoc($result)) {
    // do something with the data from the table...
}

score 0 · Answer 2 · answered Apr 22 '14 at 09:42

0

Not really what you are looking for, but this can help you too

Find all the tables of a database

SHOW TABLES

Find all the columns of a table

DESCRIBE table_name

answered Apr 22 '14 at 09:42

fluminis

3,575
4
34
47

Find table name & then get data from that table.

2 Answers2