how can I do to return a query grouping by the table name using pdo, php and mysql?

Question

how can I do to return a query grouping by the table name using pdo, php and mysql?

Navigation

#1 by (2 votes)
#2 by (0 votes)

3

How can I do to return a query grouping by the name of the table using pdo, php and mysql?

For example I want to make a query and return a json equal to this one, using pdo, how?

{
    "nome da tabela ": [{
        "nome da coluna": "225",
        "nome da coluna": "16",

    }],
    "outra tabela": [{
        "nome da coluna": "226",
        "nome da coluna": "16",
        "nome da coluna": "95",

    }, {
        "nome da coluna": "226",
        "nome da coluna": "16",
        "nome da coluna": "99",

    }]
}

php

asked by anonymous 21.11.2016 / 12:27

2 answers

Doubt with the use of the Optional How to show data from two tables in C #?

score 2 · Answer 1

Try this:

$db = new PDO('mysql:host=localhost;dbname=DB_Tests', 'asdfds', 'dsad');

$tabelas = array('users', 'posts'); // aqui coloca as tabelas que quer
$dados = array();
foreach($tabelas as $table) {
    $dados[$table] = array();
    $sql = 'SELECT * FROM ' .$table;
    $data = $db->prepare($sql);
    $data->execute();
    foreach($data->fetchAll(PDO::FETCH_OBJ) as $collumn => $value) {
        $dados[$table][$collumn] = $value;
    }
}
$jsonData = json_encode($dados);

In the variable $tabelas you will define the tables you want

score 0 · Answer 2

I did not understand the column number

"column name": "226",

But you can consult the INFORMATION_SCHEMA with CAST (AS JSON)

SELECT table_name, CAST(
    ( SELECT ISC.COLUMN_NAME
      FROM INFORMATION_SCHEMA.COLUMNS as ISC
      WHERE ISC.table_name = IST.table_name)
 AS JSON) as json_colunas
FROM INFORMATION_SCHEMA.TABLES as IST
  WHERE IST.table_schema = 'db_name'