I’m creating simple sample programs for commonly used processes as a personal memo.
※ Preliminary settings and introductions are omitted.
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”.
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 1Execution 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, 0Note
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 cityCodeFile 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);
