jcadima
3/29/2017 - 3:57 PM

Export MySQL table to CSV via cron job

Export MySQL table to CSV via cron job

https://gist.github.com/findjonmos/9975890

<?php 
if (isset($_SERVER['REMOTE_ADDR'])) {
    die(':)');
}

echo "Cron is running";

$hostname   = 'localhost';
$username   = 'cl50-whiskey_p';
$password   = 'rmBRsm!kK';
$myDatabase = 'cl50-whiskey_p';

try {
    $conn = new PDO('mysql:host=localhost;dbname='.$myDatabase, $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);    

    $stmt = $conn->prepare("SELECT users.id AS ui, users.firstname AS uf, users.lastname AS ul, users.email AS ue, users.dob AS ud, messages.line1 AS ml1, messages.line2 AS ml2, messages.line3 AS ml3, messages.line4 AS ml4, messages.barcode_selected AS mb, address.my_address AS am, address.firstname AS af, address.lastname AS al, address.address1 AS aa1, address.address2 AS aa2, address.town AS at, address.postcode AS ap, address.datecreated AS ad
			FROM users
			JOIN messages
			ON users.id = messages.user_id
			JOIN address
			ON users.id = address.user_id");

    $stmt->execute();

	$filelocation = 'assets/exports/';
	$filename     = 'export-'.date('Y-m-d H.i.s').'.csv';
	$file_export  =  $filelocation . $filename;

    $data = fopen($file_export, 'w');

    $csv_fields = array();

	$csv_fields[] = 'id';
	$csv_fields[] = 'firstname';
	$csv_fields[] = 'lastname';
	$csv_fields[] = 'email';
	$csv_fields[] = 'dob';
	$csv_fields[] = 'line1';
	$csv_fields[] = 'line2';
	$csv_fields[] = 'line3';
	$csv_fields[] = 'line4';
	$csv_fields[] = 'barcode_selected';
	$csv_fields[] = 'my_address';
	$csv_fields[] = 'firstname';
	$csv_fields[] = 'lastname';
	$csv_fields[] = 'address1';
	$csv_fields[] = 'address2';
	$csv_fields[] = 'town';
	$csv_fields[] = 'postcode';
	$csv_fields[] = 'datecreated';

	fputcsv($data, $csv_fields);

    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        fputcsv($data, $row);
    }

} catch(PDOException $e) {
    echo 'ERROR: ' . $e->getMessage();
}