How to structure a mysql database from a JSON

Question

I have the following structure json test:

{
  "CE": {
    "Fortaleza": [
      {
        "nome": "Teste",
        "email": "[email protected]",
        "cel": "(xx) xx-xx"
      },
      {
        "nome": "Teste",
        "email": "[email protected]",
        "cel": "(xx) xx-xx"
      }
    ],
    "Abaiara": [
      {
        "nome": "Cidade Abaiara 1",
        "email": "[email protected]",
        "cel": "(xx) xx-xx"
      },
      {
        "nome": "Cidade Abaiara 2",
        "email": "[email protected]",
        "cel": "(xx) xx-xx"
      }
    ]
  },
  "rn": {
    "pindaiba": [
      {
        "nome": "rtyretyerty",
        "email": "[email protected]",
        "cel": "(xx) xx-xx"
      },
      {
        "nome": "zzzzzz",
        "email": "[email protected]",
        "cel": "(xx) xx-xx"
      }
    ],
    "sorisal": [
      {
        "nome": "sor",
        "email": "[email protected]",
        "cel": "(xx) xx-xx"
      },
      {
        "nome": "sorsor",
        "email": "[email protected]",
        "cel": "(xx) xx-xx"
      }
    ]
  }
}

I created a database and I’m having doubts about how to create my table structures from the json above, but no data is required.

[PERGUNTA] The question is about programming, because I can’t write a code SQL set up this structure.

Goal: Create a echo json_encode($stm->fetchAll(PDO::FETCH_ASSOC)); building the same structure json above.

What I already have:

• Connection to the bank:

-

function Conectar(){
        try{
            $opcoes = array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES UTF8');
            $con = new PDO("mysql:host=localhost; dbname=teste;", "root", "", $opcoes);
            return $con;
        } catch (Exception $e){
            echo 'Erro: '.$e->getMessage();
            return null;
        }
    }

• Search of all criminal records:

-

function getFilterCidade($estado){
        $pdo = Conectar();
        $sql = 'SELECT * FROM locais WHERE uf = ?';
        $stm = $pdo->prepare($sql);
        $stm->bindValue(1, $estado);
        $stm->execute();
        sleep(1);
        echo json_encode($stm->fetchAll(PDO::FETCH_ASSOC));
        $pdo = null;        
    }

- Name of json structure for **columns creation:**

Realize that a state poderá ter várias cidades and a city poderá ter vários usuários with: nome , email e celular. In this example structure we have 2 states with 2 cities and each city with 2 users.

Answer 1

Interesting question, because it goes against the hand of most questions, wanting to generate a json or instead of just reading.

Well come on:

Tables:

I will only create a table with columns (id, Estado, Cidade, Nome, Email, Cel), I believe that you know how to do, this is not the problem of the question. You can choose two to avoid duplications, but I do not believe that it is necessary to the question!

Solution:

Initially there will be this in the table:

0, CE, Fortaleza, Teste, [email protected], (xx) xx-xx
1, CE, Fortaleza, Teste, [email protected], (xx) xx-xx

What you really want is to group based on CE and Fortaleza, in this case you will have to use the while.

<?php

$rAux = $mysqli->query("SELECT * FROM tabela ORDER BY Estado ASC, Cidade ASC");
// Pega todos os registros do banco de dados.

while($r = $rAux->fetch_array()){
// Criado um loop finito para cada registro

$nomeCidade = $r['Cidade'];
$nomeEstado = $r['Estado'];

$dados = array('nome' => $r['Nome'], 'email' => $r['Email'], 'cel' => $r['Cel']);
// Esta é a array que deseja que seja retornada


$jsonAux[ $nomeEstado ][ $nomeCidade ][] = $dados;
// CE -> FORTALEZA -> [Nome, Email Tel], [Nome, Email, Tel]
// O [] no final é para adicionar um novo. Assim como $array[] = 'manteiga'.


}
// Fim do loop

$json = json_encode($jsonAux);
echo $json;

I tried to make the code as self-explanatory as possible so you can understand step by step. PHP will create the path CE -> FORTALEZA the first time and then will add the array to it, since it already exists.

Upshot:

Mysql:

--
-- Criar tabela
--
    CREATE TABLE `tabela` (
  `id` int(11) NOT NULL,
  `Estado` varchar(255) NOT NULL,
  `Nome` varchar(255) NOT NULL DEFAULT 'Inkeliz',
  `Cidade` varchar(255) NOT NULL,
  `Email` varchar(255) NOT NULL,
  `Cel` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dados utilizados para teste
--

INSERT INTO `tabela` (`id`, `Estado`, `Nome`, `Cidade`, `Email`, `Cel`) VALUES
(1, 'CE', 'Inkeliz', 'Fortaleza', '[email protected]', '[phone]'),
(2, 'CE', 'Inkeliz', 'Fortaleza', '[email protected]', '[phone]'),
(3, 'CE', 'Inkeliz', 'Aquiraz', '[email protected]', '[phone]'),
(4, 'RN', 'Inkeliz', 'Pindaiba', '[email protected]', '[phone]');

--
-- Index
--
ALTER TABLE `tabela`ADD PRIMARY KEY (`id`);

JSON:

{"CE":{"Aquiraz":[{"nome":"Inkeliz","email":"[email protected]","cel":"[phone]"}],"Fortaleza":[{"nome":"Inkeliz","email":"[email protected]","cel":"[phone]"},{"nome":"Inkeliz","email":"[email protected]","cel":"[phone]"}]},"RN":{"Pindaiba":[{"nome":"Inkeliz","email":"[email protected]","cel":"[phone]"}]}}