Help with PHP/SQL needed

[washo4evr]

Hello,

I'm working on a project that will let users access a website to view results.

I getting via a FTP server CSV files.

 

Files look like that (in French):

"ARTICLES;

BOISSONS 5.50%      ;

      1; ORANGINA            ;        2.50;

      2; PERRIER            ;        5.00;

      2; SCHWEPPES          ;        5.00;

----------------------------------------;

      5; BOISSONS 5.50%      ;      12.50;                            RATIO %;  6.94;

BOISSONS 19.60%    ;

      4; PELFORTH            ;      10.00;

      1; CHIVAS              ;        7.00;

      1; LIQUEUR            ;        5.50;

----------------------------------------;

      6; BOISSONS 19.60%    ;      22.50;                            RATIO %;  12.50;

RESTAURANT          ;

      1; BOUILLON POT AU FEU ;        7.00;

      2; JAMBON D'AUVERGNE  ;      20.00;

      2; TERRINE PIED DE PORC;      16.00;

      1; CASSOULET          ;      20.00;

      2; CONFIT DE CANARD    ;      46.00;

      1; CUISSE DE LAPIN    ;      18.00;

      1; MOURTAYROL          ;      18.00;

----------------------------------------;

    10; RESTAURANT          ;      145.00;                            RATIO %;  80.56;

----------------------------------------;

TOTAL    ; ARTICLES  ;          180.00;

 

 

------------------------------------------;

------------------------------------------;

------------------------------------------;

REGLEMENTS;

      2; ESPECES            ;        109.50;

      1; CARTE              ;        70.50;

------------------------------------------;

TOTAL    ; REGLEMENTS;          180.00;"

 

Basically, I'm trying to write code to "sort" each categorie (ARTICLES, REGLEMENTS....)

 

Here is what I'd like to get:

 

      A                B                            C          D                                          E

ARTICLES;

BOISSONS 5.50%      ;

1; ORANGINA            ;

2; PERRIER            ;

2; SCHWEPPES          ;

BOISSONS 19.60%    ;

4; PELFORTH            ;

1; CHIVAS              ;

1; LIQUEUR            ;

RESTAURANT          ;

1; BOUILLON POT AU FEU ;

2; JAMBON DE DINDE    ;

2; TERRINE PIED DE PORC;

1; CASSOULET          ;

2; CONFIT DE CANARD    ;

1; CUISSE DE LAPIN    ;

1; MOURTAYROL          ;

TOTAL        ;

5; BOISSONS 5.50%      ;      12.50;

6; BOISSONS 19.60%    ;      22.50;

10; RESTAURANT          ;      145.00;

REGLEMENTS;

2; ESPECES            ;        109.50;

1; CARTE              ;        70.50;

A, B, D only names

C, E only numbers

 

These datas will be used to generate pie charts

 

Thanks for your attention

[creata.physics]

What is the code you are using to try to accomplish this?

[xyph]

That's a terribly formed CSV file.

 

Here's a quick snippet that will turn that into an array.

 

Hope this helps you start out. Let me know if you don't understand it.

 

<?php

$csv = "ARTICLES;
BOISSONS 5.50%      ;
      1; ORANGINA            ;        2.50;
      2; PERRIER             ;        5.00;
      2; SCHWEPPES           ;        5.00;
----------------------------------------;
      5; BOISSONS 5.50%      ;       12.50;                            RATIO %;   6.94;
BOISSONS 19.60%     ;
      4; PELFORTH            ;       10.00;
      1; CHIVAS              ;        7.00;
      1; LIQUEUR             ;        5.50;
----------------------------------------;
      6; BOISSONS 19.60%     ;       22.50;                            RATIO %;  12.50;
RESTAURANT          ;
      1; BOUILLON POT AU FEU ;        7.00;
      2; JAMBON D'AUVERGNE   ;       20.00;
      2; TERRINE PIED DE PORC;       16.00;
      1; CASSOULET           ;       20.00;
      2; CONFIT DE CANARD    ;       46.00;
      1; CUISSE DE LAPIN     ;       18.00;
      1; MOURTAYROL          ;       18.00;
----------------------------------------;
     10; RESTAURANT          ;      145.00;                            RATIO %;  80.56;
----------------------------------------;
TOTAL     ; ARTICLES  ;          180.00;


------------------------------------------;
------------------------------------------;
------------------------------------------;
REGLEMENTS;
      2; ESPECES             ;        109.50;
      1; CARTE               ;         70.50;
------------------------------------------;
TOTAL     ; REGLEMENTS;          180.00;";

// Split string by linebreak
$data = explode( "\n", $csv );
// Loop through each line
foreach( $data as $key => $csv_string ) {
// Split line by CSV, explode could also be used here
$raw = str_getcsv($csv_string,';');
// Loop through each CSV cell to trim whitespace
foreach( $raw as &$csv_cell )
	// Use a reference to apply change to source array
	$csv_cell = trim($csv_cell);
// Clear reference to prevent future issues
unset( $csv_cell );
// Replace string line with split version of it.
$data[$key] = $raw;
}
print_r( $data );

?>

[washo4evr]

Hi,

thank you very much for your quick reply.

I know the formating isn't good to begin with...

It is being automatically generated by a software my company didn't write.

Therefore, we can't change it

 

And, since I did some C++ a long time ago, my boss wants me to make a website that will get data from a FTP server, sort everything, generate charts and let users compare figures/results.

 

With your code, I'll get an array, right?

How should I deal with it afterwards?

 

Thanks again

 

 

 

[xyph]

Yes, $data will contain a 2D array.

 

If what I did wasn't enough to scoot you along, then I can't really help. This is the point where you need to research on your own. My snippet covered the more complex part of splitting the string into an array.

 

Give it a couple attempts, check out the manual on array functions and array iteration. My script end with print_r, so you get a clear idea of the array structure and data

[washo4evr]

Awesome.

I will research that.

Thanks