CODEWITHSUNDEEP
phpmysqljoininner-join
Mukilan R
Mukilan R

Reputation: 445

How to get table name from mysql result?

The MySQL query is as follows

SELECT  t1.*,  t2.*, t3.*, t4.*, t5.*, t6.*
FROM table1 t1
INNER JOIN table2 t2
INNER JOIN table3 t3
INNER JOIN table4 t4
INNER JOIN table5 t5
INNER JOIN table6 t6
order by t1.updated_time, t2.updated_time, t3.updated_time, t4.updated_time, t5.updated_time, t6.updated_time desc

from the above query i need the result with their respective table name like

Array(
    [0] => stdClass Object
        (
            [id] => 1
            [cloumn1] => data1
            [column2] => table3
            [updated_time] => data1
        )
    [1] => stdClass Object
        (
            [id] => 2
            [cloumn1] => data1
            [column2] => table1
            [updated_time] => data2
        )
)

The tables have more than 15 columns which may vary.
How to achieve the result by modifying the query ?

Upvotes: 5

Views: 10277

Answers (2)

Rafael Shkembi
Rafael Shkembi

Reputation: 766

After you make your query you can make a foreach loop to get all your column names. Something like this

while($row = mysqli_fetch_assoc($query)){
    foreach($row as $key => $value){
        echo "$key=$value";
    }
}

Update

If you want to get your table names in a database you can try something like this.

$sql = "SHOW TABLES FROM database";
$result = mysqli_query($conn,$sql);

while ($row = mysqli_fetch_row($result)) {
    echo "Table: {$row[0]}\n";
}

Update

First we need to get our table names. Since you don't know the table names we can get the by using this code

$tables = array();
$sql = "SHOW TABLES FROM database";
$result = mysqli_query($conn,$sql);

while ($row = mysqli_fetch_row($result)) {
    $tables[] = $row[0];
}

if you have the tables you can add them to the array skiping the code above

$tables = array("table1","table2","table3");

After getting all the tables we can start with the database

$data = array();
foreach($tables as $table){
    $query = "select * from $table";
    $res = mysqli_query($conn,$query);
    while($row = mysqli_fetch_assoc($res)){
        $i=1;
        foreach($row as $key => $value){
            $data[$i][$key][$value];
            $i++;
        }
    }
}

Update the code to your needs. This is a example

Upvotes: 3

Georges O.
Georges O.

Reputation: 992

Another solution (used by some ORM)

1. Create a list of tables

<?php $tables = ['table1', 'table2', 'table3']; ?>

2. Execute SHOW COLUMNS FROM table to analyze the tables

http://dev.mysql.com/doc/refman/5.7/en/show-columns.html

<?php
$structures = [];
$structuresLinear = [];
foreach( $tables as $table ) {
  $query = mysqli_query('SHOW TABLES FROM ' . $table);

  while ($row = mysqli_fetch_row($query)) {
    $structures[$table][] = $row[0];
    $structuresLinear[] = sprintf('%s.%s as %s', $table, $row[0], $table . '_' . $row[0]);
  }
}

You will have on your $structures all the fields for each tables, and another one $structuresLinear with the different fields renamed.

Ex: ['table1.id as table1_id', 'table1.name as table1_name', ...]

3. Create your final query

<?php
$sql  = 'SELECT ';
$sql .= implode(', ', $structuresLinear);
$sql .= ' FROM ' . implode(' INNER JOIN ', $tables);
$sql .= ' ORDER BY ' . implode(', ',
  array_map(function($t) {
    return $t . '.updated_time';
  }, $tables)
);

?>

Output

You will have something like this:

SELECT
  table1.id as table1_id,
  table2.id as table2_id, table2.col2 as table2_col2, table2.col3 as table2_col3
FROM table1
  INNER JOIN table2
ORDER BY
  table1.updated_time,
  table2.updated_time

And the final array will be:

Array(
    [0] => Array(
        table1_id = table1.id,
        table2_id = table2.id,
        table2_col2 = table2.col2,
        table2_col3 = table2.col3,
    )
)

You can also split each $key result to have the table or create multidim array :)

Upvotes: 1

Related Questions