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've created a database and I'm not sure how to create my table structures from json above, but no data is needed.
[PERGUNTA]The question is about programming, because I can not write aSQLcode that mounts this structure.Objective : Create a
echo json_encode($stm->fetchAll(PDO::FETCH_ASSOC));that assembles the same structurejsonabove.
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 for all entries:
-
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;
}
- json structure name for the creation of the columns: **
Note that a state poderá ter várias cidades is a city poderá ter vários usuários com: nome , email e celular. In this example structure we have 2 states with 2 cities and each city with 2 users.
