Query to Run - Remote File on Server
Writing the File for the Server
In the application, you'll see a line that says "Query to Run:". Generally, websites are constructed in a way that only allows a program on the server to access the database. In our example to the right, we have a php script that would run on your server. This script, when called, will write a file on the server that can then be located by the application, and downloaded to your pc. From there, the file is merged with your text email, and sent to the appropriate individuals or organizations.
Query to Run
If you're sending lots of emails, everyday, you'll want to automate as much as possible. This field allows you to specify a query that resides on a remote server. In our case, the query would open the database, and ask, what members expire in seven days? If you have a programming department, you may want to get them involved. In general terms, the query will often run against a MYSQL database, with a php query. What's nice about this, is that the query is written once, and you can forget about it. It get's called everyday, pulling out the customers that expire in seven days, no other work on your part.
<?php
function query_to_csv($db_conn, $query, $filename, $attachment = false, $headers = true) {
if($attachment) {
// send response headers to the browser
header( 'Content-Type: text/csv' );
header( 'Content-Disposition: attachment;filename='.$filename);
$fp = fopen('php://output', 'w');
} else {
$fp = fopen($filename, 'w');
}
$result = mysql_query($query, $db_conn) or die( mysql_error( $db_conn ) );
if($headers) {
// output header row (if at least one row exists)
$row = mysql_fetch_assoc($result);
if($row) {
fputcsv($fp, array_keys($row));
// reset pointer back to beginning
mysql_data_seek($result, 0);
}
}
while($row = mysql_fetch_assoc($result)) {
fputcsv($fp, $row);
}
fclose($fp);
}
$conn=mysql_connect("localhost", "keepmembers","##password##") or die('Cannot connect to the database because: ' . mysql_error());
$rs = mysql_select_db ("keepmembers");
// Using the function
$sql = "SELECT * FROM keep_test";
// output as an attachment
query_to_csv($conn, $sql, "test.csv", true);
// output to file system
query_to_csv($conn, $sql, "test.csv", false);
?>