Views: 5.944 views
Staff,
Good afternoon.
After a long time without posting, I will come back with a nice news for you that I am using in a project where I need to create an integration between two systems, one local and another on the web: exporting data to a CSV file.
For that, I created a class containing the function that we will use, available here.
Example of use:
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | <?php require_once("classe.php"); // Conecta no banco de dados $hostname = "localhost"; $usuario = "root"; $senha = ""; $database = "empresa"; $conexao = mysql_connect( $hostname, $usuario, $senha ); mysql_select_db( $database, $conexao ); // Exporta os dados $classe = new Exporta(); $classe->exportMysqlToCsv("SELECT * FROM cores", "cores.csv"); ?> |
And that will generate a file with the following:
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | ID,STATUS,MARCA,DATA_INC,DATA_ALT,DATA_HAB,NOME,DATA_COM,COD_INTELIGENTE,ID_COLECAO,DATA_PUBLIC "1","N","N","2007-08-14","2007-08-14",,"UNICA","2007-08-14 17:32:59",,, "2","N","N","2007-08-14","2007-08-14",,"BRANCA","2007-08-14 17:33:35",,, "3","N","N","2007-08-14","2007-08-14",,"AMARELA","2007-08-14 17:33:47",,, "4","N","N","2007-08-14","2007-08-17",,"AZUL CLARO","2007-08-17 15:24:53",,, "5","N","N","2007-08-14","2007-08-14",,"VERDE","2007-08-14 17:34:19",,, "12","N","N","2007-08-17","2007-08-17",,"DIVERSAS","2007-08-17 17:10:28",,, "6","N","N","2007-08-14","2007-08-14",,"ROSA","2007-08-14 17:34:29",,, "7","N","N","2007-08-14","2007-08-14",,"LARANJA","2007-08-14 17:34:40",,, "8","N","N","2007-08-14","2007-08-14",,"LILAS","2007-08-14 17:34:53",,, "9","N","N","2007-08-14","2007-08-14",,"PRETO","2007-08-14 17:35:20",,, "10","N","N","2007-08-17","2007-08-17",,"AZUL ESCURO","2007-08-17 15:25:09",,, "11","N","N","2007-08-17","2007-08-17",,"AZUL MARINHO","2007-08-17 15:25:37",,, "13","N","N","2007-08-17","2007-08-17",,"PINK","2007-08-17 17:44:10",,, "14","N","N","2007-08-17","2007-08-17",,"VERMELHA","2007-08-17 18:13:37",,, |
And that's it, guys! To the next!
For those who do not want to download the class, follow the code below:
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 | <?php class Exporta { public function exportMysqlToCsv( $strQuery, $Ds_Arquivo_Destino = 'export.csv' ) { $Ds_Separador_Linha = "\n"; $Ds_Separador_Coluna = ","; $Ds_Caracter_Encapsular = '"'; $Ds_Caracter_Escape = "\\"; // Recupera os dados do servidor $Ds_Retorno = mysql_query( $strQuery ); $Nr_Colunas = mysql_num_fields( $Ds_Retorno ); $Ds_Linha_CSV = ''; for ( $i = 0; $i < $Nr_Colunas; $i++ ) { $Ds_Linha_CSV .= mysql_field_name( $Ds_Retorno, $i ) . $Ds_Separador_Coluna; } $Ds_Saida = trim( substr( $Ds_Linha_CSV, 0, -1 ) ); $Ds_Saida .= $Ds_Separador_Linha; while ( $Ds_Linha_Banco = mysql_fetch_array( $Ds_Retorno ) ) { $Ds_Linha_CSV = ''; for ( $j = 0; $j < $Nr_Colunas; $j++ ) { if ( $Ds_Linha_Banco[$j] == '0' || $Ds_Linha_Banco[$j] != '' ) { if ( $Ds_Caracter_Encapsular == '' ) { $Ds_Linha_CSV .= $Ds_Linha_Banco[$j]; } else { $Ds_Linha_CSV .= $Ds_Caracter_Encapsular . str_replace( $Ds_Caracter_Encapsular, $Ds_Caracter_Escape . $Ds_Caracter_Encapsular, $Ds_Linha_Banco[$j] ) . $Ds_Caracter_Encapsular; } } else { $Ds_Linha_CSV .= ''; } if ( $j < $Nr_Colunas - 1 ) { $Ds_Linha_CSV .= $Ds_Separador_Coluna; } } $Ds_Saida .= $Ds_Linha_CSV; $Ds_Saida .= $Ds_Separador_Linha; } // Grava o arquivo físico if ( !is_dir( dirname( $Ds_Arquivo_Destino ) ) ) { mkdir( dirname( $Ds_Arquivo_Destino ), 0755, true ); } $criarArquivo = (!is_file( $Ds_Arquivo_Destino ) ); $objTxt = fopen( $Ds_Arquivo_Destino, "w" ); if ( $criarArquivo) { //UTF-8 fwrite( $objTxt, pack( "CCC", 0xef, 0xbb, 0xbf ) ); } fwrite( $objTxt, $Ds_Saida ); fclose( $objTxt ); } } ?> |
Hi Dirceu, thanks for sharing! It helped me a lot, but I have a doubt if I need to change the names using str_replace for example change BLACK for BLACK. Which part of the code do I change?
Hugs!