Read a txt file by php and mysql and viewed by each user

Question

I am a beginner programmer in PHP, I accepted a challenge of a job and right now I am in need of a help. They send me a month-round expense report of each user in a single file (a list), in txt format. I need to convert the lines separately from each user, so that he can view his expenses summed up in a total. I would like some command in PHP, linked with a Mysql database, so that it can view your expenses.

Obs.: The file received in txt, is a list of all users. Below a part of the received file so they can understand.

000001|06|2015|001|MONTHLY CHARGE JUNE 2015 |000000003000|D 000001|06|2015|002|CLUB CRUZEIRO DO SUL |000000004500|D 000005|06|2015|001|DEVOLUCAO DE CREDITO PARA SOCIO |00007500|C

Separated by pipe are first the user id, month and then the year, order, entity and (taking out zeros) value spent on each entity.

Answer 1

I made a simple script to get the data of the text file in the format you sent, follows:

<?php 

$handle = fopen("dados.txt", "r");
if ($handle) {
    $total = 0;
    while (($line = fgets($handle)) !== false) {

        $data = explode("|",$line);

        echo "ID: ".$data[0]."</br>";
        echo "Mês: ".$data[1]."</br>";
        echo "Ano: ".$data[2]."</br>";
        echo "Ordem: ".$data[3]."</br>";
        echo "Entidade: ".$data[4]."</br>";
        echo "Valor: R$".number_format($data[5],2)."</br>";
        echo "Cod: ".$data[6]."</br>";
        $total+=$data[5];       
    }

    echo '</br>Total: R$' . number_format($total, 2);

    fclose($handle);
} else {
} 

?>

Just so you have an idea, but you can save in the database too.

Answer 2

Here’s a way to do it:

function readMyFile($file) {
    $data = file_get_contents($file);
    $collection = explode("\n",$data);
    $collection = array_filter($collection);

    $keys = array(
                 'id_usuario',
                 'mes',
                 'ano',
                 'ordem',
                 'entidade',
                 'valor_gasto',
                 'categoria'
                 );
    $return = array();
    if (count($collection)) {
       foreach ($collection as $linha => $collection_pipes) {

       $resultLine = explode('|',$collection_pipes);

      $resultMap[$linha] = array_map(function($a, $b) {
                                        if ($a == 'valor_gasto') {
                                             //remove os zeros
                                            $b = (int) $b; 
                                        }
                                     return array($a => trim($b));
                                    },$keys, $resultLine);
          /* se for remover o índice 0,
             coloque o trecho do código abaixo aqui */
      }
   }

return $resultMap;
}

$saida = readMyFile('seuarquivo.txt');

//sua saída
   echo '<pre>';
    print_r($saida);

Whereas your file breaks lines, the output will be:

Array
(
    [0] => Array
        (
            [0] => Array
                (
                    [id_usuario] => 000001
                )

            [1] => Array
                (
                    [mes] => 06
                )

            [2] => Array
                (
                    [ano] => 2015
                )

            [3] => Array
                (
                    [ordem] => 001
                )

            [4] => Array
                (
                    [entidade] => MENSALIDADE JUNHO DE 2015
                )

            [5] => Array
                (
                    [valor_gasto] => 3000
                )

            [6] => Array
                (
                    [categoria] => D
                )

        )

    [1] => Array
        (
            [0] => Array
                (
                    [id_usuario] => 000001
                )

            [1] => Array
                (
                    [mes] => 06
                )

            [2] => Array
                (
                    [ano] => 2015
                )

            [3] => Array
                (
                    [ordem] => 002
                )

            [4] => Array
                (
                    [entidade] => CLUBE CRUZEIRO DO SUL
                )

            [5] => Array
                (
                    [valor_gasto] => 4500
                )

            [6] => Array
                (
                    [categoria] => D
                )

        )

    [2] => Array
        (
            [0] => Array
                (
                    [id_usuario] => 000005
                )

            [1] => Array
                (
                    [mes] => 06
                )

            [2] => Array
                (
                    [ano] => 2015
                )

            [3] => Array
                (
                    [ordem] => 001
                )

            [4] => Array
                (
                    [entidade] => DEVOLUCAO DE CREDITO PARA SOCIO
                )

            [5] => Array
                (
                    [valor_gasto] => 7500
                )

            [6] => Array
                (
                    [categoria] => C
                )

        )

)

To remove the duplicate numeric index, just put this snippet of code inside the foreach:

   $resultMap[$linha] =  array('id_usuario'=>$resultMap[$linha][0]['id_usuario'],
                                'mes'=>$resultMap[$linha][1]['mes'],
                                'ano'=>$resultMap[$linha][2]['ano'],
                                'ordem'=>$resultMap[$linha][3]['ordem'],
                                'entidade'=>$resultMap[$linha][4]['entidade'],
                                'valor_gasto'=>$resultMap[$linha][5]['valor_gasto'],
                                'categoria'=>$resultMap[$linha][6]['categoria']);  

Or if you prefer, put this after the foreach:

$resultMap =  array_map(function($arr) {
                 return array_merge($arr[0],$arr[1],$arr[2],$arr[3],$arr[4],$arr[5],$arr[6]);
}, $resultMap);
return $resultMap;

The other way to do it is this:

function readMyFile($file) {

$content = fopen($file, "r");
if ($content) {
    $total = 0;
   $collection = array();
    while (($line = fgets($content)) !== false) {

        $data = explode("|",$line);
        $collection[$total] = array(
                 'id_usuario' => $data[0],
                 'mes'        => $data[1],
                 'ano'        => $data[2],
                 'ordem'      => $data[3],
                 'entidade'   => $data[4],
                 'valor_gasto'=>(int) $data[5],
                 'categoria'  => $data[6]
                 );

        $total++;       
    }
     return $collection;
    fclose($content);
} 
$collection = readMyFile('seuarquivo.txt');
 echo '<pre>';
  print_r($collection);

Answer 3

Can save each column in the database make one explode() by pipe, if not too many lines can use file() to open the file as an array.

<?php

$linhas = file('gastos');//abre o arquivo como um array 
$sql = "INSERT INTO tabela (c1, c2, c3, c4, c5, c6, c7) values ";

//define o padrão do insert
$pls = '('. str_repeat('?,', 7) .'),';
$sql .= trim(str_repeat($pls, count($linhas)), ',');

//monta o array com os valores a serem inseridos
$valores = array();
foreach($linhas as $item){
    $valores = array_merge($valores, explode('|', $item));
}



$db = new PDO('mysql:host=localhost;dbname=test', 'usuario', 'senha');
$stmt = $db->prepare($sql);
if(!$stmt->execute($valores)){
    echo '<pre>';
    print_r($stmt->errorInfo());
}