I develop in a few languages, but I use PHP for just about all of my data feeds. I do so because the PHP scripts are portable (most web hosts support PHP) and the code itself is small and easy to change.
The script I use is typically only about 20 lines long, as show below. I'll spill the code, then explain.
The Script
header("Content-type: text/csv");
$conn = new mysqli("server", "username", "password", "database_name");
$query = "SELECT id, model, title, link, image_url, price
FROM products
WHERE active = 1";
$result = $conn->query($query);
echo "id,model,title,link,image_url,price\n";
while($row = $result->fetch_assoc()) {
echo sprintf("%s,%s,%s,%s,%s,%f\n",
$row['id'],
$row['model'],
$row['title'],
$row['link'],
$row['image_url'],
$row['price']);
}
The htAccess File
AddHandler application/x-httpd-php .csv
Explained
In the first line, we issue an HTTP header called Content-Type. This tells the client or browser requesting this page that this is a CSV file. In most cases, you can do without this, especially if the requester knows to expect a CSV file in return.
Next, we use PHP's built in mysqli functions to create a connection to the database. If your database is hosted on the same server as your script, you can typically just use localhost for the server. Although, on some systems that have not setup their hosts file properly, you may be required to use 127.0.0.1 as the server. I've had to do this before.
In the next two lines, we build and execute our query on the database. A result set is returned which can be "iterated" over to get the query results.
Before we iterate over the result set, depending on the data feed requirements, we may need to output a "header row". This indicates to the feed parser what each column in the feed represents. We do this with a simple echo statement.
In the final step, we use the fetch_assoc method of the result set object we created to loop over the results and output them one-by-one. The sprintf statement provides a means to format the output a little more cleanly. It is not required.
The \n found at the end of each row signals the end of the row, and that output should begin on a new row.
You'll notice that I've added a "handler" inside the .htaccess file. This file should only be present in your feeds folder, otherwise, it would apply to all csv files within your site. The handler essentially says to Apache (the web server), "run all files with a .csv extension through PHP".
This is not required, but is a nice touch. It allows us to offer our feed files for download with a .csv extension, i.e.
http://www.mysite.com/feeds/here-is-my-awesome.csv