I have this query result from Sql server db:
and I need to get this json output from PHP:
{"Clientes":
[{
"CodCliente": 1,
"NombreCliente": "Garcia",
"Direcciones":[
{
"Direccion": "Av Uriburu 2569",
"Telefono": "4558899"
},
{
"Direccion": "Pte Roca 1527",
"Telefono": "4887541"
}
]
},
{
"CodCliente": 2,
"NombreCliente": "Gonzales",
"Direcciones":[
{
"Direccion": "Lamadrid 475",
"Telefono": "4897425"
}
]
},
{
"CodCliente": 3,
"NombreCliente": "Ferreyra",
"Direcciones":[
{
"Direccion": "Eva Peron 253",
"Telefono": "4689553"
},
{
"Direccion": "Laprida 658",
"Telefono": "4658963"
}
]
}
]
}
I think I should have Clientes class and Direcciones Class but I can't assocc the query result to get the json :
class Cliente
{
public $CodCliente;
public $NombreCliente;
public $Direcciones;
public function __construct(){}
}
class Direccion
{
public $CodCliente;
public $Direccion;
public $Telefono;
public function __construct(){}
}
This is how I get the query and I'm not sure if I'm doing correctly:
$pdo = Database::connect();
$sentencia = $pdo->prepare($comando);
$sentencia->execute();
if ($sentencia) {
$resultado = $sentencia->fetchAll(PDO::FETCH_CLASS, "Cliente");
return $resultado;
}
Well, you have to process data from database manually and create new array, which would be formatted appropriately. May be something like this?
...
$result = [];
$data = $sentencia->fetchAll(PDO::FETCH_ASSOC, "Cliente");
foreach ($data as $item) {
// Create client if not exists
$id = $item['CodCliente'];
if (!isset($result[$id]) {
$result[$id] = [
'CodCliente' => $id,
'NombreCliente' => $item['NombreCliente'],
// Create empty array
'Direcciones' => [],
];
}
// Add new contact
$result[$id]['Direcciones'][] = [
'Direccion' => $item['Direccion'],
'Telefono' => $item['Telefono'],
];
}
// Clear reference ids and get continuous numeric array
$result = array_values($result);
// Final encapsulation
$result = ['Clientes' => $result];
return json_encode($result, JSON_PRETTY_PRINT);
The code might be improved in many ways depending on which version of PHP and/or libraries you are using, but that's another story.
When you return the data just encode it with json E.g echo json_encode($data); which we genrally use for apis
$data = '[{
"CodCliente": "1",
"NombreCliente": "Garcia",
"Direccion": "Av Uriburu 2569",
"Telefono": "4558899"
}, {
"CodCliente": "1",
"NombreCliente": "Garcia",
"Direccion": "Pte Roca 1527",
"Telefono": "4887541"
}, {
"CodCliente": "2",
"NombreCliente": "Gonzales",
"Direccion": "Lamadrid 475",
"Telefono": "4897425"
}, {
"CodCliente": "3",
"NombreCliente": "Ferreyra",
"Direccion": "Eva Peron 253",
"Telefono": "4689553"
}, {
"CodCliente": "3",
"NombreCliente": "Ferreyra",
"Direccion": "Laprida 658",
"Telefono": "4658963"
}]';
$array = [];
$decodedData = json_decode($data, true);
foreach ($decodedData as $key) {
$tempArray[$key['CodCliente']]['CodCliente'] = $key['CodCliente'];
$tempArray[$key['CodCliente']]['NombreCliente'] = $key['NombreCliente'];
$tempDirec['Direccion'] = $key['Direccion'];
$tempDirec['Telefono'] = $key['Telefono'];
$tempArray[$key['CodCliente']]['Direcciones'][] = $tempDirec ;
$array[$key['CodCliente']] = $tempArray[$key['CodCliente']];
}
$result = json_encode(array_values($array));
echo "<pre>";
echo print_r($result);
echo "</pre>";die;
Explanation: You need to have only one object for CodCliente type, so we need to group the remaining stuff, so what I did was
SQL databases are relational meaning that the result set is per row and that practically means (especially as the number of joins rises) that you can have tenths, hundreds maybe sometimes thousands rows for something that in an object representation can be an object with some nested object/s. From the other hand an object especially if it has nested objects is more like a tree representation. In you case the SQL result probably comes from a join between a table that holds personal details and an other that holds the address details. What are you looking for is called Relational-To-Object-Mapping and there are plenty of material to study, just take a look here for start https://scholar.google.gr/scholar?q=mapping+relational+to+object&hl=el&as_sdt=0&as_vis=1&oi=scholart&sa=X&ved=0ahUKEwjboszIwtDXAhVsDZoKHbS8AMAQgQMIJDAA
Now i can try to give you an answer based on my experience on developing algorithms like that. Some key points befaore we start: 1.pdo supports mapping on an object (like you have already written) but the result is always one level object no nested objects are presented, therefore it cannot work for you.
2.you have to create your class by keeping in mind the table relations so if you have a 1 to many relation between the personal details table and the addresses table this relation has to be somehow in your class. A simple approach could be the Cliente class to have an array that holds objects of class Direccion something like that you have already done as far as i can see.
3.You need an algorithm that can do the mapping. Since the result from the pdo using the $sentencia->fetchAll(PDO::FETCH_ASSOC); will be an associative array the algorithm needs to know when to map a class property and when an object.
Following is an approach that you can use as is, please note that this code likely needs more optimization and for large results thousand rows the performance degrades. Use at your own risk...
1.Input data if you change to $resultado = $sentencia->fetchAll(PDO::FETCH_ASSOC); should be an array like that
$resultado = array(
array("CodCliente"=>1,"NombreCliente"=>"Garcia","Direccion"=>"Sv Uriburu 2569","Telefono"=>"4558899"),
array("CodCliente"=>1,"NombreCliente"=>"Garcia","Direccion"=>"Pte Roce 1527","Telefono"=>"4887541"),
array("CodCliente"=>2,"NombreCliente"=>"Gonzales","Direccion"=>"Lambadrid 475","Telefono"=>"4897425"),
array("CodCliente"=>3,"NombreCliente"=>"Ferreyra","Direccion"=>"Eva Peron 253","Telefono"=>"4689553"),
array("CodCliente"=>3,"NombreCliente"=>"Ferreyra","Direccion"=>"Laprida 658","Telefono"=>"4658963")
);
2.You need to change your classes as follows
class Cliente{
public $CodCliente;
public $NombreCliente;
public $Direcciones;
public function getDirecciones(){
if(!isset($this->Direcciones)){
return array(new Direccion());
}
return $this->Direcciones;
}
public function setDirecciones($Direcciones){
$this->Direcciones = $Direcciones;
}
public function getCodCliente(){
if(!isset($this->CodCliente)){
return 0;
}
return intval($this->CodCliente); //ensure for the data type
}
public function setCodCliente($CodCliente){
$this->CodCliente = $CodCliente;
}
public function getNombreCliente(){
if(!isset($this->NombreCliente)){
return "";
}
return $this->NombreCliente;
}
public function setNombreCliente($NombreCliente){
$this->NombreCliente = $NombreCliente;
}
public function __construct(){}
}
class Direccion{
public $Direccion;
public $Telefono;
public function getDireccion(){
if(!isset($this->Direccion)){
return 0;
}
return $this->Direccion;
}
public function setDireccion($Direccion){
$this->Direccion = $Direccion;
}
public function getTelefono(){
if(!isset($this->Telefono)){
return "";
}
return $this->Telefono;
}
public function setTelefono($Telefono){
$this->Telefono = $Telefono;
}
public function __construct(){}
}
3.Add this
$mapper = new rbe_mapper();
$result = array();
for($i=0;$i<count($resultado);$i++){
$client = new Cliente();
$client = $mapper->mapArrayToObjectByProperties($resultado[$i],$client); //each iteration map a singe row from the result
$index = getItemIndexForValueByMethod($result,$client->getCodCliente(),"getCodCliente");
if($index===false){
array_push($result,$client);
}else{
$direction = $client->getDirecciones();
$directions = $result[$index]->getDirecciones();
array_push($directions,$direction[0]);
$result[$index]->setDirecciones($directions);
}
}
4.And this
function getItemIndexForValueByMethod($array,$value,$method){
if(!is_array($array)){
return false;
}
if(empty($array)){
return false;
}
for($i=0;$i<count($array);$i++){
if($array[$i]->$method()==$value){
return $i;
}
}
return false;
}
class rbe_mapper{
public function mapArrayToObjectByProperties($row,$object){
$class = get_class($object);
$properties = get_class_vars($class);
$keys = array_keys($properties);
$plength = count($keys);
for($i=0;$i<$plength;$i++){
$property = ucfirst($keys[$i]);
$setter = "set".$property;
$getter = "get".$property;
if(method_exists($class,$setter) && method_exists($class,$getter)){
if(is_object($object->$getter())){
$object->$setter($this->mapArrayToObjectByProperties($row,$object->$getter())); //for nested objects
}elseif(is_array($object->$getter())){
$ar = $object->$getter();
$ar[0] = $this->mapArrayToObjectByProperties($row,new $ar[0]()); //for arrays
$object->$setter($ar);
}else{//value setting fonr non of the avove data types
$value = false;
if(isset($row[$property])){
$value = $row[$property];
}
if($value!==false){
$object->$setter($value);
}
}
}
}
return $object;
}
}
final code should be
//class definition here sample 2,4
$pdo = Database::connect();
$sentencia = $pdo->prepare($comando);
$sentencia->execute();
if ($sentencia) {
$resultado = $sentencia->fetchAll(PDO::FETCH_ASSOC);
//add here sample 3
return json_encode($result);
}
and the result in json is
[{
"CodCliente": 1,
"NombreCliente": "Garcia",
"Direcciones": [{
"Direccion": "Sv Uriburu 2569",
"Telefono": "4558899"
}, {
"Direccion": "Pte Roce 1527",
"Telefono": "4887541"
}
]
}, {
"CodCliente": 2,
"NombreCliente": "Gonzales",
"Direcciones": [{
"Direccion": "Lambadrid 475",
"Telefono": "4897425"
}
]
}, {
"CodCliente": 3,
"NombreCliente": "Ferreyra",
"Direcciones": [{
"Direccion": "Eva Peron 253",
"Telefono": "4689553"
}, {
"Direccion": "Laprida 658",
"Telefono": "4658963"
}
]
}
]
Note that getItemIndexForValueByMethod function and rbe_mapper class with mapArrayToObjectByProperties method can generaly work for most of the situations like this, you can try with other tables and joins. As long as you create class with setter/getter methods and nested objects (example for nested object can be easily done) or arrays are in the top level class this code can work. If you need more clarifications or exmaples just let me know.
Explanation The big idea behind all this is to have an abstract method mapArrayToObjectByProperties that will map a nested array to object (pdo fetch result) and the guide to map is the object it self that's why your class needed revision. You still have to iterate through every row of the array that the pdo will return, and that's the reason why i'm saying that you can probably have performance degradation. In general the mapArrayToObjectByProperties method, maps a single row from the array, the result is an object. Since every result of mapArrayToObjectByProperties method is an object you need to check if the object is already in the array or an addition to an already object (extra address, email etc) this check is performed by getItemIndexForValueByMethod.
I have do some additions to your exmample in order to demonstrate how easily you can expand this theory. In my example every user can have multiple emails and a car. That means that class Cliente has an extra array of email Objects and a nested object named car.
$input = array(
array("CodCliente"=>1,"NombreCliente"=>"Garcia","Direccion"=>"Sv Uriburu 2569","Telefono"=>"4558899","Email"=>"test@gmail.com","Brand"=>"X brand","Model"=>"Y Model"),
array("CodCliente"=>1,"NombreCliente"=>"Garcia","Direccion"=>"Pte Roce 1527","Telefono"=>"4887541","Email"=>"test@gmail.com","Brand"=>"X brand","Model"=>"Y Model"),
array("CodCliente"=>1,"NombreCliente"=>"Garcia","Direccion"=>"Sv Uriburu 2569","Telefono"=>"4558899","Email"=>"test_@gmail.com","Brand"=>"X brand","Model"=>"Y Model"),
array("CodCliente"=>1,"NombreCliente"=>"Garcia","Direccion"=>"Pte Roce 1527","Telefono"=>"4887541","Email"=>"test_@gmail.com","Brand"=>"X brand","Model"=>"Y Model"),
array("CodCliente"=>1,"NombreCliente"=>"Garcia","Direccion"=>"Sv Uriburu 2569","Telefono"=>"4558899","Email"=>"test@gmail.com","Brand"=>"X brand","Model"=>"Y Model"),
array("CodCliente"=>1,"NombreCliente"=>"Garcia","Direccion"=>"Pte Roce 1527","Telefono"=>"4887541","Email"=>"test@gmail.com","Brand"=>"X brand","Model"=>"Y Model"),
array("CodCliente"=>1,"NombreCliente"=>"Garcia","Direccion"=>"Sv Uriburu 2569","Telefono"=>"4558899","Email"=>"test_@gmail.com","Brand"=>"X brand","Model"=>"Y Model"),
array("CodCliente"=>1,"NombreCliente"=>"Garcia","Direccion"=>"Pte Roce 1527","Telefono"=>"4887541","Email"=>"test_@gmail.com","Brand"=>"X brand","Model"=>"Y Model"),
array("CodCliente"=>2,"NombreCliente"=>"Gonzales","Direccion"=>"Lambadrid 475","Telefono"=>"4897425","Email"=>"test2@gmail.com","Brand"=>"","Model"=>""),
array("CodCliente"=>3,"NombreCliente"=>"Ferreyra","Direccion"=>"Eva Peron 253","Telefono"=>"4689553","Email"=>"test3@gmail.com","Brand"=>"","Model"=>""),
array("CodCliente"=>3,"NombreCliente"=>"Ferreyra","Direccion"=>"Laprida 658","Telefono"=>"4658963","Email"=>"test3@gmail.com","Brand"=>"","Model"=>"")
);
class Cliente{
public $CodCliente;
public $NombreCliente;
public $Direcciones;
public $Emails;
public $Car;
public function getCar(){
if(!isset($this->Car)){
return new Car();
}
return $this->Car;
}
public function setCar($Car){
$this->Car = $Car;
}
public function getEmails(){
if(!isset($this->Emails)){
return array(new Email());
}
return $this->Emails;
}
public function setEmails($Emails){
$this->Emails = $Emails;
}
public function getDirecciones(){
if(!isset($this->Direcciones)){
return array(new Direccion());
}
return $this->Direcciones;
}
public function setDirecciones($Direcciones){
$this->Direcciones = $Direcciones;
}
public function getCodCliente(){
if(!isset($this->CodCliente)){
return 0;
}
return intval($this->CodCliente); //ensure for the data type
}
public function setCodCliente($CodCliente){
$this->CodCliente = $CodCliente;
}
public function getNombreCliente(){
if(!isset($this->NombreCliente)){
return "";
}
return $this->NombreCliente;
}
public function setNombreCliente($NombreCliente){
$this->NombreCliente = $NombreCliente;
}
public function __construct(){}
}
class Direccion{
public $Direccion;
public $Telefono;
public function getDireccion(){
if(!isset($this->Direccion)){
return 0;
}
return $this->Direccion;
}
public function setDireccion($Direccion){
$this->Direccion = $Direccion;
}
public function getTelefono(){
if(!isset($this->Telefono)){
return "";
}
return $this->Telefono;
}
public function setTelefono($Telefono){
$this->Telefono = $Telefono;
}
public function __construct(){}
}
class Email{
public $Email;
public function getEmail(){
if(!isset($this->Email)){
return "";
}
return $this->Email;
}
public function setEmail($Email){
$this->Email = $Email;
}
public function __construct(){}
}
class Car{
public $Brand;
public $Model;
public function getBrand(){
if(!isset($this->Brand)){
return "";
}
return $this->Brand;
}
public function setBrand($Brand){
$this->Brand = $Brand;
}
public function getModel(){
if(!isset($this->Model)){
return "";
}
return $this->Model;
}
public function setModel($Model){
$this->Model = $Model;
}
public function __construct(){
}
}
$mapper = new rbe_mapper();
$result = array();
for($i=0;$i<count($input);$i++){
$client = new Cliente();
$client = $mapper->mapArrayToObjectByProperties($input[$i],$client); //each iteration map a singe row from the result
$index = getItemIndexForValueByMethod($result,$client->getCodCliente(),"getCodCliente");
if($index===false){
array_push($result,$client);
}else{
$direction = $client->getDirecciones();
$directions = $result[$index]->getDirecciones();
$_index = getItemIndexForValueByMethod($directions,$direction[0]->getDireccion(),"getDireccion");
if($_index===false){
array_push($directions,$direction[0]);
$result[$index]->setDirecciones($directions);
}
$email = $client->getEmails();
$emails = $result[$index]->getEmails();
$_index = getItemIndexForValueByMethod($emails,$email[0]->getEmail(),"getEmail");
if($_index===false){
array_push($emails,$email[0]);
$result[$index]->setEmails($emails);
}
}
}
echo json_encode($result);