Simon Hume Simon Hume - 7 months ago 18
SQL Question

Inserting records between JSON entries using PHP

I've got a nice simple bit of PHP which grabs data from my database View and makes a JSON file and then writes it to my server.

It's a list of employees from 5 different companies.

What I'd like to be able to do, is before the first employee of each company that gets returned is to insert an item which represents the company (inc a logo).

So, it would look something like this...

COMPANY 1
EMPLOYEE
EMPLOYEE
EMPLOYEE
COMPANY 2
EMPLOYEE
COMPANY 3
EMPLOYEE
EMPLOYEE

At the moment I'm just getting all the employees.

This is my PHP that generates the JSON

<?php
$db=new PDO('mysql:dbname=DB;host=localhost;','username','pass');
$row=$db->prepare('select * from Employee_JSON');

$row->execute();//execute the query
$json_data=array();//create the array
foreach($row as $rec)//foreach loop
{
$json_array['employee_id']=$rec['employee_id'];
$json_array['employee_firstname']=$rec['employee_firstname'];
$json_array['employee_surname']=$rec['employee_surname'];
$json_array['employee_image']=$rec['employee_image'];
$json_array['employee_status']=$rec['employee_status'];
$json_array['company_id']=$rec['company_id'];
$json_array['company_name']=$rec['company_name'];
$json_array['company_hex']=$rec['company_hex'];
//here pushing the values in to an array
array_push($json_data,$json_array);

}

//built in PHP function to encode the data in to JSON format
echo json_encode($json_data);

//write to json file
$fp = fopen('data/employees.json', 'w');
fwrite($fp, json_encode($json_data));
fclose($fp);


?>


Here is my JSON sample returned.

[{
"employee_id": "9",
"employee_firstname": "Test",
"employee_surname": "Name",
"employee_image": "9.jpg",
"employee_status": "1",
"company_id": "1",
"company_name": "CDE",
"company_hex": "99C440"
}, {
"employee_id": "49",
"employee_firstname": "Testy",
"employee_surname": "Test",
"employee_image": "ce_holding.png",
"employee_status": "1",
"company_id": "1",
"company_name": "CDE",
"company_hex": "99C440"
}, {
"employee_id": "8",
"employee_firstname": "Tester",
"employee_surname": "McTest",
"employee_image": "8.jpg",
"employee_status": "1",
"company_id": "1",
"company_name": "CDE",
"company_hex": "99C440"
}]


I have two tables in the database, a Company table that contains the URL of the image I want and an Employee table that has all the employee data.

Here is the Company table structure

enter image description here

Here is the Employee table structure

enter image description here

I then do an Inner Join on those to create my database View which is called in the PHP sample.

CREATE VIEW `Employee_JSON`
AS SELECT
`Employee`.`employee_id` AS `employee_id`,
`Employee`.`employee_firstname` AS `employee_firstname`,
`Employee`.`employee_surname` AS `employee_surname`,
`Employee`.`employee_image` AS `employee_image`,
`Employee`.`employee_status` AS `employee_status`,
`Company`.`company_id` AS `company_id`,
`Company`.`company_name` AS `company_name`,
`Company`.`company_hex` AS `company_hex`
FROM (`Employee` join `Company` on((`Employee`.`employee_company_id` = `Company`.`company_id`))) where (`Employee`.`employee_active` = 1) order by `Company`.`company_name`,`Employee`.`employee_surname`,`Employee`.`employee_firstname`;


So my expected output could look something like this, where adding a "is_company": "1" into the JSON could help my app detect whether to display the company image or employee image.

[{
"employee_id": "0",
"employee_firstname": "Company",
"employee_surname": "Name",
"employee_image": "companyimage.jpg",
"employee_status": "1",
"company_id": "1",
"company_name": "CDE",
"is_company": "1",
"company_hex": "99C440",

}, {
"employee_id": "9",
"employee_firstname": "Test",
"employee_surname": "Name",
"employee_image": "9.jpg",
"employee_status": "1",
"company_id": "1",
"company_name": "CDE",
"company_hex": "99C440"
}, {
"employee_id": "49",
"employee_firstname": "Testy",
"employee_surname": "Test",
"employee_image": "ce_holding.png",
"employee_status": "1",
"company_id": "1",
"company_name": "CDE",
"company_hex": "99C440"
}, {
"employee_id": "8",
"employee_firstname": "Tester",
"employee_surname": "McTest",
"employee_image": "8.jpg",
"employee_status": "1",
"company_id": "1",
"company_name": "CDE",
"company_hex": "99C440"
}]


I'm then reading this JSON file into an iOS app to display the contents.

Any ideas how I might be able to achieve adding in those extra nodes?

Many thanks

Simon

Answer

Something like this might do the trick:

<?php    
//Before this you should have the data you want to insert in the JSON of each company. I guess you should get it from a database.
$json_data_c=array();
foreach($row as $rec_company){ //I supose you have the information in the $row variable after a query to the database
    $json_array['company_field1']=$rec_company['company_field1'];
    ....
    $json_data_c[$rec_company['company_id']]=$json_array;
}

//end of company information

$db=new PDO('mysql:dbname=DB;host=localhost;','username','pass');    
$row=$db->prepare('select * from Employee_JSON');  

$row->execute();//execute the query  
$json_data=array();//create the array  
$company_id_loop=-1;
foreach($row as $rec)//foreach loop  
{  
    if($company_id_loop!=$rec['company_id']){
        //insert here the json with the data of the company $rec['company_id']
        $json_array_company=$json_data_c[$rec['company_id']];
        array_push($json_data, $json_array_company); 
        $company_id_loop=$rec['company_id'];
    }
$json_array['employee_id']=$rec['employee_id'];  
    $json_array['employee_firstname']=$rec['employee_firstname'];  
    $json_array['employee_surname']=$rec['employee_surname'];  
    $json_array['employee_image']=$rec['employee_image'];
    $json_array['employee_status']=$rec['employee_status'];
    $json_array['company_id']=$rec['company_id']; 
    $json_array['company_name']=$rec['company_name']; 
    $json_array['company_hex']=$rec['company_hex'];  
//here pushing the values in to an array  
    array_push($json_data,$json_array);  

}  

//built in PHP function to encode the data in to JSON format  
echo json_encode($json_data);  

//write to json file
$fp = fopen('data/employees.json', 'w');
fwrite($fp, json_encode($json_data));
fclose($fp);


?>