PHP function to convert a CSV to JSON

I have the following CSV :

siret;nom long;nom_court;type;rue;code_postal;ville;pays
123;Nom long;Nom_court;Entreprise;rue du lac;21789;Marseille;France

And I need to obtain this one:

{

    "1": {

        "siret": "123",

        "nom long": "Nom long",

        "nom_court": "Nom_court",

        "type": "Entreprise",

        "address": {

            "ville": "Marseille",

            "code_postal": "21789",

            "country": "France"

        },

        ...

    },

I made this operation often and to simply my job I defined a generic function.

Initialization

First, we need to define elements like the separator by defining a constant : const SEPARATOR = ';';

We also need to identify columns associate to the JSON file. For example, in our CSV file, siret is at the first position. So we set : $columns[0]="siret";

Then longname is at the second position, so we set  $columns[1]="longname"; and so on.

Thus, at the beginning of our php file, we defined an array and set the correspondance between CSV and JSON :

$ini_array=array();
const SEPARATOR = ';';
// CSV files columns
$columns = array();
$columns[0]="siret";
$columns[1]="longname";
$columns[2]="shortname";
$columns[3]="type";
$columns[4]="rue";
$columns[5]="zipcode";
$columns[6]="city";
$columns[7]="country";

The next step is to identify dependency between elements. By using a simple array we set for example the address dependency :

$jsonHierarchy = array(

    "ville" => "address",

    "code_postal" => "address",

    "country" => "address",

);

In this array, we give all sub element of address in order to obtain this JSON structure :

        "address": {

            "ville": "Marseille",

            "code_postal": "21789",

            "country": "France"

These variables will be used by the convert method.

Read the CSV file

In my case, I used a config file where I stored the path of the CSV file path : PI_import_pathfile="C:\\tmp\file.csv"

I also have severeal environnement so I need to choose between them (Linux or Windows ) :

if (strtoupper(substr(PHP_OS, 0, 3)) === 'WIN') {

    $ini_array = parse_ini_file("C:\\tmp\\config.ini");

} else {

    $ini_array = parse_ini_file("/root/config/config.ini");

}

$file=$ini_array["PI_import_pathfile"];

Call the method  convert_CSV_to_JSON()

The next step is to read the CSV file and convert it in JSON file :

if( file_exists($file)) {

    $csv = file_get_contents($file);

    $json = convert_CSV_to_JSON($csv, $columns, $jsonHierarchy);

    echo json_encode($json, JSON_PRETTY_PRINT);

} else {

    echo "File doesn't exist";

}

Method convert_CSV_to_JSON()

This is the code of the convert CSV to JSON method :

/**
 * Convert CSV content in an JSON array
 * Need to be improve for global use
 * @param String $csv_data Content of csv file
 * @param array $columns Field array of you Excel column
 *     e.g     // Excel column
 *              $columns[0]="uid";
 *              $columns[1]="code";
 * @param array $jsonHierarchy Field hierarchy
 * @return array for json encode
 */
function convert_CSV_to_JSON($csv_data, $columns, $jsonHierarchy){

    $json = array();
    $Data = str_getcsv($csv_data, "\n"); //parse the rows
    foreach($Data as $row_index => $Row) {
        $data = str_getcsv($Row, SEPARATOR);

        // skip the first row, since it's the headers
        if($row_index === 0) {
            continue;
        }
        $addr=array();
        foreach ($data as $column_index => $column_value) {

            // get the key for each entry
            if (isset($columns[$column_index])) {

                $label = $columns[$column_index];
                if (array_key_exists($label, $jsonHierarchy)) {

                    $parent = $jsonHierarchy[$label];
                    $addr[$label]=trim($column_value);
                    $json[$row_index][$parent]=$addr;
                } else {
                    $json[$row_index][$label] = trim($column_value);
                }
            }

        }
    }

    return $json;
}

Full code to convert CSV to JSON

<?php
/**
 * Read a csv file and produce an JSON output in order to import organisations.
 *
 * Note :
 * - The column order is important and set in the convert_CSV_to_JSON§.
 * - csv in UTF8 format.
 *
 * e.g :
 * php convertCSVtoJSON.php > organisationPI.json
 * php public/index.php oscar organizationsjson:sync /root/PI/organisationPI.json
 *
 */
$ini_array=array();
const SEPARATOR = ';';
// Excel columns
$columns = array();
$columns[0]="siret";
$columns[1]="longname";
$columns[2]="shortname";
$columns[3]="type";
$columns[4]="rue";
$columns[5]="zipcode";
$columns[6]="city";
$columns[7]="country";

/*$columns[0]="uid";
$columns[1]="code";
$columns[2]="shortname";
$columns[3]="longname";
$columns[4]="type";
$columns[5]="address";
$columns[6]="city";
$columns[7]="zipcode";
$columns[8]="country";
$columns[9]="phone";
$columns[10]="url";
$columns[11]="email";
$columns[12]="siret";
$jsonHierarchy = array(
    "address1" => "address",
    "city" => "address",
    "zipcode" => "address",
    "country" => "address",
);
//PI_import_pathfile="C:\\tmp\\tmp.csv"
if (strtoupper(substr(PHP_OS, 0, 3)) === 'WIN') {
    $ini_array = parse_ini_file("C:\\tmp\\config.ini");
} else {
    $ini_array = parse_ini_file("/tmp/config.ini");
}
$file=$ini_array["PI_import_pathfile"];
if( file_exists($file)) {
    $csv = file_get_contents($file);
    $json = convert_CSV_to_JSON($csv, $columns, $jsonHierarchy);
    echo json_encode($json, JSON_PRETTY_PRINT);
} else {
    echo "Le fichier d'import CSV n'existe pas !";
}


/**
 * Convert CSV content in an JSON array
 * Need to be improve for global use
 * @param String $csv_data Content of csv file
 * @param array $columns Field array of you Excel column
 *     e.g     // Excel column
 *              $columns[0]="uid";
 *              $columns[1]="code";
 * @param array $jsonHierarchy Field hierarchy
 * @return array for json encode
 */
function convert_CSV_to_JSON($csv_data, $columns, $jsonHierarchy){

    $json = array();
    $Data = str_getcsv($csv_data, "\n"); //parse the rows
    foreach($Data as $row_index => $Row) {
        $data = str_getcsv($Row, SEPARATOR);

        // skip the first row, since it's the headers
        if($row_index === 0) {
            continue;
        }
        $addr=array();
        foreach ($data as $column_index => $column_value) {
            // get the key for each entry
            if (isset($columns[$column_index])) {
                $label = $columns[$column_index];
                if (array_key_exists($label, $jsonHierarchy)) {


                    $parent = $jsonHierarchy[$label];
                    $addr[$label]=trim($column_value);
                    $json[$row_index][$parent]=$addr;
                } else {
                    $json[$row_index][$label] = trim($column_value);
                }
                //exit(1);
                /*switch ($column_index) {
                    case 5:
                        $addr["address1"]=trim($column_value);
                        $json[$row_index]["address"]=$addr;
                        break;
                    case 6:
                        $addr["city"]=trim($column_value);
                        $json[$row_index]["address"]=$addr;
                        break;
                    case 7:
                        $addr["zipcode"]=trim($column_value);
                        $json[$row_index]["address"]=$addr;
                        break;
                    case 8:
                        $addr["country"]=trim($column_value);
                        $json[$row_index]["address"]=$addr;
                        break;
                    default:
                        $json[$row_index][$label] = trim($column_value);
                }*/
            }
        }
    }
    return $json;
}

Version : PHP 7.3.1

Auto-generated menu : a simple idea to save time in your developpement

Most of the time, I need to do small developpement in my job and I need to do it quickly. I have to think how to improve my developpement.For example, recently, I need to add menu items quickly and when you read the code of your web site you've made few month later, it 's sometimes quite difficult. 

That's why I decided to save my menu in database. Now, menu is auto-generated.

Why do I need to save a menu in database ? 

In my work, I made a business web site with cake-php 3.9.X , Postgresql database and Bootstrap.

The goal of this tool is to display reports (generated automatically in pdf with Businness Object).Every report has a specific name.If click on an item of my menu, I need severeal paremeters in order to display the correct report like name, date, enable or disable... 

Here is an example of a  menu :

I've met a problem when I wanted  to add a submenu, I need to write to much codes :

<?php

//
$ssdomain='INFORMATION';
if($selectedSSDomain == $ssdomain) {
$ssdomainLabel = "<span class='navbar-custom'>CONTACT</span>";
} else {
$ssdomainLabel = "CONTACT";
}
echo "<form method='post' action='index.php' id='form_contact'>";
echo "<input type='hidden' name='domain' value='Information'/>";
echo "<input type='hidden' name='ssdomain' value='CONTACT'/>";
echo "<input type='hidden' name='startWithReport' value='key_1'/>";
if (isset($_POST['chooseReportDate'])) {
$tmpDate = $_POST['chooseReportDate'];
echo "<input type='hidden' name='chooseReportDate' value=\"$tmpDate\"/>";
}
if(isset($_POST['select_org']) ) {
$code = $_POST['select_org'];
echo "<input type='hidden' name='select_org' value=\"$code\"/>";
}

echo "<a class='dropdown-item' onclick=\"document.getElementById('form_contact').submit(); return false;\" >$ssdomainLabel</a>";

echo "</form>";

In my business, it happens quite often (every 6 month) and I need to remember all parameters !That's why I decided to save my menu in database. Now, it's really easy to add a new menu !

Table Menu

First, we need to add all your menu information in a table of our database as text, position, enable...

Here is the Menu table :

drop table yuzu.menu;
CREATE TABLE yuzu.menu (
                      id int primary key ,
                      id_parent int,
                      shortkey varchar(50) NOT NULL,
                      name varchar(50) NOT NULL,
                      position int,
                      grey bool
);

This table contains :

• id : menu id
• id_parent : To indicate that the menu is a submenu
• shortkey : Use to identify the choosen menu
• position : We need to order menu.

For each level of menu, we indicated the position. For example, HOME is first (position 1) then INFORMATION is second.

The submenu is also sortered with CONTACT in second position and PROFILE in the first position :

HomeController

In my controller, I retreived the Menu table and stored it in a session.    

Display auto generated menu

I wrote some code to display the menu automatically. To achieved that, I defined one method : function displayMenu($menus){}

Here is the code of two methods :

        <?php


        function getSortedMenu($menus, $currentIdParent) {
            $mainMenus = array();
            // Sort Menu
            foreach ($menus as $arr) {
                $submenus = $arr[0];
                $idParent = $submenus['id_parent'];
                if ($idParent == $currentIdParent) {
                    $position = $submenus['position'];
                    $mainMenus[$position]=$submenus;
                }
            }
            ksort($mainMenus);
            return $mainMenus;
        }

        function displayMenu($menus) {
            $mainMenus = getSortedMenu($menus, 0);
            foreach($mainMenus as $levelOnes) {

                echo "<li class=\"nav-item dropdown\">";
                $shortkey = "dropdown_".$levelOnes['shortkey'];
                echo "<a class=\"nav-link dropdown-toggle text-menu\" id='$shortkey' data-toggle=\"dropdown\" href='#'>".$levelOnes['name']."</a>";
                echo "<div class='dropdown-menu' aria-labelledby='$shortkey'>";

                foreach ($menus as $arr) {

                    $menu = $arr[0];
                    if($menu['id_parent']==$levelOnes['id']) {
                        $shortkey = "form_".$menu['shortkey'];

                        echo "<form method='post' action='/home' id='".$shortkey."'>";
                        $disabled="";
                        if ($menu['grey']) {
                            $disabled='disabled';
                        }
                        echo "<input type='hidden' name='menu_selected_id' value='".$menu['id']."'/>";
                        echo "<a class='dropdown-item $disabled text-menu' href='#'>" . $menu['name'] . "</a>";
                        echo "</form>";
                    }
                }
                echo "</div>";
                echo "</li>";
            }
        }
        ?>

This code used bootstrap and will certainly be improved.I think it's often a good way to auto-generated things. I hope it will give you some idea for your source code. 

Notes :

• We have only two levels because I didn't need a level three of my menu.
• The next step  is to manage authorization ! 

Versions :

CakePHP 3.9
Postgresql 9.6
Bootstrap 4.2.1

PHPStorm Warning : Illegal string offset

 

Here is a comment about "Illegal string offset warning" I had in phpstorm.

I found the solution on StackOverflow : 

The error Illegal string offset 'whatever' in... generally means: you're trying to use a string as a full array.

In my case, I defined the following :

    /**
     * isAuthorized
     * @param string $user user authorized
     */
    public function isAuthorized($user)
    {
        // Autorisations
        // admin peut tout faire
        if ($user['role'] === 'admin') {
            return true;
        }

In fact, I made a mistake. The $user variable was defined in method comment as a string. But, $user is an array ! That's why I had this warning.

To avoid this warning, you have to change the type of the variable in method comment and a good practice is to add an "s" at the end of the variable name in order to identify easily the array variable. 

    /**
     * isAuthorized
     * @param array $users user authorized
     */
    public function isAuthorized($users)
    {
        // Autorisations
        // admin peut tout faire
        if ($users['role'] === 'admin') {
            return true;
        }