How can I return a query by grouping by the table name using Pdo, php and mysql?

Asked

Viewed 56 times

3

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

po example I want to make a query and return a json like this one here, using Pdo, has as?

{
    "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

2 answers

2

Try it this way:

$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 will define the tables you want

0

I didn’t understand the column number

"column name": "226",

But you can make a consulate in INFORMATION_SCHEMA with the 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'

Browser other questions tagged php

You are not signed in. Login or sign up in order to post.