[PHP Sample] Quick and Easy! Output Data Retrieved from a Database to CSV

I’m creating simple sample programs for commonly used processes as a personal memo.
※ Preliminary settings and introductions are omitted.

Summary
Sample Program
Note

Summary

Retrieve data from the database using the value specified by the command-line argument as a key (using PDO).
Format the retrieved data into CSV format => Output as a CSV file.
Save the CSV file to the specified file path.

※The database used in the sample contains regional data retrieved from “RESAS”.

RESAS-API - 市区町村一覧

地域経済分析システム（RESAS：リーサス）のデータがAPI提供されます。API提供されることにより、従来よりも深く・自由に分析することが可能となります。今までRESASでは見えなかった各地域における真の課題の抽出や地域資源の再発見をサポートし、地域ビジネスに通じるサービスを地域自らが創り上げることが可能となります。

Sample Program

Source Code

The file is named csv_sample.php.

<?php
// DB connection information
$dsn = "mysql:host=localhost;dbname=resas;charset=utf8mb4;port=22";
$id = "userName";
$pw = "passwd";

if (empty($argv[1]) === true) {
    print("The command-line argument is empty.");
    exit();
}

// DB connection
$pdo = new PDO($dsn, $id, $pw,array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));
$sql = "SELECT * FROM cities WHERE prefCode = :prefCode ORDER BY cityCode ASC";
$stmt = $pdo->prepare($sql);
$stmt->bindValue(":prefCode", $argv[1], PDO::PARAM_STR);
$stmt->execute();
$res = $stmt->fetchAll();

if (empty($res) === true) {
    print("No data found.");
    exit();
}

// CSV header
$csvstr = "City Code,City Name,Special Ward/Administrative Ward Flag rn";

// CSV content
foreach ($res as $v) {
    $csvstr .= $v["cityCode"] . ",";
    $csvstr .= $v["cityName"] . ",";
    $csvstr .= $v["bigCityFlag"] . "rn";
}

// Specify the output destination + filename for the CSV file
$filepath = "./cities.csv";
// Create a CSV file
$file_handle = fopen($filepath, "w");

// Write data to the file
$csvstr = mb_convert_encoding($csvstr, "UTF-8", "UTF-8");
fwrite($file_handle, $csvstr);
chmod($filepath, 0777);
fclose($file_handle);

Execution Method

Place the script in any directory and run it from the command line with arguments.

Specify the "Prefecture Code" as an argument
# php /var/www/sampletasks/csv_sample.php // Prefecture_Code

Example) 
# php /var/www/sampletasks/csv_sample.php 1

Execution Result

Contents of the CSV file output when executing with the prefecture code “1”.

City Code, City Name, Special Ward/Administrative Ward Flag
01100, Sapporo City, 2
01101, Chuo Ward, Sapporo City, 1
01102, Kita Ward, Sapporo City, 1
01103, Higashi Ward, Sapporo City, 1
01104, Shiroishi Ward, Sapporo City, 1

... omitted ...

01696, Tomari Village, Kunashiri District, 0
01697, Ruyobetsu Village, 0
01698, Rubetsu Village, 0
01699, Shana Village, 0
01700, Shibetoro Village, 0

Note

Additional Notes on the Sample Program

Command-Line Arguments

The value specified by the command-line argument is stored in the $argv variable (array) and can be used within the program.

PDO

PDO Connection

Please replace the following with the appropriate values as needed.

$dsn = "mysql:host=hostname;dbname=database_name;charset=charset;port=port_number";
$id = "userName";
$pw = "passwd";

$pdo = new PDO($dsn, $id, $pw, array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));

Binding

As a precaution against injection, always bind any values that are input from external sources.
As a note, there are two ways to specify bindings: “named placeholders” and “question mark placeholders.”

Named Placeholders

The binding in the sample program uses “named placeholders.”

$sql = "SELECT * FROM cities WHERE prefCode = :prefCode ORDER BY cityCode ASC";
$stmt = $pdo->prepare($sql);
$stmt->bindValue(":prefCode", $argv[1], PDO::PARAM_STR);

Question Mark Placeholders

ou can also write it using “question mark placeholders” as shown below.

$sql = "SELECT * FROM cities WHERE prefCode = ? ORDER BY cityCode ASC";
$stmt = $pdo->prepare($sql);
$stmt->bindValue(1, $argv[1], PDO::PARAM_STR);

In the bindValue argument, you specify the position of the “?” within the SQL.
In the sample program, there is only one bind, which makes it hard to see, but if there are multiple, it would look like this:

$sql = "SELECT * FROM cities WHERE prefCode = ? AND cityCode = ? ORDER BY cityCode ASC";
$stmt = $pdo->prepare($sql);
$stmt->bindValue(1, $argv[1], PDO::PARAM_STR); // Position of prefCode
$stmt->bindValue(2, $argv[2], PDO::PARAM_STR); // Position of cityCode

File Saving

In this sample, file operations are done using fopen to fclose, but you can achieve the same functionality using file_put_contents.

fopen ~ fclose

// Specify the output destination and filename for the CSV file
$filepath = "./cities.csv";
// Create a CSV file
$file_handle = fopen($filepath, "w");

// Write data to the file
$csvstr = mb_convert_encoding($csvstr, "UTF-8", "UTF-8");
fwrite($file_handle, $csvstr);
chmod($filepath, 0777);
fclose($file_handle);

file_put_contents

// Specify the output destination and filename for the CSV file
$filepath = "./cities.csv";

// Write data to the file
$csvstr = mb_convert_encoding($csvstr, "UTF-8", "UTF-8");
file_put_contents($filepath, $csvstr);
chmod($filepath, 0777);