Export sql server query result to txt file on apache server automatically

Question

I do the following query of my apache server in an SQL SERVER database:

<?php

// Dados do banco
$dbhost   = "192.168.0.100";    #Nome do host
$db       = "DATABASE";         #Nome do banco de dados
$user     = "root";                 #Nome do usuáo
$password = "root";             #Senha do usuáo

@mssql_connect($dbhost,$user,$password) or die("Nãfoi possíl a conexãcom o servidor!");
@mssql_select_db("$db") or die("Nãfoi possíl selecionar o banco de dados!");

$instrucaoSQL = "SELECT  
                    A.CODIGO     AS COD,
                    B.chapa      AS CHAPA,
                    A.nome       AS NOME,
                    REPLICATE('0', 11 - LEN(A.cpf)) + RTrim(A.cpf) AS CPF,
                    B.CODFILIAL  AS LOJA
                FROM
                    PPESSOA AS 
                        A LEFT OUTER  JOIN PFUNC as B on 
                                A.CODIGO = b.CODPESSOA                              
                                WHERE B.CODSITUACAO<>'D'
                                  ORDER BY CHAPA";
$consulta = mssql_query($instrucaoSQL);
$numRegistros = mssql_num_rows($consulta);

echo "Esta tabela contém $numRegistros registros!\n<hr>\n";

if ($numRegistros!=0) {
    while ($row = mssql_fetch_array($consulta)) {
        echo $row['COD']."-".$row['CHAPA']."-".$row['NOME']."-".$row['CPF']."-".$row['LOJA']."\n<br>\n";
    }
}

?>

I need to save the result inside a folder in a file . txt on my apache server,example:

/var/www/html/sistema/export/resultado.txt

How could I do this kind of process automatically?

Answer 1

For this you will need 3 php functions:

fopen, fwrite, fclose

fopen: php.net/fopen

$file = fopen(string $filename , string $mode);

$filename: Path to the file to be used

$mode: How you will work with this file. The fopen will automatically create the txt file if it does not exist, if it exists you must decide whether what will put inside the file should be at the beginning or at the end, this you can edit by $mode, Learn more!

Once you open your file, you need to write to it, as stated earlier, what you write will appear at the beginning or end of the file, depending on the $mode used in the function $fopen. To write to the file use fwrite.

fwrite: php.net/fwrite

fwrite ( resource $handle , string $string [, int $length ] )

Once written, you need to close the file in question using the function fclose

fclose: php.net/fclose

bool fclose ( resource $handle )

Example: w3schools

<?php
$file = fopen("test.txt","w");
fwrite($file,"Hello World. Testing!");
fclose($file);
?>

Adapting to your code would look like this:

<?php

// Dados do banco
$dbhost   = "192.168.0.100";    #Nome do host
$db       = "DATABASE";         #Nome do banco de dados
$user     = "root";                 #Nome do usuáo
$password = "root";             #Senha do usuáo

@mssql_connect($dbhost,$user,$password) or die("Nãfoi possíl a conexãcom o servidor!");
@mssql_select_db("$db") or die("Nãfoi possíl selecionar o banco de dados!");

$instrucaoSQL = "SELECT  
                    A.CODIGO     AS COD,
                    B.chapa      AS CHAPA,
                    A.nome       AS NOME,
                    REPLICATE('0', 11 - LEN(A.cpf)) + RTrim(A.cpf) AS CPF,
                    B.CODFILIAL  AS LOJA
                FROM
                    PPESSOA AS 
                        A LEFT OUTER  JOIN PFUNC as B on 
                                A.CODIGO = b.CODPESSOA                              
                                WHERE B.CODSITUACAO<>'D'
                                  ORDER BY CHAPA";
$consulta = mssql_query($instrucaoSQL);
$numRegistros = mssql_num_rows($consulta);

echo "Esta tabela contém $numRegistros registros!\n<hr>\n";

if ($numRegistros!=0) {
    while ($row = mssql_fetch_array($consulta)) {
        $string = $row['COD']."-".$row['CHAPA']."-".$row['NOME']."-".$row['CPF']."-".$row['LOJA']."\n<br>\n";
        $file = fopen("test.txt","w");
        fwrite($file,$string);
        fclose($file);
        echo $string;
    }
}

?>

EDIT: Since you need to record many records with a single mysql call the correct way to use it would be:

if ($numRegistros!=0) {
    $file = fopen("test.txt","w");
    while ($row = mssql_fetch_array($consulta)) {
        $string = $row['COD']."-".$row['CHAPA']."-".$row['NOME']."-".$row['CPF']."-".$row['LOJA']."\n<br>\n";
        fwrite($file,$string);
        echo $string;
    }
    fclose($file);
}

Using the $mode "w" will open the file and write at the beginning of it

Answer 2

Use file_put_contents() to create the file by passing a string ($conteudo) which will have all the fields of consultation set by - who does this implode()

$conteudo .= '';  
while ($row = mssql_fetch_array($consulta)) {
    $conteudo .= implode($row, '-') .PHP_EOL;  
}

file_put_contents('consulta.txt', $conteudo);