How to save MySQL query as csv file on remote ftp server using PHP

Learn how to save the result of a MySQL query as a CSV file on a remote FTP server using PHP. Follow these simple steps to effectively manage data storage and transfer.

To save the result of a query MySQL As a CSV file on a remote FTP server using PHP, you can follow these steps:

Step 1: Connect to MySQL Database

First, you need to connect to your MySQL database using PHP. You can use the PHP extension mysqli or PDO for this purpose.

Step 2: Execute the MySQL Query

Run the MySQL query to retrieve the data you want to save as a CSV file.

Step 3: Convert Data to CSV Format

Convert the recovered data to CSV format. You can use the function fputcsv of PHP for this purpose.

Step 4: Connect to Remote FTP Server

Create an FTP connection to the remote server using PHP. You can use the functions ftp_connect and ftp_login because of this.

Step 5: Upload CSV File

Upload the generated CSV file to the remote FTP server using the function ftp_put of PHP.

connect_error) {
    die("Connessione fallita: " . $conn->connect_error);
}

// Passo 2: Eseguire la Query MySQL
$sql = "SELECT * FROM tua_tabella";
$result = $conn->query($sql);

// Passo 3: Convertire i Dati nel Formato CSV
$csv_data = fopen('php://temp', 'w');
while ($row = $result->fetch_assoc()) {
    fputcsv($csv_data, $row);
}
rewind($csv_data);

// Passo 4: Connettersi al Server FTP Remoto
$ftp_server = "il_tuo_server_ftp";
$ftp_username = "il_tuo_nome_utente_ftp";
$ftp_password = "la_tua_password_ftp";
$conn_id = ftp_connect($ftp_server);
$login_result = ftp_login($conn_id, $ftp_username, $ftp_password);

// Passo 5: Caricare il File CSV
if ($login_result) {
    ftp_pasv($conn_id, true); // Abilita la modalità passiva
    ftp_fput($conn_id, '/percorso/della/cartella/remota/tuo_file.csv', $csv_data, FTP_ASCII);
    ftp_close($conn_id);
} else {
    echo "Accesso FTP fallito";
}

// Chiudi la connessione MySQL
$conn->close();
?>

Replace placeholders (e.g., il_tuo_server_mysql, il_tuo_server_ftp, etc.) with your actual MySQL and FTP server details and customize the MySQL query to suit your needs.

This example demonstrates how to connect to a MySQL database, run a query, convert the result to CSV, and then upload it to a remote FTP server using PHP.

Published in MySQL, PHP